[Dbix-class] has_many and count

[Lee Standen]

Hmm...

$schema->resultset('User')->search( { join => { "Template" => { COUNT => 
{ "Projects"}}}} );

Something along those lines?  Syntax is probably wrong, but you might be 
able to do that....

Jesper Krogh wrote:
> 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/
>>
>>
>>     
>
>
>