We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

How to order by count of related model?

I have a model Poem, which hasMany PoemLike.

class Poem extends \Phalcon\Mvc\Model
{
    /**
     * Define relationships to PoemLike
     */
    public function initialize()
    {
        $this->hasMany('id', 'PoemLike', 'poem_id', array(
            'alias' => 'likes',
        ));
    }
}

I want find top 10 poems with most likes, How could I do this?



98.9k
Accepted
answer 
$poems = $this->modelsManager->createBuilder()
    ->colums('Poem.id', 'number' => 'COUNT(*)')
    ->from('Poem')
    ->join('PoemLike')
    ->orderBy('number')
    ->groupBy('Poem.id');
    ->getQuery()
    ->execute();


5.2k
in reply to Phalcon

Hi, Let's say that every Poem has on catalog,

how can I get catalog sort by poem liked?

$poems = $this->modelsManager->createBuilder()
  ->colums('Poem.id', 'number' => 'COUNT(*)')
   ->from('Poem')
   ->join('PoemLike')
   ->orderBy('number')
  ->groupBy('Poem.id');
   ->getQuery()
   ->execute();