[sqlalchemy] Re: How to perform inner joins

Fri, 26 Sep 2008 12:19:08 -0700

Doug -

what happened with the example I pasted ?  It generates the exact SQL you describe.   A full script is attached, using the mappings you've sent.  It uses sqlite, so you can just run it.

from sqlalchemy import *
from sqlalchemy.orm import *

engine = create_engine('sqlite://', echo=True)
metadata = MetaData()
press_table = Table('press', metadata, 
    Column('id', Integer, primary_key=True),
    Column('code', String)
)
press_routing_table = Table('press_routing', metadata, 
    Column('id', Integer, primary_key=True),
    Column('code', String)
)
press_routing_press_table = Table('press_routing_press',
                                  metadata,
                                  Column('press_id',
                                         Integer,
                                         ForeignKey('press.id'),
                                         primary_key=True),
                                  Column('press_routing_id',
                                         Integer,
                                         ForeignKey('press_routing.id'),
                                         primary_key=True),
                                  Column('type', String),
)

metadata.create_all(engine)

class Press(object): pass
class PressRouting(object): pass
 
mapper(Press,
       press_table,
       properties=dict(routes=relation(PressRouting,
                                       secondary=press_routing_press_table,
                                       backref='presses')))
      
mapper(PressRouting, press_routing_table)

sess = sessionmaker(bind=engine)()

# produces:
# SELECT press.id AS press_id, press.code AS press_code 
# FROM press JOIN press_routing_press AS press_routing_press_1 ON press.id = press_routing_press_1.press_id 
# JOIN press_routing ON press_routing.id = press_routing_press_1.press_routing_id 
# WHERE press_routing.code = ?
sess.query(Press).join(Press.routes).filter(PressRouting.code=='A').all()


On Sep 26, 2008, at 2:50 PM, Doug Farrell wrote:

Michael,
 
Thanks for your help once again. Unfortunately I’m just not ‘getting it’; I can’t make the query work the way you describe. So I’m bailing out and going back to plan SQL statements till I have more time to work on this.
 
Again, thanks for your help and guidance, I’m determined to make better use of SqlAlchemy, but I have to move ahead on the project I’m on right now.
 
Doug
 
From: [email protected] [mailto:[email protected]] On Behalf Of Michael Bayer
Sent: Friday, September 26, 2008 11:03 AM
To: [email protected]
Subject: [sqlalchemy] Re: How to perform inner joins
 
 
On Sep 26, 2008, at 9:23 AM, Doug Farrell wrote:


I'm trying to represent a many-to-many relationship between the press_table and the press_routing table using the linking table, press_routing_press. I think I've got the table structure and mapping set up, but now I need some help to build a query using SqlAlchemy that does the same thing as this MySQL query:
 
select p.id, p.code
from press p
inner join press_routing_press prp
on p.id=prp.press_id
inner join press_routing pr
on pr.id=prp._press_routing_id
where pr.code='A'
 
 
 
Hi Doug -
 
this kind of join is described at  http://www.sqlalchemy.org/docs/05/ormtutorial.html#datamapping_joins , the fourth example (albeit its for a one-to-many, the approach is the same with a "secondary" table).
 
The approach here is:
 
sess.query(Press).join(Press.routes).filter(PressRouting.code=='A').all()
 
 
 
 

 

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "sqlalchemy" group. 
To post to this group, send email to [email protected] 
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 
-~----------~----~----~----~------~----~------~--~---


Reply via email to