thats a bug.
turning off the eager loading will definitely fix it for now. a
slightly longer shot would be to use a literal as your order by,
"order_by='habitats.id'", but im going out on a limb with that one.
seems like the "aliasing" step of the eager loading is clobbering the
ORDER BY on the inner query when it shouldnt be.
ill fix this soon, probably today since these eager load bugs are
pretty key (its been awhile since we've had a good old eager loading
bug....)
On Jun 19, 2006, at 11:07 AM, Julien Cigar wrote:
> Hello again,
>
> Another strange thing I noticed today. Here is a part of my tables /
> mappers :
>
> invasive_habitats = Table('invasive_habitats', meta,
> Column('invasive_id', Integer, ForeignKey('invasives.id'),
> primary_key=True),
> Column('habitat_id', Integer, ForeignKey('habitats.id'),
> primary_key=True))
>
> invasives = Table('invasives', meta,
> Column('id', Integer, primary_key=True),
> Column('added', DateTime, default=datetime.now()),
> Column('modified', DateTime, default=datetime.now(),
> onupdate=datetime.now()),
> Column('scientific_name', String(200)),
> Column('geographic_range', String(3)),
> (...)
> Column('group_id', Integer, ForeignKey('invasive_groups.id')),
> Column('subgroup_id', Integer, ForeignKey
> ('invasive_subgroups.id')))
>
> mapper(Invasive, invasives, properties = {
> 'names' : relation(InvasiveName, backref='invasive', cascade='all,
> delete-orphan'),
> 'habitats' : relation(Habitat, secondary=invasive_habitats,
> lazy=False),
> 'origins' : relation(Origin, secondary=invasive_origins, lazy=True)
> }
> )
>
> session = create_session()
> query_invasive = session.Query(Invasive)
>
> cond = and_(query_invasive.join_to('habitats'))
> order_by = Habitat.c.habitat
> ##
>
> When I do the following, everything works fine :
> invasive_objects = query_invasive.select(cond, order_by=order_by)
>
> The generated query looks like :
>
> SELECT invasives.nat_dispersal_capacity AS
> invasives_nat_dispersal__1c9f, invasives.intr_accidental AS
> invasives_intr_accidental, invasives.scientific_name AS
> invasives_scientific_name, invasives.impact_disease_transmission AS
> invasives_impact_disease_608, invasives.intr_petornamental AS
> invasives_intr_petornamental, invasives.first_obs_belgium AS
> invasives_first_obs_belgium, invasives.impact_competition AS
> invasives_impact_competition, invasives.geographic_range AS
> invasives_geographic_range, invasives.intr_culture AS
> invasives_intr_culture, invasives.impact_health AS
> invasives_impact_health, invasives.first_obs_flanders AS
> invasives_first_obs_flanders, invasives.id AS invasives_id,
> invasives.trend AS invasives_trend, invasives.black_lists_eppo AS
> invasives_black_lists_eppo, habitats_47b7.id AS habitats_47b7_id,
> habitats_47b7.habitat AS habitats_47b7_habitat,
> invasives.subgroup_id AS
> invasives_subgroup_id, invasives.status AS invasives_status,
> invasives.impact_economic AS invasives_impact_economic,
> invasives.added
> AS invasives_added, invasives.black_lists_sebi AS
> invasives_black_lists_sebi, invasives.nat_dense_populations AS
> invasives_nat_dense_popu_f4af, invasives.impact_global AS
> invasives_impact_global, invasives.nat_natural_habitats AS
> invasives_nat_natural_ha_29c1, invasives.impact_hybridisation AS
> invasives_impact_hybridi_79e2, invasives.intr_fur AS
> invasives_intr_fur,
> invasives.black_lists_belg AS invasives_black_lists_belg,
> invasives.intr_hunting AS invasives_intr_hunting,
> invasives.impact_ecosystem_disruption AS
> invasives_impact_ecosyst_dc3d,
> invasives.modified AS invasives_modified, invasives.intr_canals AS
> invasives_intr_canals, invasives.intr_fishing AS
> invasives_intr_fishing,
> invasives.first_obs_wallonia AS invasives_first_obs_wallonia,
> invasives.intr_deliberated AS invasives_intr_deliberated,
> invasives.impact_predation AS invasives_impact_predation,
> invasives.group_id AS invasives_group_id,
> invasives.nat_reproduction AS
> invasives_nat_reproduction
> FROM invasive_habitats, habitats, invasives LEFT OUTER JOIN
> invasive_habitats AS invasive_habita_d386 ON invasives.id =
> invasive_habita_d386.invasive_id LEFT OUTER JOIN habitats AS
> habitats_47b7 ON habitats_47b7.id = invasive_habita_d386.habitat_id
> WHERE (invasives.id = invasive_habitats.invasive_id AND
> habitats.id = invasive_habitats.habitat_id) AND invasives.status = 1
> ORDER BY habitats_47b7.habitat, invasive_habita_d386.invasive_id
>
> However, if I do :
> invasive_objects = query_invasive.select(cond, order_by=order_by,
> limit=limit, offset=offset)
>
> I got an error :
>
> 2006-06-19 16:55:06 [22183] LOG: statement: SELECT
> invasives.nat_dispersal_capacity AS invasives_nat_dispersal__3468,
> invasives.intr_accidental AS invasives_intr_accidental,
> invasives.scientific_name AS invasives_scientific_name,
> invasives.impact_disease_transmission AS
> invasives_impact_disease_b32f,
> invasives.intr_petornamental AS invasives_intr_petornamental,
> invasives.first_obs_belgium AS invasives_first_obs_belgium,
> invasives.impact_competition AS invasives_impact_competition,
> invasives.geographic_range AS invasives_geographic_range,
> invasives.intr_culture AS invasives_intr_culture,
> invasives.impact_health AS invasives_impact_health,
> invasives.first_obs_flanders AS invasives_first_obs_flanders,
> invasives.id AS invasives_id, invasives.trend AS invasives_trend,
> invasives.black_lists_eppo AS invasives_black_lists_eppo,
> habitats_6ae0.id AS habitats_6ae0_id, habitats_6ae0.habitat AS
> habitats_6ae0_habitat, invasives.subgroup_id AS invasives_subgroup_id,
> invasives.status AS invasives_status, invasives.impact_economic AS
> invasives_impact_economic, invasives.added AS invasives_added,
> invasives.black_lists_sebi AS invasives_black_lists_sebi,
> invasives.nat_dense_populations AS invasives_nat_dense_popu_f47b,
> invasives.impact_global AS invasives_impact_global,
> invasives.nat_natural_habitats AS invasives_nat_natural_ha_299,
> invasives.impact_hybridisation AS invasives_impact_hybridi_46f0,
> invasives.intr_fur AS invasives_intr_fur,
> invasives.black_lists_belg AS
> invasives_black_lists_belg, invasives.intr_hunting AS
> invasives_intr_hunting, invasives.impact_ecosystem_disruption AS
> invasives_impact_ecosyst_2cfe, invasives.modified AS
> invasives_modified,
> invasives.intr_canals AS invasives_intr_canals, invasives.intr_fishing
> AS invasives_intr_fishing, invasives.first_obs_wallonia AS
> invasives_first_obs_wallonia, invasives.intr_deliberated AS
> invasives_intr_deliberated, invasives.impact_predation AS
> invasives_impact_predation, invasives.group_id AS invasives_group_id,
> invasives.nat_reproduction AS invasives_nat_reproduction
> FROM (SELECT invasives.id AS invasives_id
> FROM invasives, invasive_habitats, habitats
> WHERE (invasives.id = invasive_habitats.invasive_id AND
> habitats.id = invasive_habitats.habitat_id) AND invasives.status = 1
> ORDER BY habitats_6ae0.habitat DESC
> LIMIT 200 OFFSET 0) AS rowcount, invasives LEFT OUTER JOIN
> invasive_habitats AS invasive_habita_f18f ON invasives.id =
> invasive_habita_f18f.invasive_id LEFT OUTER JOIN habitats AS
> habitats_6ae0 ON habitats_6ae0.id = invasive_habita_f18f.habitat_id
> WHERE rowcount.invasives_id = invasives.id ORDER BY
> habitats_6ae0.habitat DESC, invasive_habita_f18f.invasive_id
> 2006-06-19 16:55:06 [22183] ERROR: relation "habitats_6ae0" does
> not exist
>
> Does it look like a bug or I missed something about the limit/offset
> clues ?
>
> Thanks !
> Julien
>
>
> _______________________________________________
> Sqlalchemy-users mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
