We are moving our forum in GitHub Discussions. For questions about Phalcon v3/v4 you can visit here and for Phalcon v5 here.

Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

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.4k

$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.