I need opinions about how to create this sql query for best performance with query builder not eloquent.
I have two tables,
Images | |->id |->id_user |->name
Users | |->id |->name |->images (BOOLEAN) (if have images)
I need show a list of users with one image (first image), if users.images is TRUE and a default image if users.images is FALSE.
Regards and thanks!
First, some notes on improving your schema. Users.images is redundant information you can find out from joining with Images.id_user. I would suggest removing it. It will only fall out of sync or require a trigger which will slow down inserts and deletes.
Which image is the "first" image for a user? There's no sorting information on the images. I'll just pick the smallest ID, but you should figure that out.
Now onto the query. Use a LEFT OUTER JOIN to get all the elements in Users (the left side of the join with Images) even if they have no image.
Use a WHERE clause with a sub select to get just one image for each user, making sure to account for users with no pictures.
To provide the default, COALESCE will return the first non-NULL argument.
SELECT Users.name, COALESCE(Images.id, $DEFAULT_IMAGE_ID)
FROM Users
LEFT OUTER JOIN Images
Users.id = Images.id_user
WHERE Images.id is NULL
OR Images.id = (
SELECT MIN(ID) FROM Images WHERE Users.id = Images.id_user
)
Make sure Images.id_user is indexed or performance will not be good.
I cribbed heavily from this answer. You could also use GROUP BY as in this answer.
Alternatively you can leave the image NULL and let your application fill in the default. The application layer may be the better place to make that decision about what the default user image should be.
