Combine Queries / Results

Is it possible, using CDbCriteria, to combine two queries?

For example I want to return a total of 15 results, of these five results must be "type = 2", the remaining 10 results are "type = 1". If there are less than five type 2 results, then the type 1 results would need to increase.

The results would be used as a dataprovider for a CListView widget.

Any idea how I can go about this?

Sounds much like a job for UNION and CSqlDataProvider.

Any way to do this using CActiveDataProvider and CDbCriteria?

Will there be more than 5 of type=2?

Only if you are going through CActiveRecord.findBySql(). As far as I can see, neither CDbCriteria nor CActiveRecord are aware of the UNION statement.

I would rather want to consider making a db view.

What is it with all people wanting to use views lately? It’s not like it’s the newest, hippest thing in the database world, isn’t it? :huh:

Anyway, this is the type of query I’d imagine for the given problem:

(SELECT * FROM `table` WHERE `type`=2 LIMIT 5) UNION (SELECT * FROM `table` WHERE `type`=1) ORDER BY `type` DESC LIMIT 15;

I’m relatively new to the world of DB, so I don’t know the exact place for db view.

But what I like most is that you can simplify the PHP code that retrieves the records, using the simplest syntax of AR, when you have created a AR model for a db view.

Thanks. How would I put that in to a CDbCriteria object?

When it comes to MySQL, views are mostly a convenience thing. They are very limited, though. E.g. MySQL doesn’t know materialized views, which can become a scalability issue, IMHO.

If I’d know … CDbCommand has a union() method. Can you imagine building two CDbCriteria objects and joining them together? Otheriwse, CSqlDataProvider is the way to go.

On second thought, I would follow Da:Sourcerer’s suggestion to use CSqlDataProvider in this scenario, because a db view based AR model may not provide you with a proper result when you have some requirements to filter the result set.

When you want to filter both results of type 2 and type 1, and still want to show 5 records of type 2, then a db view based AR model will not work as expected.

Thanks guys. I am now going to try doing this using CSqlDataProvider.

My only concern is that the query I generate needs to be dynamic, so for example I want to be able to pass in GET parameters that can be used to extend the query, for example a "WHERE LIKE" clause or an IN clause. Preferably in a similar way to when doing it using CDbCriteria, e.g : $criteria->compare()

Is this going to be possible?

That’s going to be a problem. You can sort with CSqlDataProvider. But filtering is an ar feature.

So, here’s an idea: Perhaps you can let your regular ar model construct a CDbCriteria object via its search() method and extract the [font=“Courier New”]condition[/font] and [font=“Courier New”]params[/font] part, and finally recycle it for your CSqlDataProvider. It’s a bit hackish and assumes you don’t do anything fancy like searching in related fields, but it would be worth the try. I remember having done something similar in a complex CSV export.