8

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?

halfer
  • 19,824
  • 17
  • 99
  • 186
Johan Dannenberg
  • 81
  • 1
  • 1
  • 2

3 Answers3

13

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();
William Durand
  • 5,439
  • 1
  • 26
  • 37
  • 1
    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) – halfer Mar 10 '12 at 13:20
  • 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. – William Durand Mar 10 '12 at 13:27
  • 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). – halfer Mar 10 '12 at 13:29
  • I answered based on the title, I mis-read the code... Anyway, edited. – William Durand Mar 10 '12 at 13:47
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

Manse
  • 37,765
  • 10
  • 83
  • 108
  • You should avoid the use of criteria. – William Durand Mar 10 '12 at 13:00
  • @WilliamDURAND care yo expand on that ? any particular reason ? – Manse Mar 10 '12 at 13:31
  • 1
    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: http://propel.posterous.com/design-your-queries-like-a-boss – William Durand Mar 10 '12 at 13:45
1

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
  • 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 ;) – William Durand Mar 10 '12 at 13:10
  • 1
    As noted on your answer, a simple `count()` would be insufficient. – halfer Mar 10 '12 at 19:44