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

Multi column aggregation

I have a model, let's call it MyModel. It has four fields:

id (autonumber) Name Grade Person Date

In sql I want to do something the equivalent of:

select name, count(id), count(distinct person)
from
MyModel
where grade>=3

Is there any way do to this purely using Model and not PHQL?



98.9k

What do you mean by model? PHQL is also part of Model/ORM.

You can use the query builder if you don't want to use PHQL.

$robots = $this->modelsManager->createBuilder()
    ->columns('name', 'count(id)', 'count(distinct person)')
    ->from('MyModel')
    ->where('grade>=3') 
    ->getQuery()
    ->execute();


40.7k

OK I am trying to do this:

    $query = $this->modelsManager->createQuery("select ae_name, ae_grade, count(IF (fk_site_enrolling=84,id)) as num_events, count(distinct patient_study_id) as pts from dsmc\Models\Events where ae_grade >= 3 and pk_study = ".$id." group by ae_name, ae_grade ");

And it appears to not like the "if" statement. Can I not embed ifs inside count?



40.7k

This is the error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) AS num_events, count(DISTINCT events.patient_study_id) AS pts FROM `e' at line 1



98.9k
Accepted
answer
edited Oct '14

IF (fk_site_enrolling=84,id) requires 3 parameters, it has 2