many-to-many relationship and sorting

[prev] [thread] [next] [Date index for 2005/03/09]

You're right!
But what sense do the relations the way they currently are have???
In EVERY case I'd need them I have to use a workaround!

Sorting in perl is not an option for me, sql should do this (better and f=
aster).
So what would be needed to make this work is the option requested a few t=
imes already that search can filter and order based on values of foreign =
tables...

-Alex


-----Urspr=FCngliche Nachricht-----
Von: William Ross [mailto:will@xxxxxxx.xxx]=20
Gesendet: Mittwoch, 9. M=E4rz 2005 12:37
An: Hartmaier Alexander
Cc: cdbi-talk@xxxxxx.xxxxx.xxx
Betreff: Re: many-to-many relationship and sorting

On 9 Mar 2005, at 11:26, Hartmaier Alexander wrote:

> So something like that would do the trick?
>
> my @films =3D map { $_->film } Role->search( actor =3D> $actor, {order_=
by =20
> =3D> 'name'} );

not quite: you're still applying the order_by criterion to the roles =20
table. this makes a sort of sense in the case of roles and actors, but =20=

in your original question the goal was to sort by a property of the =20
actor, not the role, I believe?

As Perrin originally said, you do have a few simple options: write out =20=

the m2m join with set_sql, use one of the manytomany modules that =20
people have mentioned, or hang the expense and sort them in perl after =20=

retrieval.

will


>
> -Alex
>
>
> -----Urspr=FCngliche Nachricht-----
> Von: William Ross [mailto:will@xxxxxxx.xxx]
> Gesendet: Mittwoch, 9. M=E4rz 2005 12:14
> An: Hartmaier Alexander
> Cc: cdbi-talk@xxxxxx.xxxxx.xxx
> Betreff: Re: many-to-many relationship and sorting
>
> On 9 Mar 2005, at 10:29, Hartmaier Alexander wrote:
>
>> How does the mapping method work internally?
>> I thought it retrieves all id's of the foreign class (table) with a
>> single sql statement...then only an ORDER BY sql clause would be
>> needed...
>
> The has_many(x =3D> [y =3D> z]) shortcut you're using doesn't change th=
e
> SQL generated by has_many: it just adds an extra inflation step after
> the foreign ids have been retrieved from the linking table. The
> destination table in a m2m relationship is never touched, so there is
> no chance to order the results by something in that table.
>
> If you look at Class::DBI::Relationship::HasMany->_hm_run_search,
> you'll see that it just calls search() on the linking class. So with
> this:
>
> 	Actor->has_many(films =3D> [ Role =3D> 'film' ]);
>
> these two statements are _exactly_ the same:
>
> 	my @films =3D $actor->films;
> 	my @films =3D map { $_->film } Role->search( actor =3D> $actor );
>
> I thought Matt's HasManytoMany looked good: thoughtful and
> well-constructed, and it will do what you need. the documentation is a
> bit sparse, though :)
>
> best
>
> will
>
>
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*=
=20
> "*"*"*
> Hinweis: Dieses E-mail kann vertrauliche und gesch=FCtzte Informationen=
 =20
> enthalten.
> Sollten Sie nicht der beabsichtigte Empf=E4nger sein, verst=E4ndigen Si=
e =20
> bitte den Absender und l=F6schen Sie dieses E-mail dann sofort.
>
> Notice: This e-mail contains information that is confidential and may =20=

> be privileged.
> If you are not the intended recipient, please notify the sender and =20=

> then delete this e-mail immediately.
> *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*=
=20
> "*"*"*


*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*=
"*"*
Hinweis: Dieses E-mail kann vertrauliche und gesch=FCtzte Informationen e=
nthalten.
Sollten Sie nicht der beabsichtigte Empf=E4nger sein, verst=E4ndigen Sie =
bitte den Absender und l=F6schen Sie dieses E-mail dann sofort.

Notice: This e-mail contains information that is confidential and may be =
privileged.
If you are not the intended recipient, please notify the sender and then =
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*=
"*"*