setting order_by on the mapper itself worked like a charm :)
Thank you much!
On 9/6/06, Michael Bayer <[EMAIL PROTECTED]> wrote:
> hey Pete -
>
> if you can provide a full example that runs, it would be helpful,
> i.e. attach a script that has the partial table definitions, some
> classes/mappers, and a "select()" that makes it acutally happen. i
> can just replace the oracle connect string with something local here.
>
> what is probably happening is a somewhat known issue where SA will
> try to use a LIMIT when selecting just one instance, as it does in
> the import_instance() method. Oracle "limits" are implemented using
> ROW NUMBER OVER ORDER BY(something), so it adds "rowid" when it cant
> find anything else to order by. In theory if you have "order by" set
> up for things it shouldnt have to use rowid. I see below you have a
> lot of "order by"s in there, but you probably need to specify the
> "order by" on the mapper() definition itself, not just the relations
> (in fact you might need it only on the mappers).
>
> if that doesnt work, if you can send me a script to work with i can
> narrow it down...at the very least I can have the oracle compiler not
> allow "rowid" to be used if "DISTINCT" is turned on.
>
> - mike
>
> On Sep 6, 2006, at 10:14 AM, Pete Taylor wrote:
>
> > Hi All,
> > I'm running into a strange problem that I can't seem to constrain to a
> > test case. I'm including the relevant pieces of the tables and
> > relationships as reference for the following description of the
> > problem. And, just for reference, I'm running SA from svn.
> >
> > As you can see below, the Endorsement object is mapped against a
> > specific select statement on the endorsements table, with a set of
> > relationships to the EndorsementDefinition and EndorsementLineItem
> > objects. Endorsements themselves are children of Policy objects.
> >
> > The actual ORA error I'm seeing is:
> > "(DatabaseError) ORA-01446: cannot select ROWID from view with
> > DISTINCT, GROUP BY, etc."
> >
> > The select statement itself is too long to relate here, but the
> > relevant piece is as follows:
> > "ORDER BY distinct_ends_select.rowid,
> > uhe_endorsement_7775.end_endorsement_type ASC,
> > end_endorsement_3b96.end_endorsement_type ASC"
> >
> > When I've run into 01446 before, I've merely added an explict order_by
> > to the relation, and this has taken care of it. But in this case,
> > I've added order_by in each relationship, but the SQL is still being
> > constructed with an attempt to order by rowid.
> >
> > There is a "however" to this whole thing though, and it's a strange
> > one. If I build an Endorsement object explicitly, assigning all the
> > relevant primary keys and then running a session.import_instance
> > against the object, I get the above ORA error. However, if I just
> > allow the Policy object to lazyload the endorsement objects, it
> > retrieves them without any difficulty.
> >
> > Because the relationships seem to work in lazyload, and becaue I've
> > already added all the "order_by" statements I can think to add, I'm
> > not sure where to go next, or how to narrow the whole of it down to a
> > test case. Any help, though, would be greatly appreciated. Thanks!
> >
> > The above references the following set of tables and relationships:
> >
> > policies = Table('pol_homepolicy_tbl', meta,
> > Column('pol_policy_num', String(10), primary_key=True,
> > key='policyNum'),
> > Column('pol_policy_eff_date', DateTime, primary_key=True,
> > key='effectiveDate'),
> > # truncated
> > ...
> > )
> >
> > endorsements = Table('end_endorsement_tbl', meta,
> > Column('end_policy_num', String(10),
> > ForeignKey('pol_homepolicy_tbl.policyNum'), primary_key=True,
> > key='policyNum'),
> > Column('end_endorsement_type', String(20),
> > ForeignKey('uhe_endorsement_tbl.type'), primary_key=True, key='type'),
> > Column('end_endorsement_id', Integer, primary_key=True, key='id'),
> > Column('end_policy_eff_date', DateTime,
> > ForeignKey('pol_homepolicy_tbl.effectiveDate'), primary_key=True,
> > key='policyEffDate'),
> > Column('end_endorsement_edition_date', String(7),
> > ForeignKey('uhe_endorsement_tbl.editionDate'), key='editionDate'),
> > # truncated
> > ...
> > )
> >
> > endorsementDefinitions = Table('uhe_endorsement_tbl', meta,
> > Column('end_endorsement_type', String(10), primary_key=True,
> > key='type'),
> > Column('uhe_effect_date', DateTime, primary_key=True,
> > key='effectiveDate'),
> > Column('end_endorsement_desc', String(100), key='description'),
> > Column('end_endorsement_form_id', String(12), key='formNumber'),
> > Column('r_end_ed_date', String(7), key='editionDate')
> > )
> >
> > EndorsementDefinition.mapper = mapper(EndorsementDefinition,
> > endorsementDefinitions)
> >
> > EndorsementLineItem.mapper = mapper(EndorsementLineItem, endorsements)
> >
> > distinct_ends_select = select(
> > [
> > endorsements.c.policyNum,
> > endorsements.c.policyEffDate,
> > endorsements.c.type,
> > endorsements.c.editionDate
> > ],
> > order_by=[asc(endorsements.c.type)],
> > distinct=True,
> > ).alias('distinct_ends_select')
> >
> > Endorsement.mapper = mapper(Endorsement, distinct_ends_select,
> > is_primary=True, properties=dict(
> > definition = relation(EndorsementDefinition, lazy=False,
> > private=False, order_by=asc(endorsementDefinitions.c.type),
> > primaryjoin=and_(
> >
> > distinct_ends_select.c.end_endorsement_type==endorsementDefinitions.c.
> > type,
> >
> > distinct_ends_select.c.end_endorsement_edition_date==endorsementDefini
> > tions.c.editionDate,
> > ),
> > ),
> > lineItems = relation(EndorsementLineItem, lazy=False,
> > private=True, order_by=asc(endorsements.c.type),
> > foreignkey=(
> > endorsements.c.policyNum,
> > endorsements.c.policyEffDate,
> > endorsements.c.type,
> > ),
> > primaryjoin=and_(
> >
> > distinct_ends_select.c.end_policy_num==endorsements.c.policyNum,
> >
> > distinct_ends_select.c.end_policy_eff_date==endorsements.c.policyEffDa
> > te,
> >
> > distinct_ends_select.c.end_endorsement_type==endorsements.c.type
> > )
> > )
> > ))
> >
> > Policy.mapper = mapper(Policy, policies, extension=PolicyExtension(),
> > properties=dict(
> > endorsements = relation(Endorsement, lazy=True, private=True,
> > order_by=asc(distinct_ends_select.c.end_endorsement_type)),
> > # truncated
> > ...
> > ))
> >
> > --
> > "All guilt is relative, loyalty counts, and never let your conscience
> > be your guide."
> > - Lucas Buck, American Gothic
> >
> > ----------------------------------------------------------------------
> > ---
> > Using Tomcat but need to do more? Need to support web services,
> > security?
> > Get stuff done quickly with pre-integrated technology to make your
> > job easier
> > Download IBM WebSphere Application Server v.1.0.1 based on Apache
> > Geronimo
> > http://sel.as-us.falkag.net/sel?
> > cmd=lnk&kid=120709&bid=263057&dat=121642
> > _______________________________________________
> > Sqlalchemy-users mailing list
> > Sqlalchemy-users@lists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
>
>
--
"All guilt is relative, loyalty counts, and never let your conscience
be your guide."
- Lucas Buck, American Gothic
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
