Count and group-by with Propel

Question

In 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?

Scullery

Answer 1

Damned! It's easier than that!

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

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

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

If you want to add a count or nb extra column to your query which represent a SQL aggregate functions like 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();

Diapophysis

I think the OP was looking for something more complicated than just a basic count - something like SELECT name, COUNT(*) FROM x GROUP BY name (the SELECT * in the question may be a red herring) – Metacarpus

Be fair, and read the documentation. For your query, just write MyTableQuery::create()->withColumn('COUNT(*)', 'Count')->select(array('Name', 'Count'))->groupByName()->find(). In both case, you're on the wrong path. Your down vote is not justified at all. – Diapophysis

It's not my question, but nevertheless that's a much better answer! Would you edit your original response? (I politely disagree about justification, since your reply did not answer the question; however if you fix your answer I will happily revert the downvote). – Metacarpus

I answered based on the title, I mis-read the code... Anyway, edited. – Diapophysis

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 snippets of info on propel queries here -> http://snippets.symfony-project.org/snippets/tagged/criteria/order_by/date

Mycetozoan

You should avoid the use of criteria. – Diapophysis

@WilliamDURAND care yo expand on that ? any particular reason ? – Mycetozoan

The ActiveQuery API is much better, really fluent, and it's the recommended API to use since 1.5. Propel2 will focus on the ActiveQuery approach, not on criteria/criterion. More hints on this blog post: propel.posterous.com/design-your-queries-like-a-boss – Diapophysis

Answer 3

Something like this:

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

I'm not sure about the GROUP BY - you may need an alias, or to re-specify the COUNT clause. Try it and see what works by experimentation :)

You really must use an auto-completing IDE to take advantage of Propel (and Doctrine for that matter) - your queries will be much easier to construct.

My usual reply would normally be like @ManseUK's, i.e. based on the Criteria class - but that will be phased out when Propel 2 comes along, so it's probably a good idea to get your code ready now.

Metacarpus

You don't need to write the query by yourself. Just think Propel provides termination methods like find(), and count() for instance. Propel aims to be as simple as possible ;) – Diapophysis

As noted on your answer, a simple count() would be insufficient. – Metacarpus