Hi, Relatively new to SA and unable to find the answer in this group's archives. (Possibly my search-fu is weak, in which case I apologize but hope you'll point me in right direction.)
We're building a generic DB query engine on SA using the ORM and mappers, but we can't seem to get SA to naturally figure out the join required to render a query that filters on data found in two tables that are related through a 3rd-party table. For example, given two tables: tbl_people idpeople (primary key) ... tbl_documents iddoc (primary key) ... We have a 3rd-party table that relates the two of them together thusly: tbl_people_documents idpeople (foreign key to tbl_people.idpeople; primary key) iddoc (foreign key to tbl_documents.iddoc; primary key) ... Building a mapper object with the proper relations is no problem. But building a query object that filters on data in just tbl_people and tbl_documents fails because SA can't seem to infer the join between tbl_people and tbl_documents despite the foreign keys present in tbl_people_documents. MUST we explicitly supply the join to such query objects? Or is there some way that SA can figure out that tbl_people_documents is in between tbl_people and tbl_documents on its own? Perhaps there is something we can add to the tbl_people/tbl_documents object definitions that clues SA in? Silly example: Say we want to find all the people records that have last names of "SMITH" and document body content of "SPAM". We would write the SQL like this: SELECT p.* FROM tbl_people p, tbl_documents d, tbl_people_documents pd WHERE p.idpeople = pd.idpeople AND pd.iddoc = d.iddoc AND p.lastname = 'SMITH' AND d.body = 'SPAM' ; I understand how to build the equivalent SA query object by explicitly supplying the query object the join criteria. But we want SA to *know* how to infer that join criteria itself. Is this possible? Thanks in advance, Damon --~--~---------~--~----~------------~-------~--~----~ 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---