Count and group with Propel

Question

In the Doctrine, I can do:

public function getCount() { $q = $this->createQuery('q') ->select('*') ->addSelect('count(q.name) as count') ->groupBy('q.name') ->orderBy('count DESC'); return $q->execute(); } 

How can I do the same in Propel in Symfony 1.4?

Answer 1

Damned! It's easier than that!

If you need to count the result lines for a given query, you need to use the count() completion method, basically:

 MyTableQuery::create()->count(); 

Read more in the following section of the documentation: http://www.propelorm.org/documentation/03-basic-crud.html#query_termination_methods

If you want to add an extra count or nb column to your query, which is an aggregate SQL function, such as count or SUM , then you should use the withColumn() method:

 $query = MyTableQuery::create() ->withColumn('COUNT(*)', 'Count') ->select(array('Name', 'Count')) ->groupByName() ->orderByCount() ; $results = $query->find(); 

Answer 2

try:

 public function getCount() $c = new Criteria(); $c->addAsColumn('count', 'count(name)'); $c->addDescendingOrderByColumn($c->getColumnForAs('count')); $c->addGroupByColumn('name'); return self::doCount($c); } 

There are some good pieces of information about propel queries here → http://snippets.symfony-project.org/snippets/tagged/criteria/order_by/date

Answer 3

Something like that:

 $myRows = MyTableQuery::create()-> addAsColumn('count', 'COUNT(name)')-> addGroupByColumn('count')-> addDescendingOrderByColumn('count')-> find(); 

I'm not sure about GROUP BY - you might need an alias or override the COUNT clause. Try it and see what works in the experiment :)

You really have to use IDE autocompletion to use Propel (and the doctrine, for that matter) - your queries will be much easier to build.

My usual answer would usually be similar to @ManseUK, i.e. based on the Criteria class, but this will be canceled when Propel 2 appears, so it's probably a good idea to prepare your code now.