I am currently working on an API using express and postgreSQL to showcase restaurant ratings, reviews, and cuisine in specific areas. I have successfully created routes and controllers that display SQL tables in Postman.
My challenge arises when attempting to extract data from joined tables in SQL. While I understand how to specify the desired data using JOIN in SQL, I struggle with transferring data from the joined table to generate a response for the API. The tables involved include:
The first two tables are the "areas" table and the "restaurants" table, both sharing an "area_id" which is utilized in the third table - the joined table. What I specifically need access to is the name of the city in the joined table (located bottom right)...
Below is my express controller handling the request to retrieve all restaurants within a particular area_id:
const getRestaurantsByAreaId = (req, res, next) => {
db.many(
"SELECT * FROM restaurants JOIN areas ON areas.area_id = restaurants.area_id WHERE areas.area_id = $<area_id>",
{
area_id: req.params.area_id
}
)
.then(restaurants => {
res.status(200).send({
area_id: req.params.area_id,
name: '',
total_restaurants: restaurants.length,
restaurants
});
})
.catch(err => console.log(err));
};
Router endpoint:
areasRouter.get('/:area_id/restaurants', getRestaurantsByAreaId)
Although the query to the SQL database to create the joined table has been successful, I struggle with setting the 'name' value in the response object (after .then) to 'Manchester City Centre' from the joined table... I have attempted multiple approaches without success.
When making a request in Postman to view restaurants in area_id '1': http://localhost:9090/api/areas/1/restaurants
Output received client-side from the provided code..
Everything seems to be functioning correctly except for fetching the city name from the joined table.
Your help would be greatly appreciated as I navigate through this learning process.