Feature request - Allow searching columns of related tables in search() and search_like()

[prev] [thread] [next] [Date index for 2005/02/17]

Any news on this?
I'd need searching and ordering by fields of foreign tables all the time!=


I didn't look at the CDBI internals but wouldn't it be the easiest way to=
 create an sql query which just returns all matching primary id's of the =
searched object and then retrieve all ESSENTIAL columns?

The 'order by' can be done the same way...

-Alex


-----Urspr=FCngliche Nachricht-----
Von: Cees Hek [mailto:ceeshek@xxxxx.xxx]=20
Gesendet: Mittwoch, 12. Januar 2005 17:14
An: tony@xxxxx.xxx; cdbi-talk@xxxxxx.xxxxx.xxx
Betreff: Re: Feature request - Allow searching columns of related tables =
in search() and search_like()

On Tue, 11 Jan 2005 09:19:05 +0000, Tony Bowden <tony-cdbitalk@xxxxx.xxx>=
 wrote:
> What would you like to see it do that it doesn't at the moment?

Well, since this is setup as a relationship, you need an object before
you can use this method.  So to use the example you provided
(Star->manymany(movies =3D> [ qw/Role Movie/ ]);), you have to have a
Star object before you can call the movies method.  This means your
result set will always be constrained to a single star.

It would be nice to reduce it one more level to make it work as a
class method where we can place conditions on the Star table as well.=20
Maybe it could work like add_constructor does by creating a custom
method for the class, but extend it so it can work across
relationships (really what I am thinking of is a generic way to build
methods explained in the Wiki on UsingJoins [1], particularly the last
entry by Randal).

This would complement what you have provided in the ManyMany
relationship, which is added to a class, but returns a list of objects
from another class, whereas this would return a list of objects in the
current class.

Maybe something like this at it's simplest level:

CD->add_???(artist_and_track_where =3D> [ qw/Artist Track/ ]);

Note that in this case CD has_a Artist and CD has_many tracks, so we
are using the middle table in the relationship.  You might use it like
this:

@cds =3D CD->search_artist_and_track_where(<<"", 13)
    track.num =3D ? ORDER BY artist.name, cd.title

- or using SQL::Abstract -

@cds =3D CD->search_artist_and_track_where(
      { 'track.num' =3D> 13},
      { order_by  =3D> "artist.name, cd.title" }
);

This would return all CDs with at least 13 tracks, and order the
results by Artists name and cd title.

This could probably be done as an third party module like
Class::DBI::AbstractSearch as well...

Cheers,

Cees

[1] http://www.class-dbi.com/cgi-bin/wiki/index.cgi?UsingJoins

*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*=
"*"*
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.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*=
"*"*