John Davidson

Message:

I have 3 tables with following column names:

restaurants:

• id, name, cuisine, address, image_file

reviews:

• id, restaurant_id(foreign key), user_id(foreign key), header, content, posted_at, modified_at

users: -id, username, email, password, privileges

In my PHP project on the main page where i want to display all the restaurants with the latest review if there is any review. Can someone please help me how to do that within single query and single foreach loop? I've tried left join with subqueries but could not resolve the problem of duplicated rows. Thanks in advance!

---

My Answer:
You can achieve this by using a subquery to get the latest review for each restaurant and then joining the result with the restaurants table. Here's an example query that you can use:

php
SELECT r.id, r.name, r.cuisine, r.address, r.image_file,
rv.header, rv.content, rv.posted_at
FROM restaurants r
LEFT JOIN (
SELECT r.id AS restaurant_id,
re.header, re.content, re.posted_at
FROM reviews re
INNER JOIN (
SELECT restaurant_id, MAX(posted_at) AS latest_posted_at
FROM reviews
GROUP BY restaurant_id
) latest_review ON re.restaurant_id = latest_review.restaurant_id
AND re.posted_at = latest_review.latest_posted_at
) rv ON r.id = rv.restaurant_id


In this query, we first create a subquery to get the latest review for each restaurant by finding the maximum `posted_at` date for each `restaurant_id`. Then, we join this subquery with the restaurants table to get the desired result.

You can then loop through the result set in your PHP code using a foreach loop to display the restaurants and their latest reviews.