[Dbix-class] is joining a table twice with nesting possible?

[James Marca]

Hi all,

I want to get two different values from nested join tables.  Borrowing
from the excellent Cookbook examples:

  If the same join is supplied twice, it will be aliased to <rel>_2 (and
  similarly for a third time). For e.g.

    my $rs = $schema->resultset('Artist')->search({
      'cds.title'   => 'Down to Earth',
      'cds_2.title' => 'Popular',
    }, {
      join => [ qw/cds cds/ ],
    });
  
  will return a set of all artists that have both a cd with title 'Down
  to Earth' and a cd with title 'Popular'.


My application isn't like this, but it is close enough.  Suppose cds
joins to another table called "ripped", which contains the raw 'wav'
file plus some metadata columns, say the length of the wav file.

Suppose I want to select artist of Down to Earth and Popular, plus the
file length, but not the raw wav file.  I want to do something like

    my $rs = $schema->resultset('Artist')->search({
      'cds.title'   => 'Down to Earth',
      'cds_2.title' => 'Popular',
    }, {
      join => { cds=>ripped,
                cds=>ripped },
      '+select' => [ 'ripped.length', 'ripped_2.length' ]
    });

But of course that won't work, because the hash just aliases out the
second entry.  But if I just do 

    my $rs = $schema->resultset('Artist')->search({
      'cds.title'   => 'Down to Earth',
      'cds_2.title' => 'Popular',
    }, {
      join => [ qw/cds cds/ ],
      '+select' => [ 'ripped.length', 'ripped_2.length' ]
    });
  
The search will fail with a complaint like "Unknown column
'ripped.length' in 'field list'
  
I want to avoid prefetch, because I don't want the big "wav" column.
I have a solution that gets a list of artist, one for 'down to earth'
and one for 'popular', but then I have to post process the list.

I was going to just hack on a select clause, but the docs were quite
severe that one should ask the mailing list first...
Thanks in advance for any  tips on this,

James


--
James E. Marca
Institute of Transportation Studies
University of California
Irvine, CA 92697-3600

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.