Snippets
Create an account or login to be able to add, comment and rate snippets.

Navigation

Simple orderby, groupby and count

$query = new Doctrine_Query();
$query->select('c.*, COUNT(u.id) num_users'); // soon it may be possible to do COUNT(u.id) as num_users like in sql
$query->from('Church c');
$query->leftjoin('c.User u');
$query->orderby('c.name asc');
$query->groupby('c.id');
$churches = $query->execute();
 
foreach($churches AS $church)
{
   echo $church->name.' - '.$church->Users[0]->num_users.'<br/>';
}

That last part is tricky, no real way to know that from any of the documentation that num_users is in $church->users[0]->num_users.

by Jonathan Wage on 2007-04-05, tagged doctrine 

Comments on this snippet

gravatar icon
#1 hansbricks on 2007-04-08 at 06:02

nice snippet. just a note that in the case of a computed column WITHOUT a join, the syntax is a bit different. to wit:

//how many todolists does this user own?
$q = new Doctrine_Query;
$q->select('t.*, count(t.id) tasklist_count')->from('ToDoList t');
$numlists = $q->where('t.user_id = ?', <<id>>)->groupby('t.id')->execute();
//note the difference in the accessor -- no Doctrine DB class required
//as opposed to $numlists[0]->ToDoList[0]->tasklist_count like above would be
echo "This user has {$numlists[0]->tasklist_count} tasklists.";
You need to create an account or log in to post a comment or rate this snippet.