On Feb 4, 8:13 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On Feb 4, 2008, at 11:29 AM, Utku Altinkaya wrote:
>
>
>
> > Greetings,
>
> > I have Users class with relation to Address which have relation to
> > Cities, all are eager loaded. I want to sort the Users query with the
> > name field of the cities table. But if I order by City.name the cities
> > table is joined to the actual query. How can I explain it to use the
> > cities.name field in the join statement ? Or any different are welcome
> > also.
>
> two ways.
>
> historically we've told people not to rely upon eager loading for
> ordering or join criterion:
>
> http://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOIN...
>
> while that FAQ entry seems kind of finalish, you can achieve some
> basic ordering against the eager load if you set order_by=None on the
> parent mapper (or set it to None with your Query;
> query.order_by(None)), and then set order_by as desired on each
> relation() (which can also be None, such as on your Address
> ordering). that way the ordering of the eagerly loaded collection
> affects the ordering overall.
I get it, the result with joins for eager loading has nothing to do
with sorting. So I have to join them to base selected set to use. But
I feel like selecting twice, is there a peformance penalty here?
properties =
properties.select_from(data.properties_table.join(data.addresses_table.join(data.districts_table)))
SELECT anon_1.properties_address_id AS anon_1_properti
es_address_id ,
anon_1.properties_id AS
anon_1_properties_id ,
anon_1.properties_ctime AS
anon_1_properties_ctime ,
anon_1. properties_utime AS
anon_1_properties_utime ,
anon_1.properties_category AS
anon_1_properties_category ,
anon_1.properties_default_image_id AS
anon_1_properties_default_image_id,
towns_1.id AS
towns_1_id ,
towns_1.city_id AS
towns_1_city_id ,
towns_1.name AS
towns_1_name ,
towns_1.lat AS
towns_1_lat ,
towns_1.lng AS
towns_1_lng ,
cities_1.id A S
cities_1_id ,
cities_1.name AS
cities_1_name ,
cities_1.lat AS
cities_1_lat ,
cities_1.lng AS
cities_1_lng ,
cities_1.acod e AS
cities_1_acode ,
districts_1.id AS
districts_1_id ,
districts_1.town_id AS
districts_1_town_id ,
districts_1.name AS d
istricts_1_name ,
districts_1.lat AS
districts_1_lat ,
districts_1.lng AS
districts_1_lng ,
addresses_1.id AS addresses_1_i
d ,
addresses_1.city_id AS
addresses_1_city_id ,
addresses_1.town_id AS
addresses_1_town_id ,
addresses_1.district_id AS ad
dresses_1_district_id ,
addresses_1.address AS
addresses_1_address ,
addresses_1.phone AS
addresses_1_phone ,
addresses_1.p hone2 AS
addresses_1_phone2 ,
addresses_1.phone_cell AS
addresses_1_phone_cell ,
addresses_1.fax AS
addresses_1_fax ,
addre sses_1.lat AS
addresses_1_lat ,
addresses_1.lng AS addresses_1_lng
FROM
(SELECT properties.address_id AS properties_address_id ,
properties.id AS properties_id ,
properties.ctime AS propert ies_ctime ,
properties.utime AS properties_utime ,
properties.status AS properties_status ,
properties.kind AS properties_k ind ,
properties.category AS properties_category ,
properties.price AS properties_price ,
properties.price_cur AS propertie s_price_cur ,
properties.deposit AS properties_deposit ,
properties.deposit_cur AS properties_deposit_cur,
properties.user _id AS properties_user_id ,
properties.company_id AS properties_company_id ,
properties.published AS properties_published ,
properties.shared AS properties_shared ,
properties.ev2_id AS properties_ev2_id ,
properties.default_image_id AS properti
es_default_image_id
FROM properties
INNER JOIN (addresses
INNER JOIN districts
ON districts.id = addresses.district_id)
ON addresses.id = pr operties.address_id
ORDER BY properties.utime DESC LIMIT 0,
10
) AS anon_1
LEFT OUTER JOIN addresses AS addresses_1
ON addresses_1.id = anon_1.properties_address_id LEFT O
UTER
JOIN towns AS towns_1
ON towns_1.id = addresses_1.town_id
LEFT OUTER JOIN cities AS cities_1
ON cities_1.id = addre sses_1.city_id
LEFT OUTER JOIN districts AS districts_1
ON districts_1.id = addresses_1.district_id
ORDER BY anon_1.prop erties_utime DESC,
addresses_1.id ,
towns_1.id ,
cities_1.id ,
districts_1.id 2008-02-04 22:35:11,562 INFO
sqlalchemy.engine.base.Engine.0x..90 [] 2008-02-04 22:35:11,671 INFO
sqlalchemy.engine.base.Engine.0x..90
SELECT companies.id AS companies_id ,
companies.ctime A S companies_ctime ,
companies.kind AS companies_kind ,
companies.name AS companies_name ,
FROM companies
WHERE companies.id = %s
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
