Multi column aggregation

Question

Multi column aggregation

blm14 Oct '14

Created Oct '14	Last Reply Oct '14	Replies 2	Views 522	Votes 0

blm14 40.7k

Oct '14

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?

Phalcon
98.9k

Accepted
answer

edited Oct '14
Oct '14

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

Phalcon · Answer 1 · 2014-10-07T08:46:10-07:00

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();

blm14 · Answer 2 · 2014-10-07T09:30:05-07:00

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?

blm14 · Answer 3 · 2014-10-07T09:31:39-07:00

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