Julien -
can I get a full test case on this one ? theres a "AND
invasives.status = 1" in the join condition for the queries you are
showing me but that is not in the mapper setup you are showing me;
when I run a basic many-to-many with three tables I cannot reproduce
the error you are getting.
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
