SQL - JOIN newest 1 value in a one-to-many relationship

Question

I have the following query which displays information on "cooks", and the food items they sell for a food service app. It displays each seller, some personal info, and the food items they sell on the platform.

This is a postgresql database.

The current query references and pulls together data from 7 different tables. I've included a diagram below of each of the tables, and their link relationships to one another. I agree, this query should be split into separate more relevant reports, but this is what the client is asking for.

This query is almost complete, the final requirement being to somehow include a column to show the most recent order time for each food item (orders.time_placed).

One main problem comes to mind:

Would this even be possible? Considering that the 'orders' table has no column to include the food item that was purchased. The 'orders' table merely links back to the 'store' table... which leads me to believe the best I can do is pull the time of the last sale for each store (not down to the item level).

When searching around, I've uncovered a few very similar situations to mine- this one in particular: link

Though I have had no success in implementing the solution. Am I making this too complicated?

Here is my current query:

select 
account.id as "Account ID",
account.firstname as "Seller First Name", 
account.lastname as "Seller Last Name",
account.email as "Seller Email",
account.phone as "Seller Phone",
address.address as "Seller Address (Street)",
address.address_2 as "Seller Address 2",
account.zip_code as "Seller Zip",
address.neighborhood as "Seller Neighborhood",
menu.name as "Name of active menu",
kitchen_item.name as "Dishes", 
kitchen_item.price as "Price",
kitchen_item.daily_max_orders as "Quantity",
menu.pickup_start_time as "Start time", 
menu.pickup_end_time as "End time",
menu.repeat_mon as "Monday",
menu.repeat_tues as "Tuesday",
menu.repeat_wed as "Wednesday",
menu.repeat_thurs as "Thursday",
menu.repeat_fri as "Friday",
menu.repeat_sat as "Saturday", 
menu.repeat_sun as "Sunday"
from account
left join store on account.id = store.account_id
left join menu on store.id = menu.store_id
left join menu_item on menu.id = menu_item.menu_id
left join kitchen_item on (menu_item.kitchen_item_id = kitchen_item.id and store.id = kitchen_item.store_id)
join store_address on store.id = store_address.store_id
join address on store_address.address_id = address.id
group by account.id, account.firstname, account.lastname, account.email, account.phone, address.address, address.address_2, account.zip_code, address.neighborhood, menu.name, kitchen_item.name, kitchen_item.price, kitchen_item.daily_max_orders, menu.pickup_start_time, menu.pickup_end_time, menu.repeat_mon, menu.repeat_tues, menu.repeat_wed, menu.repeat_thurs, menu.repeat_fri, menu.repeat_sat, menu.repeat_sun
order by account.id asc;

And here is the solution I tried to borrow from the link above ... surprise surprise, it doesn't work :) (inserted right below the last JOIN):

inner join (
    SELECT orders.store_id,
    MAX(orders.placed) maxdate
    from orders
    group by orders.store_id
    ) maxdates on account.id = maxdates.buyer_account_id INNER JOIN
    orders o on maxdates.store_id = store.id
    and maxdates.maxdate = o.placed

Is there a simpler way to do this seemingly simple task? Thank you

EDIT - I apologize, I realize this is a little bit too much for me to provide sample data and expected output aside from screenshots. There's a simplified example in the linked question, but I have no idea how to apply the logic to my query here.

Answer 1

As you suspect, you cannot query for data you don't have. Since your schema doesn't include any information about when certain items were ordered, you cannot retrieve that information.

For getting the latest sale in the store, you can use postgresql's DISTINCT ON (see docs).

select distinct on (account.id, kitchen_item.name)
account.id as "Account ID",
account.firstname as "Seller First Name", 
account.lastname as "Seller Last Name",
account.email as "Seller Email",
account.phone as "Seller Phone",
address.address as "Seller Address (Street)",
address.address_2 as "Seller Address 2",
account.zip_code as "Seller Zip",
address.neighborhood as "Seller Neighborhood",
menu.name as "Name of active menu",
kitchen_item.name as "Dishes", 
kitchen_item.price as "Price",
kitchen_item.daily_max_orders as "Quantity",
menu.pickup_start_time as "Start time", 
menu.pickup_end_time as "End time",
menu.repeat_mon as "Monday",
menu.repeat_tues as "Tuesday",
menu.repeat_wed as "Wednesday",
menu.repeat_thurs as "Thursday",
menu.repeat_fri as "Friday",
menu.repeat_sat as "Saturday", 
menu.repeat_sun as "Sunday",
orders.time_placed as "Last Store Sale"
from account
left join store on account.id = store.account_id
left join menu on store.id = menu.store_id
left join menu_item on menu.id = menu_item.menu_id
left join kitchen_item on (menu_item.kitchen_item_id = kitchen_item.id and store.id = kitchen_item.store_id)
left join orders on (orders.store_id = store.id)
join store_address on store.id = store_address.store_id
join address on store_address.address_id = address.id
order by account.id asc, kitchen_item.name asc, orders.time_placed desc;