On Nov 19, 2011, at 7:42 PM, Hector Blanco wrote:
> As you can see, only two of those classes have a "_name" attribute.
>
> What I'd like to know is if I can set an "order_by" in the
> BaseObject._children relationship that does the following:
> 1) Sort by type, but not the default ordering. If I set up (in
> _children) the order_by to order by _polymorphicIdentity (by the
> discriminator), I'd get instances of type "Element1", then instances
> of "Element2" and finally, from "Element3". I don't want that. I need
> to get "Element1", "Element3" and finally, "Element2".
> 2) Sort by name if the child has a name. If it doesn't, leave it with
> the 'special' _polymorphicIdentity described above if the child class
> doesn't have a _name attribute.
two things. First off, you have collection_class=set with an order_by.
You'll want to change that as sets aren't ordered.
next thing. Don't think about mappers and classes here, think of the SQL.
You're basically asking for a query like this:
SELECT * from base_objects
LEFT OUTER JOIN elements_1 ON <onclause>
LEFT OUTER JOIN elements_2 ON <onclause>
LEFT OUTER JOIN elements_3 ON <onclause>
ORDER BY
CASE
WHEN base_object._polymorphic_identity='Element1' THEN 'A'
WHEN base_object._polymorphic_identity='Element3' THEN 'B'
WHEN base_object._polymorphic_identity='Element2' THEN 'C'
END,
CASE
WHEN base_object._polymorphic_identity='Element1' THEN
elements_1.name
WHEN base_object._polymorphic_identity='Element2' THEN
elements_2.name
ELSE NULL
END
This is assuming you wanted to sort by both the polymorphic type as well as the
name; it wasn't clear if you wanted to sort just by name with type as a
fallback instead. You'd put the first CASE into the ELSE clause of the second
for that.
So yes, of course you can set up the mappings to work this way, though it would
be tedious to get it working, as the order_by needs to be derived from all
subtables. A key issue as well is that its not really possible to use a
different "with_polymorphic" in the relationship() as that of the mapper by
itself, which means the LEFT OUTER JOIN business above would be emitted in all
cases, unless *perhaps* a secondary mapper were used but I really don't know
that those can be configured polymorphically like that. So at the mapping
level you could barely get this going and it would be pretty scary looking.
Beyond that, it would add an enormous amount of complexity to your SQL queries
and perform quite poorly as LEFT OUTER JOIN optimizes pretty badly. Complex
queries derived from the core here would produce very large, multi-page queries
as this is a recursive structure onto itself already. A schema like this
coupled with the ordering requirement would only be suitable for small to
medium scales of data with only modest performance.
Given all that, it would be my recommendation that you do the sorting in
Python. Add an __lt__() method to your classes that defines this sorting based
on attributes, then add a @property-style accessor to BaseObject that just uses
sorted(self._children). That way you could even keep your
collection_class=set on the _children relationship itself. The mapping goes
back to being simple, or at least as simple as the "joined" case calls for.
>
> I don't even know if that's possible.
> Because of certain dependencies, I can't use SqlAlchemy 0.7.x yet. I
> am using 0.6.8
>
> Thank you very much in advance!
>
> --
> 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.
>
--
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.
