[Dbix-class] What's the best way to join with an aggregated query in DBIC

Karen Etheridge perl at froods.org
Wed Jan 27 17:56:16 GMT 2021


It sounds like you are doing a correlated subquery -- take a look at
DBIx::Class::Helper::ResultSet::CorrelateRelationship (thank you so much
frew!)

On Wed, Jan 27, 2021 at 5:50 AM Veesh Goldman <rabbiveesh at gmail.com> wrote:

> have you tried ->as_subselect_rs? That should be what you're looking for.
> Just make sure to define your "columns" on the subselect, or else it will
> try to get all the cols from the original table.
>
> On Wed, Jan 27, 2021 at 1:22 PM Robert Rothenberg <robrwo at gmail.com>
> wrote:
>
>> I have an aggregated query
>>
>>    $rs->search_rs( undef, {
>>      columns => [
>>        "me.id",
>>        { total => \ "SUM(me.val)" }
>>      ],
>>      group_by => [ "me.id" ],
>>    } );
>>
>>
>> I would like to join the results with another table, but that requires
>> adding the columns to the group by clause.
>>
>> It's faster to do something like
>>
>>     SELECT a.*, y.* from (select id, sum(val) from x group by id) a join
>> y on a.id=y.x_id
>>
>> Is there a way in DBIC to do that, without creating a virtual result
>> source?
>>
>>
>> _______________________________________________
>> 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
>
> _______________________________________________
> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20210127/2e1edeb5/attachment.htm>


More information about the DBIx-Class mailing list