jpa多表分页查询排序

Question

jpa多表分页查询，加了order by 后 每次分页的数据不一致，求解
代码如下

    final PageRequest pageRequest = PageRequest.of(pageNum, pageSize);
    final Page<Object[]> page = albumImageEntityRepository
            .findByPageWithAlbumIdAndDeleted(false, albumId, pageRequest);

  @Query(nativeQuery = true,
          value = "select " +
                  "image.id, image.name, image.size," +
                  "image.location," +
                  "image.url, image.zip_url," +
                  "DATE_FORMAT(image.last_modify_date, '%Y-%m-%d %H:%i:%s')  as last_modify_date " +
                  "from album_image left join image " +
                  "on album_image.image_id = image.id " +
                  "where album_image.album_id = :albumId and album_image.deleted = :deleted " +
                  "order by image.last_modify_date desc ",
          countQuery = "select count(*) from album_image left join image on album_image.image_id = image.id where album_image.album_id = :albumId and album_image.deleted = :deleted")
  Page<Object[]> findByPageWithAlbumIdAndDeleted(@Param("deleted") boolean deleted, @Param("albumId") BigInteger albumId, Pageable pageable);

Answer 1

首先了解一下执行顺序，https://blog.csdn.net/u014044812/article/details/51004754
select在前，order by在后，你的sql日期转化为年月日时分秒字符串了，可能还有毫秒，毫秒原本不同但是这么一转换就相同了，导致乱序
解决办法：使用子查询将日期转化放在外层查询，里面查询使用原日期字段，排序放在内层子查询