How To Exclude Primary Key From Select?

[_Stan]

I have 2 tables bound via relations and need to execute an SQL statement on them, where COUNT is involved. Here is the original code:

    $criteria = new CDbCriteria();

    $criteria->select = 'COUNT(*) as count'; // count is defined in the model

    $criteria->compare('t.user_id', $user_id);

    

    $data = Price::model()->with(array

    (

      'votes' => array(

        'select' => false,

        'condition' => 'votes.datetime > NOW() - INTERVAL 1 WEEK AND votes.user_id = '.Yii::app()->user->id)

        )

    )->find($criteria);

This produces the following incorrect SQL statement:

SELECT COUNT(*) as count, t.id AS t0_c0 FROM price t LEFT OUTER JOIN votes votes ON (votes.price_id=t.id) WHERE (t.user_id=6) AND (votes.datetime > NOW() - INTERVAL 1 WEEK AND votes.user_id = 1)

The problematic part is highlighted in bold (indeed, it’s not possible to mix aggregate functions and plain fields). But, please, note that I do not request selection of the ‘id’ field. All I need is the COUNT(*).

How can one ensure that Yii will not include primary key into the statement?

Currenlty, the only workaround is to add ‘group’ => ‘votes.user_id’, but I don’t like this.

Is there a decent solution?

Thanks in advance.

[Tsunami (Erik)]

It is possible to mix plain fields and aggregate functions, but you probably do need a GROUP BY.

$criteria->group = 't.id';

[_Stan]

It is possible to mix plain fields and aggregate functions, but you probably do need a GROUP BY.

$criteria->group = 't.id';

I do NOT need grouping. With plain SQL, I can get “SELECT COUNT(*) FROM t” without GROUP BY. It is Yii’s peculiarity, that “id” is added into select regardless to my instruction to not include it. I’d like to know how to achieve the same SQL-statement from Yii.

BTW, it is NOT possible to mix this stuff. MySQL gives the error:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),…) with no GROUP columns is illegal if there is no GROUP BY clause

[Angel_De_La_Noche]

try this

[_Stan]

try this

Thanks, this works.

Still, I suppose the issue with automatically added primary key can break statements with other aggregate functions.

[Tsunami (Erik)]

BTW, it is NOT possible to mix this stuff. MySQL gives the error:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),…) with no GROUP columns is illegal if there is no GROUP BY clause

So it IS possible WHEN you use GROUP BY. Otherwise the database would just return garbage. It makes sense for Yii to always add the primary key for active records, so a simple GROUP BY on the primary key fixes the error for other aggregate functions.

[jhonatanbianchi]

So it IS possible WHEN you use GROUP BY. Otherwise the database would just return garbage. It makes sense for Yii to always add the primary key for active records, so a simple GROUP BY on the primary key fixes the error for other aggregate functions.

How can I exclude de primary key? I’m doing a SUM with a huge criteria and I can’t use STAT.

I want:

SELECT SUM(valor) AS _total FROM "financeiro"."compromissos_financeiros" "t" …

Yii generates:

SELECT SUM(valor) AS _total, "t"."entidade_id" FROM "financeiro"."compromissos_financeiros" "t"

I can’t group by using my primary key because it will not sum at all.