MySQL, Multiple select in the same table with the result together and order condition applied on all result
Today I needed made a multiple select, more than one SELECT command in the same table, but the result should comes together and I also needed apply one condition for ORDER in all result.
Firstly I tried read if CakePHP has supports for it, but CakePHP doesn’t has, so I pass to try a MySQL query solution for that, and that solution is the command UNION.
For example:
Table people
id
name
age
If for example we want show 30 person with age equal 20 and more 20 with the age equal 10.
With the union condition this is simple.
1 2 3 | (SELECT * FROM people WHERE age=20 LIMIT 30) UNION (SELECT * FROM people WHERE age=10 LIMIT 20); |
So how you can check, it’s pretty simple, make your two queries and apply the command UNION, if you want to apply one order or another thing in the result you should put it in the end
1 2 3 4 | (SELECT * FROM people WHERE age=20 LIMIT 30) UNION (SELECT * FROM people WHERE age=10 LIMIT 20) ORDER BY name ASC; |
Worth remembering, the fields that result will come should be equal, this in the case that you are not using the same table, but two tables distinct with different fields between these.
In the case of CakePHP framework, apply the query method of your model for example:
1 2 3 4 5 | $nodes = $this->Node->query(' (SELECT Node.* FROM nodes AS Node WHERE type=3 ORDER BY date DESC LIMIT 30) UNION (SELECT Node.* FROM nodes AS Node WHERE type!=3 ORDER BY date DESC LIMIT 200) ORDER BY date DESC;'); |
a better way to do it is with a behavior, query() is never a good way
this is what i have come up with which is basicaly support for UNION in a cakephp way
http://github.com/infinitas/infinitas/blob/dev/infinitas/feed/models/behaviors/feedable.php#L55
hi.
What is the reason for you tell is never a good way? is it based on what?
I understand there are always others ways and I agree when query is not the better way, but never is not one word that I’d like use.
In my case for example, make an iterator with array to do that is not a good option because the database already do that for me, so I prefer use the query…
But otherwise thanks for the comment, and I’ll check that resource that you sent.
never say never, but i would try to avoid the query() way, too.
i didn’t had a case to try a UNION query, but i will try it and look for the performance myself.
I totally agree with you Alex, avoid query but in some cases if necessary use that, yeah try you by yourself and if possible give a feedback thx.
Use a stored procedure. Is faster and cleaner.
I don’t know if CakePHP supports union and yes I would always say use built-in functionality if its available and suits your needs.
On the other hand I wouldn’t say you should do 2-3 hours of work to avoid using $model->query() either. Just use it in a smart way. Always make a single point of entry for that kind of query, whether it be a custom model method or a custom find type etc. Remember also, that if you write a bad query you can’t blame it on Cake :p
yeah, I agree with it, try always avoid the use of query but sometimes that is not possible (not the case of post) and so I should say go ahead and use query, but always try avoid it!