SqlAlchem add_column accross join question

[writeson]

Hi all,

I've got a function that makes the following query:

results = session.query(Order) \
.filter_by(reference=reference) \
.order_by(asc(Order.order_id)) \
.options(eagerload("item"), \
eagerload("state_ref"), \
eagerload("item.book_ref"), \
eagerload("item.cover_type_ref"), \
eagerload("item.cover_material_ref"), \
eagerload("item.cover_color_ref"), \
eagerload("item.book_ref.product_ref")) \
.add_column((Order.item.gross *
Order.item.qty).label("total"))
.all()

where item and state_ref are FK references in Order and book_ref,
cover_type_ref, cover_material_ref and cover_color_ref are FK
reference in Item and product_ref is a FK refeference in Book.

I want to add a new column to create a "total" for each row returned
from Order, but I'm getting the following exception when the

.add_column((Order.item.gross * Order.item.qty).label("total"))

is executed:

AttributeError: Neither 'InstrumentedAttribute' object nor
'Comparator' object has an attribute 'gross'

How can I make add_column() aware of the to gross and qty I want to
multiple together?

Thanks in advance for your help!
Doug

[Michael Bayer]

columns are always from the entity:

.add_column(Item.gross * Item.qty)

eagerload() is not used for joins that you can further filter or order from. You use query.join() for that. See http://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins as well as the note in http://www.sqlalchemy.org/docs/orm/loading.html#sqlalchemy.orm.joinedload regarding this. A specific FAQ entry regarding this is at http://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOINOUTERJOINandSQLAlchemyisnotconstructingthequerywhenItrytoaddaWHEREORDERBYLIMITetc.whichreliesupontheOUTERJOIN .

>
> Thanks in advance for your help!
> Doug
>

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[writeson]

Michael,

Thanks for the quick reply, that helped me better understand joins in
SqlAlchemy, and resolved my problem. Though I've been using SqlAlchemy
for awhile, I still consider myself a "noob", so thanks for your
patience.

Doug

> eagerload() is not used for joins that you can further filter or order from.   You use query.join() for that.     Seehttp://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joinsas well as the note inhttp://www.sqlalchemy.org/docs/orm/loading.html#sqlalchemy.orm.joined...regarding this.    A specific FAQ entry regarding this is athttp://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOIN....