[sqlalchemy] Re: Auto-joins via FK

[Jonathan Ellis]

On 2/27/07, Rick Morrison [EMAIL PROTECTED] wrote:
 Shouldn't SA be able to figure out the following joins without needing the
 explicit join params?

no, SA never tries to guess how you want to join things because then
you'd have to add a way to override it when it guesses wrong and it's
simpler to just make things explicit in the first place.

the join function will automatically do FK-based where conditions,
that's probably what you want.

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Auto-joins via FK

[Michael Bayer]

the join() function, given two tables, will make a foreign key join  
condition by default if you dont specify the onclause.  but if you  
arent using a join function somewhere, then nothing else is going  
to generate any clauses for you.

On Feb 27, 2007, at 10:50 AM, Rick Morrison wrote:

 Shouldn't SA be able to figure out the following joins without  
 needing the explicit join params?

 I've got to be missing something dumb.

 from sqlalchemy import *

 metadata = BoundMetaData('sqlite:///:memory:')

 ent = Table('entity', metadata,
 Column('id',  INT,  Sequence('entity_id'),  
 primary_key=True, nullable=False),
 Column('nm',  VARCHAR(12), nullable=False)
 )

 loc = Table('location', metadata,
   Column('id',  INT, Sequence('location_id'),  
 primary_key=True, nullable=False),
   Column('nm',  VARCHAR(50))
   )

 el = Table('el_rel', metadata,
   Column('typ', VARCHAR(12), nullable=False),
   Column('ident',   INT, ForeignKey(' entity.id'),   
 nullable=False),
   Column('idloc',   INT, ForeignKey('location.id'),  
 nullable=False)
   )

 metadata.create_all()

 ent.insert ().execute({'id':1, 'nm':'Eone'},  {'id':2,  
 'nm':'Etwo'},  {'id':3, 'nm':'Ethree'})
 loc.insert().execute({'id':1, 'nm':'Lone'}, {'id':2, 'nm':'Ltwo'},  
 {'id':3, 'nm':'Lthree'})
 el.insert().execute({'typ':'TEST1', 'ident':1, 'idloc':1},  
 {'typ':'TEST2', 'ident':2, 'idloc':2})

 testcases = [('2-way -ent', [ent.c.nm ], None),
  ('2-way -loc', [loc.c.nm], None),
  ('3-way -ent+loc', [ent.c.nm, loc.c.nm], None),
  ('3-way, explicit join', [ent.c.nm, loc.c.nm],
   el.join(ent, ent.c.id==el.c.ident).join(loc,  
 loc.c.id==el.c.idloc))
  ]

 for test_name, test_cols, test_from in testcases:
 print
 print test_name
 q = select(test_cols, el.c.typ == 'TEST1')
 if test_from:
 q.append_from(test_from)
 print str(q)
 print q.execute().fetchall()



 Thx,
 Rick

 


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---