model->with() uses HAVING clause. How to move it to WHERE clause?

Archives Yii 1.0.x Discussions
1

Hi All!

I'm having tables structure A (1…many) -> B (1…many) -> C.

I have following model relations:

Model A:

'bs' => array(self::HAS_MANY, 'B', 'id_a'),

Model B:

'cs' => array(self::HAS_MANY, 'C', 'id_b'),


'statRel0' => array(self::STAT, 'C', 'id_b', 'condition' => 'field=0'),

In my code I have the following:

$criteria = new CDbCriteria;


$criteria->order = "id_a";


$criteria->condition = 'some_field=100';


$AList = A::model()


    ->with('bs', 


              'bs.statRel0')


    ->findAll($criteria);

This produces for me 3 queries:

SELECT ... 


FROM `table_a` 


WHERE (some_field=100) 


ORDER BY id_a





SELECT ... 


FROM `table_a` 


    LEFT OUTER JOIN `table_b` t1 ON (t1.`id_a`=`table_a`.`id_a`) 


WHERE (`table_a`.`id_a`=200)





SELECT `id_b` AS `c`, COUNT(*) AS `s` 


FROM `table_c` 


WHERE (field=0) 


GROUP BY `id_b` 


HAVING `table_c`.`id_b` IN (301, 302, 303)

The problem is in last query's HAVING statement. Is it does not use index on id_b field this is significantly slows down the query. Why Yii uses HAVING in this query and is there any workaround to move this into WHERE clause?

Thanks for help.

2

Use this form to query: …with(…)->together()->findAll(…)

3

Thanks for your reply.

Using together() leads me to CDbException:

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id_a' in order clause is ambiguous

4

Try to disambiguate that then.

5

Well, using together() and resolving order clause(sorry for that ::)) still makes 2 queries:

SELECT ... 


FROM `table_a`  


    LEFT OUTER JOIN `table_b` t1 ON (t1.`id_a`=`table_a`.`id_a`) 


WHERE (some_field=100)


ORDER BY table_a.id_a





SELECT `id_b` AS `c`, COUNT(*) AS `s`


FROM `table_c`


WHERE (field=0)


GROUP BY `id_b`


HAVING `table_c`.`id_b` IN (301, 302, 303)

so there is still problem with that HAVING :(

6

My bad, I didn't read your code carefully.

The STAT relation always needs a separate query. I don't think you can merge HAVING with WHERE because id_b appears in SELECT and there is an aggregation involved. (it may work on MySQL, but not on PostgreSQL)

7

Ok, thanks for your help. Will manually create query for that purpose.