Each screening has an assigned room and the room has seats. I need to display the seats for the given screening with information if it is free. The way I do it now is that I get the id of the screening, I download the screening, I take the id of the hall from it, then all the seat id of the tickets for that screening and all the seats from that hall, and if the list of seat id of the tickets contains the id of the given seat, it means that the seat is occupied. In code, it looks like this:

public List<SeatWithStatusDto> handle(GetSeatsByScreeningId query) {
    log.info("Query:{}", query);
    var hallId = jdbcTemplate.queryForObject(
            "select s.hall_id from screenings s where s.id = ?",
    var ticketsSeatsId = getTicketsSeatIdsByScreeningHandler.handle(
            new GetTicketsSeatIdsByScreeningId(query.screeningId())
    return getSeatsByHallIdHandler
            .handle(new GetSeatsByHallId(hallId))
            .map(seatDto -> new SeatWithStatusDto(

How to do it better? Maybe introduce 2 different models for seats - HallSeat (storing information about what seats we have in a particular hall at all) and ScreeningSeat (with information about whether a seat is free or not)

  • \$\begingroup\$ We need to know more about the database and the code. \$\endgroup\$
    – SedJ601
    Dec 12, 2023 at 17:47

1 Answer 1


Without further stressing that how to do it better depends on the details at a glance there are three database queries ran to compute the result while same can be achieved with one query properly joining1 the tables storing the information of interest. It is also possible to use nested queries with in clause just mind the bound2 for the number of the values that could be specified by the clause.

1 outer, inner, left, right type of joins
2 in clause bounds are database provider specific


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.