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

[Howe, Tom (IT)]

I'm not sure if this works but I would try something like...

join => [ { cds=>ripped }, { cds=>ripped } ]

Failing that, if you create the has_many and belongs_to relationships between the tables then you can just use the relationship name instead.


Tom Howe
Morgan Stanley | Technology
25 Cabot Square | Canary Wharf | Floor 03
London, E14 4QA
Phone: +44 20 7425-9380
Tom.Howe at MorganStanley.com


> -----Original Message-----
> From: James Marca [mailto:jmarca at translab.its.uci.edu]
> Sent: 21 October 2008 06:18
> To: DBIx::Class user and developer list
> Subject: [Dbix-class] is joining a table twice with nesting possible?
>
> 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.
>
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
--------------------------------------------------------

NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error.