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

Fetch count of Model::query() instead of records

This is my code to fetch projects which are related to a user:

$query = Project::query()
    ->leftJoin('ProjectUser', 'ProjectUser.project_id = Project.id')
    ->where('ProjectUser.user_id = :userId:', array('userId' => $user->id))
    ->orderBy('Project.last_activity_at DESC');

When I do $query->execute() I get all related records and could count them. But in this I case I want to count all records instead of fetch and count it. So, is there a way to count it directly?

Thank you for any responses.



7.0k
Accepted
answer

Found a working solution:

private function getProjectCount()
{
    $query = $this->modelsManager->createBuilder()
        ->from(array('Project'))
        ->columns(array('count' => 'COUNT(*)'))
        ->leftJoin('ProjectUser', 'ProjectUser.project_id = Project.id')
        ->where('ProjectUser.user_id = :userId:', array('userId' => $this->getUser()->id))
        ->orderBy('Project.last_activity_at DESC')
        ->getQuery();

    $result = $query->execute();
    return $result[0]['count'];
}
edited Aug '15

Instead of $result[0] you can do ->getFirst after ->getQuery() also dont COUNT (*), better COUNT(id)



17.5k

$result = count($query->execute()); should also work.

Yea if he gets them earlier that should be fine too. But i guess he has some pagination and he just need to know how many records are in database.