pick and choose columns with join + collapse

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

pick and choose columns with join + collapse

Seth Daniel
I have two tables with a many-to-many relationship, say:

Table A    A_B (Join Table)   Table B

Any A can have many B, and any B can be assigned to any A.  All via the join table. Each of A and B has an 'id' and 'name' column (and likely many other columns that are unimportant for this example). 

If I want to find all A that have any one B assigned to it *and* retrieve all B for the selected A *and* do this in one query I've been doing this:

my $rs = $s->resultset( 'A' )              
           ->search( { 'B.name' => 'some_name' },
                     { prefetch => [ { A_B => 'B' },
                                     { A_B => 'B' } ] } );

I list the prefetch twice. If I only list it once I only get those B that match the search criteria, instead of all B for the selected A.

I now want to do the same thing, but only return those columns from A and B that I want.  So I tried this:

my $rs = $s->resultset( 'A' )              
           ->search( { 'B.name' => 'some_name' },
                     { columns => [ 'id', { 'A_B.B.name' => 'B.name' } ],
                       collapse => 1,
                       join => [ { A_B => 'B' },
                                 { A_B => 'B' } ] } );

This does return only the columns I ask for, but it also only returns the B that match the search criteria instead of all B for the selected A. 

Is there a way I can get the same results as my first example, but by only returning the columns like from  my second example?

I've been using DBIx::Class 0.082810. 

Thank you.

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: pick and choose columns with join + collapse

Seth Daniel


On Fri, Feb 6, 2015 at 12:29 AM, Peter Rabbitson <[hidden email]> wrote:
On 02/05/2015 09:56 PM, Seth Daniel wrote:

...


my $rs = $s->resultset( 'A' )
            ->search( { 'B.name' => 'some_name' },
                      { columns => [ 'id', { 'A_B.B.name => 'B.name' } ],
                        collapse => 1,
                        join => [ { A_B => 'B' },
                                  { A_B => 'B' } ] } );

This does return only the columns I ask for, but it also only returns
the B that match the search criteria instead of all B for the selected A.

Yes - you asked for B.name to be put into A_B.B.name and you also referenced it in the where condition. What you want is (likely)
columns => [ 'id', { 'A_B.B.name => 'B_2.name' }

Check the generated DBIC_TRACE=1 (and maybe in addition DBIC_TRACE_PROFILE=console) to easier wrap your head around what is going on.

Cheers

 That was it.  Thank you very much.


_______________________________________________
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@...
Loading...