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