with_objects and sort doesn't work right
Michael Lackhoff
behandlungen => {
type => 'one to many',
class => 'MyApp::Behandlungen',
column_map => { nr => 'rezept_id' },
manager_args => {
sort_by => q{CASE WHEN (termin ISNULL OR termin = '') THEN '9999'
ELSE termin END, t1.nr},
# with_objects => 'leistungen',
},
},
The sort_by works as long I leave the with_objects out. If I enable it I
get this SQL (only the sort part, taken from debug output):
ORDER BY t1.nr, CASE WHEN (termin ISNULL OR termin = '') THEN '9999'
ELSE termin END, t1.nr
That is, somehow an additional t1.nr came in at the beginning of the
ORDER BY clause and defeats the whole purpose. Without the
"with_objects" I get the correct:
ORDER BY CASE WHEN (termin ISNULL OR termin = '') THEN '9999' ELSE
termin END, t1.nr
Is this a bug or did I misunderstand something? I would like to keep the
with_object since it gives quite a performance improvement.
-Michael
John Siracusa
> Is this a bug or did I misunderstand something? I would like to keep the
> with_object since it gives quite a performance improvement.
See the last paragraph of the sort_by documentation:
http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/Manager.pm#sort_by
RDBO is attempting to enforce a sorting order that allows it to
properly attach child objects to the correct parents. Here's another,
older thread on the topic:
http://groups.google.com/group/rose-db-object/browse_thread/thread/93750c9e23b39a28
There is actually an undocumented (and generally unsupported)
no_forced_sort boolean Manager call parameter that you can set to true
to disable the forced sort, but using it may cause your child objects
to end up attached to the wrong parents.
-John
Michael Lackhoff
> On Fri, Nov 13, 2009 at 8:44 AM, Michael Lackhoff <mic...@lackhoff.de> wrote:
>> Is this a bug or did I misunderstand something? I would like to keep the
>> with_object since it gives quite a performance improvement.
>
> See the last paragraph of the sort_by documentation:
>
> http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/Manager.pm#sort_by
Thanks for the explanation!
> There is actually an undocumented (and generally unsupported)
> no_forced_sort boolean Manager call parameter that you can set to true
> to disable the forced sort, but using it may cause your child objects
> to end up attached to the wrong parents.
Is there a way I can test if there is a real risk in my situation? Is it
enough to get an example where the different sort actually makes a
difference, check that everything is o.k. in this example and then
assume everything is safe in this particular relationship?
-Michael
John Siracusa
> Is there a way I can test if there is a real risk in my situation? Is it
> enough to get an example where the different sort actually makes a
> difference, check that everything is o.k. in this example and then
> assume everything is safe in this particular relationship?
The order a db returns rows in the absence of any explicit ORDER BY
clause that affects them is usually undefined. Sometimes rows are
returned inteh orde rthey were inserted, sometimes in their physical
order in the db, sometimes who knows? And with joins its the
multiplication of those possibilities, or new ones entirely.
RDBO wants all child rows to be with their parents, for each level of
parent/child in a join. So this kind of order is fine:
A A-Child-2
A A-Child-1
A A-Child-3
B A-Child-2
B A-Child-1
Because all of the As and their children are together before moving
onto the Bs, but this order is not okay:
A A-Child-2
A A-Child-1
B A-Child-2
A A-Child-3
B A-Child-1
And so on, for sub-children. This is okay:
A A-Child-2 A-Child-2's-Child-2
A A-Child-2 A-Child-2's-Child-1
A A-Child-1 A-Child-1's-Child-1
A A-Child-1 A-Child-1's-Child-2
but this is not okay:
A A-Child-2 A-Child-2's-Child-2
A A-Child-1 A-Child-1's-Child-1
A A-Child-2 A-Child-2's-Child-1
A A-Child-1 A-Child-1's-Child-2
-John
Michael Lackhoff
> RDBO wants all child rows to be with their parents, for each level of
> parent/child in a join. So this kind of order is fine:
[...]
I think I will stay on the safe side and leave the sort out of the
relationship and have a dedicated method that takes the relationship and
sorts it with perl-sort.
-Michael