Re: [Modeling-users] Couple of questions...

[Sebastien B. <sbi...@us...>]

        Hi Aaron,

"Aaron Freeman" <afr...@em...> wrote:
> (1) Is there a debug flag somewhere that dump all SQL queries to the
> console?

Absolutely: set the environment variable MDL_ENABLE_DATABASE_LOGGING to
any true value. For details, see:
  http://modeling.sourceforge.net/UserGuide/env-vars-core.html


> (2) How are nested joins accomplished?
> For example, in the AuthorBooks schema, lets assume there is another table
> called publisher that has a 1-M relationship with books, like so (sorry
> about the formatting):
>=20
[...
  Writer <-author----books->> Book <<-books----publisher-> Publisher
 ...]

> How would I get writers by publisher?
>=20
> More specifically, is there an elegant way to do a nested join in a single
> query, or do I just have to do a single writer-book join and then iterate
> over the resultset for the second join?
>=20

Yes, there is a dedicated way of doing this: use the dotted notation:

  >>> ec.fetch('Writer', 'books.publisher.bizName=3D=3D"P1"')

returns every writer who published (at least) a book w/ publisher P1.

The generated SQL query is (postgresql here):

  SELECT DISTINCT t0.ID, t0.LAST_NAME, t0.FIRST_NAME, t0.AGE,
         t0.FK_WRITER_ID, t0.BIRTHDAY=20
  FROM WRITER t0=20
  INNER JOIN  ( BOOK t1
                INNER JOIN PUBLISHER t2=20
                ON t1.FK_PUBLISHER=3Dt2.ID )
  ON t0.ID=3Dt1.FK_WRITER_ID
  WHERE t2.BIZ_NAME =3D 'P1';


> Code examples would be great!

  I'm including in the end of the message a PyModel named EAuthorBooks
  (E stands for Extended :) derived from the one used in the User's
  Guide, with sample data, demonstrating the approach.

        Regards,

=20=20=20=20=20=20=20=20
-- S=E9bastien.



PS: you'll need to setup a database before running the example, e.g. w/
    postgresql:

   $ mdl_generate_DB_schema.py \
        --admin-dsn "localhost:template1:postgres:" \
        -v -C pymodel_extAutborBooks.py


------------------------------------------------------------------------
#! /usr/bin/env python
# -*- coding: iso-8859-1 -*-
"Extended AuthorBooks model, with a Publisher"

from Modeling.PyModel import *

##
# Defaults
AFloat.defaults['precision'] =3D 10
AFloat.defaults['scale'] =3D 2
AString.defaults['width'] =3D 40

Association.defaults['delete']=3D['nullify', 'nullify']

Entity.defaults['properties'] =3D [
  APrimaryKey('id', isClassProperty=3D0, isRequired=3D1, doc=3D'PK')
]

##
# Adapt to fit your own configuration!!

_connDict =3D {'database': 'EAUTHOR_BOOKS', 'host':'localhost',
             'user':'postgres','password':''}

model =3D Model('EAuthorBooks',adaptorName=3D'Postgresql',
                connDict=3D_connDict)

model.doc =3D ' ... '
model.version=3D'0.1'
model.entities =3D [
  #
  Entity('Book',
         properties=3D[ AString('title', isRequired=3D1, columnName=3D'titl=
e'),
                      AFloat('price'),
                       ],
         ),
  Entity('Writer',
         properties=3D[ AString('lastName',isRequired=3D1, width=3D30 ),
                      AString('firstName'),
                      AInteger('age', displayLabel=3D'Age'),
                      ADateTime('birthday', usedForLocking=3D0),
                      ]
         ),
  Entity('Publisher',
         properties=3D[ AString('bizName',isRequired=3D1),
                      AString('location'),
                      ]
         ),
  ]
#---
model.associations=3D[
  Association('Book', 'Writer',
              relations=3D['author', 'books'],
              delete=3D['nullify', 'cascade'],
              keys=3D['FK_Writer_Id', 'id']),
  Association('Writer', 'Writer',
              relations=3D['pygmalion', None],
              delete=3D['nullify', None],
              keys=3D['FK_Writer_id', 'id']),
  Association('Book', 'Publisher', relations=3D['publisher', 'books'])
  ]

model.build()
model=3Dmodel.component

if __name__=3D=3D"__main__":
  #import pdb ; pdb.set_trace()
=20=20
  from Modeling.dynamic import build
  build(model, define_properties=3D1)

  from Modeling.ModelSet import defaultModelSet
  defaultModelSet().addModel(model)

  from Modeling.EditingContext import EditingContext
  ec=3DEditingContext()

  from EAuthorBooks.Publisher import Publisher
  from EAuthorBooks.Writer import Writer
  from EAuthorBooks.Book import Book

  # Initialization
  p1=3DPublisher(); p1.bizName=3D"P1"
  p2=3DPublisher(); p2.bizName=3D"P2"

  w1=3DWriter(); w1.lastName=3D"in p1"
  w2=3DWriter(); w2.lastName=3D"in p2"
  w3=3DWriter(); w3.lastName=3D"in p1 and p2"
=20=20
  b1w1=3DBook(); b1w1.title=3D"b1w1"
  b2w1=3DBook(); b2w1.title=3D"b2w1"
  b1w2=3DBook(); b1w2.title=3D"b1w2"
  b2w2=3DBook(); b2w2.title=3D"b2w2"
  b1w3=3DBook(); b1w3.title=3D"b1w3"
  b2w3=3DBook(); b2w3.title=3D"b2w3"
  ec.insert(p1);
  ec.insert(p2); ec.insert(w1); ec.insert(w2); ec.insert(w3)
  ec.insert(b1w1); ec.insert(b2w1); ec.insert(b1w2); ec.insert(b2w2)
  ec.insert(b1w3); ec.insert(b2w3)

  w1.addToBooks(b1w1); b1w1.author=3Dw1; w1.addToBooks(b2w1); b2w1.author=
=3Dw1
  w2.addToBooks(b1w2); b1w2.author=3Dw2; w2.addToBooks(b2w2); b2w2.author=
=3Dw2
  w3.addToBooks(b1w3); b1w3.author=3Dw3; w3.addToBooks(b2w3); b2w3.author=
=3Dw3

  b1w1.publisher=3Dp1; p1.addToBooks(b1w1)
  b2w1.publisher=3Dp1; p1.addToBooks(b2w1);
  b1w2.publisher=3Dp2; p2.addToBooks(b1w2)
  b2w2.publisher=3Dp2; p2.addToBooks(b2w2);
  b1w3.publisher=3Dp1; p1.addToBooks(b1w3)
  b2w3.publisher=3Dp2; p2.addToBooks(b2w3);

  ec.saveChanges()

  # query
  ec=3DEditingContext()
  print [w.lastName
         for w in ec.fetch('Writer', 'books.publisher.bizName=3D=3D"P1"')]
  # returns: w1 and w3
  ec=3DEditingContext()
  print [w.lastName
         for w in ec.fetch('Writer', 'books.publisher.bizName=3D=3D"P2"')]
  # returns: w2 and w3
------------------------------------------------------------------------