How to avoid N+1 problem with native SQL query in springboot with Hibernate?

Question

I'm querying my DB with POSTGIS built-ins to retrieve the closest Machines given a Location. I have to use a native SQL because Hibernate does not support POSTGIS and CTEs:

    @Repository
    public interface MachineRepository extends JpaRepository<Machine, Long>{
    @Query(value =
                "with nearest_machines as\n" +
                "         (\n" +
                "             select distance_between_days(:id_day, machine_availability.id_day) as distance_in_time,\n" +
                "                    ST_Distance(geom\\:\\:geography, ST_SetSrid(ST_MakePoint(:longitude, :latitude), 4326)\\:\\:geography) as distance_in_meters,\n" +
                "                    min(id_day) over (partition by machine.id) as closest_timeslot_per_machine,\n" +
                "                    machine_availability.id_day,\n" +
                "                    machine.*\n" +
                "             from machine\n" +
                "                      join machine_availability on machine.id = machine_availability.id_machine\n" +
                "             where machine_availability.available = true\n" +
                "               and machine_availability.id_day >= :today\n" +
                "               and ST_DWithin(geom\\:\\:geography, ST_SetSrid(ST_MakePoint(:longitude, :latitude), 4326)\\:\\:geography, 1000)\n" +
                "         )\n" +
                "select nearest_machines.*\n" +
                    "from nearest_machines\n" +
                    "where id_day = closest_timeslot_per_machine\n" +
                    "order by distance_in_time, distance_in_meters\n" +
                    "limit 20;",
            nativeQuery = true)
        List<Machine> findMachinesAccordingToAvailabilities(@Param("longitude") BigDecimal longitude,
                                                            @Param("latitude") BigDecimal latitude,
                                                            @Param("id_day") String idDay,
                                                            @Param("today") String today);
}

Of course, Machine and MachineAvailability are @Entity's. And they are @OneToMany(fetch = FetchType.EAGER) related. I changed the default LAZY to EAGER cause i need the MachineAvailability in the final JSON.

The problem is that it triggers 2 more requests by resulting machine(ie the famous N+1 problem).

1.How can i solve that in only ONE request?

2.Is it possible to create my on JSON somehow and returning it directly in the MachineController?

Answer 1

Solving this in 1 request is tough as you will have to use Hibernate native APIs to map the table aliases for the availability collection. You would need to add a join for the availabilities in the main query and do something like this: session.createNativeQuery("...").addEntity("m", Machine.class).addFetch("av", "m", "availabilities")

Another alternative would be to use Blaze-Persistence Entity Views since Blaze-Persistence comes with support for CTEs and many more goodies that PostgreSQL provides, this might be an interesting solution for you.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

I don't know your model, but a possible DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(Machine.class)
@With(NearestMachineCteProvider.class)
@EntityViewRoot(name = "nearest", entity = NearestMachine.class, condition = "machineId = VIEW(id)", joinType = JoinType.INNER)
public interface MachineDto {
    @IdMapping
    Integer getId();
    String getName();
    @Mapping("nearest.distanceInTime")
    Integer getDistanceInTime();
    @Mapping("nearest.distanceInMeters")
    Double getDistanceInMeters();
    Set<MachineAvailabilityDto> getAvailabilities();

    @EntityView(MachineAvailability.class)
    interface MachineAvailabilityDto {
        @IdMapping
        Integer getId();
        String getName();
    }

    class NearestMachineCteProvider implements CTEProvider {
        @Override
        public void applyCtes(CTEBuilder<?> builder, Map<String, Object> optionalParameters) {
            builder.with(NearestMachine.class)
                .from(Machine.class, "m")
                .bind("distanceInTime").select("CAST_INTEGER(FUNCTION('distance_between_days', :id_day, m.availabilities.idDay))")
                .bind("distanceInMeters").select("CAST_DOUBLE(FUNCTION('ST_Distance', m.geom, FUNCTION('ST_SetSrid', FUNCTION('ST_MakePoint', :longitude, :latitude), 4326)))")
                .bind("closestTimeslotId").select("min(m.availabilities.idDay) over (partition by m.id)")
                .bind("machineId").select("m.id")
                .bind("machineAvailabilityDay").select("m.availabilities.idDay")
                .where("m.availabilities.available").eqLiteral(true)
                .where("m.availabilities.idDay").geExpression(":today")
                .where("FUNCTION('ST_DWithin', m.geom, FUNCTION('ST_SetSrid', FUNCTION('ST_MakePoint', :longitude, :latitude), 4326), 1000)").eqLiteral(true)
                .end();
        }
    }
}

@CTE
@Entity
public class NearestMachine {
    private Integer distanceInTime;
    private Double distanceInMeters;
    private Integer closestTimeslotId;
    private Integer machineId;
    private Integer machineAvailabilityDay;
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

MachineDto a = entityViewManager.find(entityManager, MachineDto.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<MachineDto> findAll(Pageable pageable);

You can then sort by using Sort.asc("distanceInTime") and Sort.asc("distanceInMeters")

The best part is, it will only fetch the state that is actually necessary!