2

I am creating an API with express.js for a food app and I can't figure out what is the best (most efficient) way to query the data and send it to the client-side. Any help is much appreciated.

I have a table called Restaurants

id name img_url address
1 restaurant 1 link to img 1 address 1
2 restaurant 2 link to img 2 address 2

Another table called categories

id name
1 Pizza
2 Pasta

And a restaurant can be in one or more categories, so I have another table for the many-to-many relationship

restaurant_id category_id
1 1
1 2
2 1
2 2

Now on the home page of the app, I need to send a get request to the server and get in return all the restaurants and the categories so that I can display them all in one scroll view with the category name and below it all the restaurants that belong to it.

The first approach that got to my mind is to join all three tables

SELECT * 
FROM Restaurants r
INNER JOIN RestaurantCategory rc ON r.id=rc.restaurant_id
INNER JOIN Categories c ON c.id=rc.category_id;

which will give me a result similar to this

id name img_url address category
1 restaurant 1 link to img 1 address 1 Pizza
1 restaurant 1 link to img 1 address 1 Pasta
2 restaurant 2 link to img 2 address 2 Pizza
2 restaurant 2 link to img 2 address 2 Pasta

And then I would somehow either on the client-side or the server-side loop on the result and make a list for each category and put in it any restaurant that is in that category.

Instead of doing the lists myself I also thought about first selecting the categories and then doing a for loop on the server-side to get the restaurants for each category, but I am not sure if in this case having multiple select statements is better than one.

I didn't like this approach because of how the table returned is having all the information about the same restaurant repeated more than once for each category. If I have 100 restaurants (with more columns than in the example above) for example and each is in maybe 3 categories, I will get 300 records, which I think will be a big amount of data being sent from the server to the client and it is all repeated.

The second approach is to Select each table alone, then do the join on the client-side myself.

I know that I should let the database engines do the joins for me because they are more powerful but I was thinking maybe if the users have a bad internet connection or something it will be worse to have the amount of data doubled or tripled?

MohamedAlaaser
  • 21
  • 3
  • 2
    If the data can be processed on the DBMS side then it must be processed on the DBMS side. Exclusions are rare. *I will get 300 records* Do not select intermediate data, select final one. – Akina Apr 29 '22 at 09:10
  • @Akina so I should not care about the data size? also if I will take the first approach would you say it is better to loop on the categories and do a select statement for each? or just one select statement and do the lists myself on nodejs? – MohamedAlaaser Apr 29 '22 at 09:16

1 Answers1

-2

Use a GROUP BY clause:

SELECT * 
FROM Restaurants r
INNER JOIN RestaurantCategory rc ON r.id=rc.restaurant_id
INNER JOIN Categories c ON c.id=rc.category_id
GROUP BY c.id
Cedric Ipkiss
  • 5,662
  • 2
  • 43
  • 72