So I’m tryna query all the rooms that do not have a reservation in the given check_in and check_out
,for doing so I’m using the above query :

When I test the logic of query on pgAdmin it works fine, but it not produces the same entity when I use it as a native query on my repository …

@Query(value = "SELECT * FROM "room" r " +
            "WHERE r."id" NOT IN (" +
            "SELECT rv."room_id" " +
            "FROM "reservation" rv " +
            "WHERE :checkInDate < rv."check_out" " +
            "AND :checkOutDate > rv."check_in") " +
            "AND r."kids" = :kids " +
            "AND r."adult" = :adults",
            nativeQuery = true)
    List<Room> findAvailableRooms(@Param("checkInDate") LocalDate checkInDate,
                                  @Param("checkOutDate") LocalDate checkOutDate,
                                  @Param("kids") int kids,
                                  @Param("adults") int adults);



package com.example.hotelManagmentSystem.dataproviders.entity;

import jakarta.persistence.*;
import lombok.*;

import java.time.LocalDate;
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
@Entity
@Table(name = "reservation")
public class Reservation {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", nullable = false)
    private Integer id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "hotel_id")
    private Hotel hotel;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "client_id")
    private User client;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "room_id")
    private Room room;

    @Column(name = "check_in")
    private LocalDate checkIn;

    @Column(name = "check_out")
    private LocalDate checkOut;

    @Column(name = "adults")
    private Integer adults;

    @Column(name = "kids")
    private Integer kids;

    @Column(name = "net_value")
    private Double netValue;

    @Column(name = "tax_rate")
    private Double taxRate;

    @Column(name = "discount")
    private Double discount;

    @Column(name = "created_at")
    private LocalDate createdAt;

    @Column(name = "total")
    private Double total;

}


package com.example.hotelManagmentSystem.dataproviders.entity;

import jakarta.persistence.*;
import lombok.*;

import java.time.LocalDate;
import java.util.LinkedHashSet;
import java.util.Set;

@Builder
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
@Entity
@Table(name = "room")
public class Room {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", nullable = false)
    private Integer id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "hotel_id")
    private Hotel hotel;

    @Column(name = "type")
    private String type;

    @Column(name = "adult")
    private Integer adult;

    @Column(name = "kids")
    private Integer kids;

    @Column(name = "description", length = Integer.MAX_VALUE)
    private String description;

    @OneToMany(mappedBy = "room")
    private Set<RoomPrice> roomPrices;

}

I tried to use a left join and get all rows where reservation is null and still reproduces the same result,
Tried to change the FetchType from lazy to eager on room inside reservation attribute …