On Jul 7, 2011, at 2:16 PM, Bryan wrote: > I'm having trouble telling an orm query which table is the "main" > table when I > only use a single column from the main table and it is wrapped up in > an SQL > function. It's almost like SqlAlchemy can't see that I am using a > column from > that table because it is inside of a function:: > > # -- Schema ------------------- > # > # Labor > # ===== > # id > # hours > # createdBy (user ref) > # editedBy (user ref) > # > # > # User > # ==== > # id > # username > # ----------------------------- > > > # -- Code ------------------- > CREATED_BY = aliased(User, name='createdBy') > EDITED_BY = aliased(User, name='editedBy') > > q = query(CREATED_BY.username, func.sum(Labor.hours)) > q = q.join((CREATED_BY, Labor.createdBy==CREATED_BY.id)) > q.all() > > > This is producing a query like this:: > > SELECT > user_1.username, sum(labor.st) > FROM > user AS user_1 > INNER JOIN user AS user_1 ON labor.createdBy = user_1.id > > Which gives me a OperationalError "1066, "Not unique table/alias: > 'user_1'". > > I would expect this:: > > SELECT > createdBy.username, sum(labor.st) > FROM > labor > INNER JOIN user AS createdBy ON labor.createdBy = createdBy.id > > As soon as I add a column from the Labor table to the query, and it is > not in a > function, the query works. For example, this works:: > > q = query(CREATED_BY.username, Labor.id, func.sum(Labor.hours)) > > Mysql 5 > SqlAlchemy 0.5.2
if you could upgrade to 0.6 or 0.7, you would say query(created_by).select_from(Labor).join(created_by, <onclause>) else if stuck with 0.5 you need to use from sqlalchemy.orm import join query(created_by).select_from(join(Labor, created_by, <onclause>).join(<whatever else needs to be joined>)) i.e. the whole JOIN needs to be in select_from -- 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.
