There are situations which require having really big query joining several tables together with sub select statements in them to produce the desired results.
My question is, should we consider using multiple smaller queries and bring the logical operations into the application layer by querying the DB in more than one calls or it's better to have them all in one go?
For example consider the following query:
SELECT * FROM `users` WHERE `user_id` IN (SELECT f2.`friend_user_id` FROM `friends` AS f1 INNER JOIN `friends` AS f2 ON f1.`friend_user_id` = f2.`user_id` WHERE f2.`is_page` = 0 AND f1.`user_id` = "%1$d" AND f2.`friend_user_id` != "%1$d" AND f2.`friend_user_id` NOT IN (SELECT `friend_user_id` FROM `friends` WHERE `user_id` = "%1$d")) AND `user_id` NOT IN (SELECT `user_id` FROM `friend_requests` WHERE `friend_user_id` = "%1$d") AND `user_image` IS NOT NULL ORDER BY RAND() LIMIT %2$d
What's the best way of doing it?