Throws new ResourceNotFoundError(id) for non-existent id n

Question

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.

Answer 1

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;
}