[Dbix-class] has_many and count

Jesper Krogh jesper at krogh.cc
Tue Aug 8 10:07:54 CEST 2006


I'm not sure that I were able to explain myself correct.. but this does
what I want:

my $templates = $schema->resultset("Template")->search({},{prefetch =>
[qw/user/], "join" => [qw/projects/],'+select' => [ {count =>
"projects.projectid"}],'+as' => "count",  group_by => [(map { "me.$_" }
$schema->resultset("Template")->result_source->columns()),(map { "user.$_"
} $schema->resultset("User")->result_source->columns())]});

A user has many templates that has many projects.

I'd like to do a:

select (all from user),(all from template), count(projects) from
user,template,projects where <join-conditions> group by (all from
user),(all from project);

Using "distinct => 1" gives me the "group by all columns in template" but
leaves out the user (gotten via prefetch) and adds in the "+select" part
in the group_by, which makes the DB blow up.

Can the above case be constructed more "elegantly".


> $schema->resultset('someresultset')->search(...)->count();
>
>
> ?
>
>
>
> count
> <http://search.cpan.org/%7Emstrout/DBIx-Class-0.07000/lib/DBIx/Class/Resu
> ltSet.pm#___top>
>
> Arguments: $cond, \%attrs??
> Return Value: $count
>
>
> Performs an SQL |COUNT| with the same query as the resultset was built
> with to find the number of elements. If passed arguments, does a search on
> the resultset and counts the results of that.
>
> Note: When using |count| with |group_by|, DBIX::Class
> <http://search.cpan.org/perldoc?DBIX%3A%3AClass> emulates |GROUP BY|
> using |COUNT( DISTINCT( columns ) )|. Some databases (notably SQLite) do
> not support |DISTINCT| with multiple columns. If you are using such a
> database, you should only use columns from the main table in your
> |group_by| clause.
>
>
>
>
> Jesper Krogh wrote:
>
>> Hi.
>>
>>
>> I cant get DBIx::Class og make this query.. I have a table with a
>> has_many relation.
>>
>> Company has many Persons, that has many projects..
>>
>>
>> Company (id,name,address).
>> Person (id,company_id,name,address)
>> Projects (id,person_id,name)
>>
>>
>> I'd like to create a view that list persons with their company name and
>> their project-count and be sortable in the database on both elements
>> from the company-table and the count from the projects table.
>>
>> I bumped into "distinct => 1" but when I add "+select =>
>> [\'count(projects.projectid')] it tries to group by this element, which
>> the database complains about.
>>
>> An example output:
>>
>>
>> Company_id,Cname, Caddress, Pid, Pname, Paddres, Count
>> 1,test,test2,2,Jesper,Krogh,7
>> 1,test,test2,3,Test,Test2,3
>>
>>
>> Getting data from the company table is "quite-easy" just putting it
>> into the "prefetch" => [qw/company/], but the "has_many group by count
>> stuff is "harder".
>>
>>
>> Any suggestions?
>>
>>
>> Jesper
>>
>>
>>
>
>
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive:
> http://www.mail-archive.com/dbix-class@lists.rawmode.org/
>
>


-- 
Jesper Krogh




More information about the Dbix-class mailing list