Phalcon Model order by item popularity (number of appearances)

Question

I'm sure I have done something like this before, but can't find it and google not being helpful.

Using Phalcon model if possible, I want to select the items from a table whose ID appears the most - i.e. 10 most popular items ordered by popularity. Is this possible using Model::find("conditions")? I do I have to use PHQL for this?

Answer 1

using model::find

Model::find([
    'columns' => 'id,count(id) as counter',
    'group' => 'id',
    'order' => 'counter DESC'
]);

PHQL:

$this->modelsManager->executeQuery('SELECT count(id) AS counter,id FROM ModelName GROUP BY id ORDER BY counter DESC');

Answer 2

find() does have a group clause, but I don't think it's possible to do what you want because you also need to do a count.

Talal's answer is close, but won't work if you want a list of model objects.

Something like this should work:

$Results = $this->modelsManager->executeQuery('SELECT * FROM ModelName GROUP BY id ORDER BY count(id) DESC LIMIT 10');
$Results->setHydrationMode(\Phalcon\Mvc\Model\Resultset::HYDRATE_RECORDS);

Setting the hydration mode may not be necessary, as Phalcon may default to that mode based on the fact the query is asking for *.