On Jun 28, 2011, at 12:26 PM, Oliver wrote: > I want to have the following query in sqlalchemy: > > SELECT t_objects_1.tid AS t_objects_1_tid FROM t_objects AS > t_objects_2 INNER JOIN c_objects AS c_objects_1 ON t_objects_2.tid = > c_objects_1.tid INNER JOIN t_objects AS t_objects_1 ON t_objects_1.tid > = t_objects_2.parent_id WHERE c.id = 1; > > this would return a list of parent_ids whose childs match a certain > condition... > > > I would go like the following: > s.query(t_objects_1.tid).join((c_objects, t_objects_2.tid == > c_objects.tid),(t_objects_1, t_objects_1.tid == > t_objects_2.parent_id)).filter(c_objects.id == 1).all() > > > ...but I get this error: (OperationalError) (1066, "Not unique table/ > alias: 't_objects_1'") > > so sqlalchemy is putting the wrong alias in the from clause: > > SELECT t_objects_1.tid AS t_objects_1_tid FROM t_objects AS > t_objects_1 INNER JOIN c_objects AS c_objects_1 ON t_objects_2.tid = > c_objects_1.tid INNER JOIN t_objects AS t_objects_1 ON t_objects_1.tid > = t_objects_2.parent_id WHERE c.id = %s'
Its going to start the FROM chain from "t_objects_1" since that's what's in the columns clause, at which point you then join *to* c_objects. query.select_from(t_objects_2).join()... will start the FROM clause instead from t_objects_2. > > Due to project constraints I have to use sqlalchemy==0.5.6 > > if maybe this is a known bug of 0.5.6 please let me now ah. It might not work in a version that old. Try the select_from(table) approach first, and if 0.5 isn't handling it , you should create your joins using the sqlalchemy.orm.join() function, then place the fully constructed join() construct into select_from() - I am loathe to reference the 0.5 docs as people keep finding them and thinking they are current, but an example of this is at http://www.sqlalchemy.org/docs/05/ormtutorial.html#querying-with-joins (Note to people reading this: these are the *OLD DOCS* regarding 0.5; for current join usage please see http://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins) -- 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.
