I'm building a small full stack system (typescript, express, NodeJs) and in one of the routes where the user can request a movie based on the selected theater, the specific services are as follows:
async function getMoviesByTheatreId(theatreId : number) : Promise<MovieModel[]> { // SQL: const sql = 'SELECT * FROM movies where theatreId = ?;' // Call dal: const movies = await dal.execute(sql ,[theatreId]); // Return: return movies; }
clarify There are two tables in the MYSQL database - Theater and Movies. They share a foreign key that references the "theatreId" column in the Theaters table. The foreign key is a foreign key in the movies table.
Now, there is a possibility that the user sends some theatreId that does not exist, in which case I want to throw a new ResourceNotFoundError. However, it's also possible that the theatreId does exist, but there just aren't any movies matching that theatre. In this case I don't want to throw that error. I also want it to perform well in terms of performance, using multiple queries to check the database will slow down the whole process.
P粉0717437322023-09-14 15:36:55
First, before querying the Movies table, check if a theater with the provided theatreId
exists in the Theaters table. Then you can query the movie.
Here is the sample code:
async function getMoviesByTheatreId(theatreId : number) : Promise<MovieModel[]> { const theatreSql = 'SELECT * FROM theatres WHERE theatreId = ?'; const theatre = await dal.execute(theatreSql, [theatreId]); if (theatre.length === 0) { // throw new ResourceNotFoundError('Theatre not found'); } // SQL to retrieve movies with provided theatreId: const moviesSql = 'SELECT * FROM movies WHERE theatreId = ?;' // Call dal: const movies = await dal.execute(moviesSql ,[theatreId]); // Return: return movies; }