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

[prev] [thread] [next] [Date index for 2005/01/10]

On Mon, 10 Jan 2005 19:12:42 +0000, Tony Bowden <tony-cdbitalk@xxxxx.xxx> wrote:
> On Mon, Jan 10, 2005 at 06:33:31PM +0000, Matt S Trout wrote:
> > It'd also be really nice if ->search allowed you to specify constraints on
> > multiple tables and automatically did the appropriate joins to allow this.
> 
> What would the syntax for this look like?

How about this for a starting point?

@cds = Music::CD->search('cd.year' => 1990, 'artist.name' => 'Neil Young', {
                   order_by => 'cd.artist',
                   joins        => { artist => 'Music::Artist', cd =>
'Music::CD' }
});

We already use the 'hashref at the end' bit for doing ordering, so why
not manage the joins the same way.  It is a bit verbose though.  If we
use the moniker of the class name automatically, maybe this would
work:

@cds = Music::CD->search('cd.year' => 1990, 'artist.name' => 'Neil Young', {
                   order_by => 'cd.artist',
                   joins        => ['Music::Artist']
});

This would do a join on Music::CD to Music::Artist, and label the
tables based on the moniker of the class, or just use the actual
tablenames for the label (cd and artist respectively).

A three level join would look the same:

@cds = Music::CD->search('cd.year' => 1990, 'artist.name' => 'Neil
Young', 'track.name' = 'Old Man', {
                   order_by => 'cd.artist',
                   joins        => ['Music::Artist', 'Music::Track']
});

This does pose the question on how to decide to do the joins?  In this
case both tables would need to be joined to Music::CD.  But what if
the third table needed to be joined to Music::Artist (for example
Music::RecordCompany).  That can probably be figured out by checking
the has_many and has_a relationships.  First check to see if you can
join Music::CD to Music::RecordCompany, and if that fails, join it
with the next entry in the joins list (Music::Artist) until you get a
join that fits.


There is also the issue of doing a self join?  With a self join you
would need to be able to specify the label for each table in the join,
which brings us back to the first method:

@artists = Music::Artist->search('cd1.year' => 1990, cd2.year => 1992, {
                   joins => { cd1 => 'Music::CD', cd2 => 'Music::CD' }
});

If my mind is working right, that should pull out all artists that
released a record in 1990 and in 1992.

Anyway, I am rambling a bit off the top of my head, but maybe this can
spark some discussion on whether generic joins are feasable.

        -- 
        Cees Hek