On Feb 26, 2013, at 5:15 PM, "Arkilic, Arman" <arki...@bnl.gov> wrote:
> Hi,
> I have four tables that I would like to join algorithmically. Element with
> element_prop, elementprop with element_typeprop in one direction and element
> with element_type in the other. I would like to do this in one line as I am
> concerned about the number queries must be minimal given I am working in a
> gigantic database.
>
> q=session.query(element).outerjoin(element_prop,element.element_id==element_prop.element_id).outerjoin(element_prop,element_type_prop.element_type_prop_id==element_prop.element_type_prop_id).outerjoin(element,element_type.element_type_id==element.element_type_id)
> I replicate the format I'd use for native SQL. However, I come across
> aliasing issues: ERROR 1066 (42000): Not unique table/alias: 'element_prop'
> when I run the generated SQL code, even though sqlalchemy doesn't prompt any
> error messages.
SQLAlchemy lets the database complain when it wants to in most cases, so that
we don't have to perform all that checking ourselves and so that we don't get
in the way of the RDBMS doing a much better job explaining it's specific issue.
the RDBMS knows best what it wants, and is often dependent on what kind of
database it is, what version, etc.
> I would like to find out an efficient and robust way of joining these tables
> the way I described. What would be the best solution given my concerns?
the aliasing refers to the fact that SQL like this is illegal:
SELECT * FROM some_table JOIN some_table ON some_table.x = some_table.y
Above we have the same table stated twice in the FROM clause. At least one
needs to be aliased so that they can be lexically identified:
SELECT * FROM some_table JOIN some_table AS st1 ON some_table.x = st1.y
Aliasing with query() and join() has a few flavors. Here's the most explicit,
which is the simplest to understand and provides the most control:
from sqlalchemy.orm import aliased
my_alias = aliased(MyClass)
query(MyClass).join(my_alias, MyClass.x == my_alias.y)
When joining on relationship(), more automation is available. You can for
example use the aliased=True flag so that join() will set the current
"selectable" to be an anonymously aliased element. This feature was designed
with self-referential joins in mind:
query(MyClass).join(MyClass.someprop, MyProp.element, aliased=True)
Above, the join is from MyClass->MyProp->MyClass (assuming that's what
MyProp.element points to). This is two JOIN clauses and in each, the right
side will be aliased. The filter() and filter_by() calls will then
automatically apply this aliasing to incoming expressions. Below, MyClass.foo
will be anonymously aliased:
query(MyClass).join(MyClass.someprop, MyProp.element,
aliased=True).filter(MyClass.foo == 'bar')
Docs on aliasing:
http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#using-aliases
http://docs.sqlalchemy.org/en/rel_0_8/orm/query.html?highlight=query.join#sqlalchemy.orm.query.Query.join
(scroll down to Constructing Aliases Anonymously)
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
