[Dbix-class] query for m:n relation

[David Schmidt]

On Sun, Oct 3, 2010 at 5:59 PM, David Schmidt <davewood at gmx.at> wrote:
> On Sun, Oct 3, 2010 at 4:59 PM, Charlie Garrison <garrison at zeta.org.au> wrote:
>> Good morning,
>>
>> On 3/10/10 at 2:42 PM +0200, David Schmidt <davewood at gmx.at> wrote:
>>
>>>How to express this query for DBIx::Class is beyond my still evolving
>>>DBIC skills.
>>
>> Without seeing some of your schema, real examples won't be possible. This might get you started.
>>
>> $pictures_rs->search(
>>  {
>>    'tags.id' => { -in => \@tag_ids }
>>  },
>>  {
>>    join => { 'picture_tags' => 'tags' }
>>  }
>> );
>>
>>
>> Charlie
>>
>> --
>>   Ꮚ Charlie Garrison ♊ <garrison at zeta.org.au>
>>
>> O< ascii ribbon campaign - stop html mail - www.asciiribbon.org
>> 〠  http://www.ietf.org/rfc/rfc1855.txt
>>
>> _______________________________________________
>> 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
>>
>
>
> This did the trick. Thanks for your input.
>
>        return $self->search(
>            {
>                tag_id => { -in => \@tag_ids },
>            },
>            {
>                join        => 'picture_tags',
>                group_by    => ['me.id', 'me.name'],
>                having      => { 'count(tag_id)' => { '=', scalar @tag_ids } }
>            }
>        );
>

        return $self->search (
            { map { 'picture_tags' . ($_ ? '_' . ($_+1) : '') .
'.tag_id' => $tag_ids[$_] } (0 .. $#tag_ids) },
            { join => [ ('picture_tags') x @tag_ids ] }
        );

multijoin much faster then group_by&having solution

thanks to ribasushi @ #dbix-class

ps: limit size of @tag_ids