Daishy wrote:
> Hi again,
>
> I have two tables contacts(Fields id, typ_id, name) and types (Fields
> id, value). contacts has a typ_id which points to typ.id (Many-To-One-
> Relation) and an according relation (Using declarative-Style)
> Now i want to query the contacts-table, but i also want to filter and
> order on the related fields. For example (I'm using Turbogears2,
> DBSession is the sqlalchemy-instance):
> DBSession.query(Contact).???.order_by(Typ.value)
> should load the contact-table, eager load the typ-tables related to
> the contact-row and order the whole result-set by the typ-value
> column.
> (The sql would be something like: select * from contacts join typ on
> contacts.typ_id = typ.id order_by typ.value )
>
> But i cant get that to work :/ I tried using the .options(eager_load
> ('typ')) but that doesnt sort it right. Using .join(Typ) works, but it
> doesnt select the variables from typ, so no eager-loading.
>
> I'm pretty new to SQLAlchemy, so i hope i provided all relevant infos.
> Thanks very much for any answer, i'm kinda stuck on this problem :)
>
> Greetings,
> Daishy
>
You're almost there. Use the .join(Type) approach, but also add
.options(contains_eager('typ')) to the query. This is the general
pattern to use when you want to eager-load related objects but also use
related fields in filter(), order_by(), etc.
In case you were wondering, .options(eager_load(...)) is designed to not
affect the semantics of the query in any way, i.e you could drop the
eager_load option and the query would select the same "root" objects in
the same order with the same limit, etc. To ensure this, SQLAlchemy does
not expose eager-loaded objects for you to use in the rest of the query.
-Conor
--
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.
