The title of the post is pretty streight forward. Here is a problem i face every day and i believe every developer, that has something to do with databases, does. When you need a result set that has to be created out of sever or eight tables what do you do? Do you make a super long query that joins all those tables and bring back the result set or do you load chunks of it into the RAM and join in in your script?Well, the obvious answer is that you make that superlong query. I mean it’s absolutely logical to say that a man’s gotta do what a man’s gotta do. If it’s seven tables then let it be. But what if you have to get the max of one column and average of another? You cannot do that in one query can you? In that case you will probably have to go for the second solution.
Here is what i got from my very short experience. The best way to do things is to query the database and bring one result set with all your data in it. In case you have some tough result set to bring, like a max with a sum all mixed together, well in that case go with the loading.
One more small thingy i do. If the query get’s too complicated i prefer to bring my data to RAM and join them php side, especialy if they are small, memory-wise.
Here is an example of what i mean. Let’s say you have a product, where you want to sum the amount and also find the max date on an order and join them all together. It is obvious that you cannot issue a query that will sum and max two different columns. So you obviously have to do it in two queries. Instead of creating a view i would suggest issuing the two queries, getting the two result sets, itterate through them and join the lines creating the result set you want.
Anyways, whatever suits you in each case, but consider both ways when things get tough.