how to transfer parameter to sub query

Question

i just use userIdx of findAllregions's parameter to findFavoriteStore

this is my query

@Select("SELECT * FROM region")
@Results(value = {
        @Result(property = "regionIdx", column = "regionIdx"),
        @Result(property = "stores", javaType = Store.class, column = "storeIdx",
                many = @Many(select = "com.travely.travely.mapper.StoreMapper.findFavoriteStore", fetchType = FetchType.LAZY))
})
List<Region> findAllRegions(@Param("userIdx") final Long useridx);



@Select("SELECT s.* FROM store as s NATURAL JOIN favorite as f WHERE f.userIdx=#{userIdx} AND f.isFavorite = 1")
    List<Store> findFavoriteStore(@Param("userIdx") final long userIdx);

it works to select region of 'findAllRegions' but doesn't work to select store of 'findFavoriteStore'

score 0 · Accepted Answer · answered Jan 04 '19 at 09:52

The query does not work because the column attribute is configured incorrectly.

Here is relevant part of the column attribute documentation:

The column name from the database, or the aliased column label that holds the value that will be passed to the nested statement as an input parameter.

As you see only the column from the main query result can be used.

It means that you either need to include the artificial column to the query and use it in the @Result.column like this:

@Select("SELECT #{userIdx} userIdx, r.* FROM region r")
@Results(value = {
     @Result(property = "regionIdx", column = "regionIdx"),
     @Result(
        property = "stores", javaType = Store.class, column = "userIdx",
        many = @Many(
                 select = "com.travely.travely.mapper.StoreMapper.findFavoriteStore",
                 fetchType = FetchType.LAZY))
})
List<Region> findAllRegions(@Param("userIdx") final Long useridx);

Alternatively if java 8+ is used you can use default interface methods to fetch associations/collections like this:

interfact MyMapper {

  @Select("SELECT * FROM region")
  @Results(value = {
        @Result(property = "regionIdx", column = "regionIdx")
  })
  List<Region> findAllRegions();

  default List<Region> findAllRegions(Long userIdx) {
      List<Region> regions = findAllRegions();
      List<Strore> favoriteStores = findFavoriteStore(userIdx);
      for(Region region:regions) {
          region.setStores(favoriteStores);
      }
      return regions;
  }

  @Select("SELECT s.* FROM store as s NATURAL JOIN favorite as f WHERE f.userIdx=#{userIdx} AND f.isFavorite = 1")
  List<Store> findFavoriteStore(@Param("userIdx") final long userIdx);

}

Note that this does not use lazy fetching of the favourite stores. It seems that this is not needed as in the contexts that favorite stores are not needed the query without userIdx can (and should0 be used.

Thank you for ur answer! Add artificial column is works! – Bebop Jan 05 '19 at 15:01 — Bebop, Jan 05 '19 at 15:01

how to transfer parameter to sub query

1 Answers1

Linked