[Dbix-class] pick and choose columns with join + collapse
Seth Daniel
dbix-class at sethdaniel.org
Thu Feb 5 20:56:55 GMT 2015
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20150205/895bba34/attachment.htm>
More information about the DBIx-Class
mailing list