MyBatis Collection recursion needs to set recursion conditions

My table data is fixed at three levels, but there are many data at the third level, which leads to slow recursion. In theory, I only need two levels of recursion

MyBatis version

3.2.8

Database vendor and version

mysql 5.7

Test case or example project

  <resultMap id="BaseResultMapParam" type="amy.gs.sd.vo.bmp.SdCategoryBizVO" >
    <id column="pk_sdcategory" property="pk_sdcategory" jdbcType="CHAR" />
  </resultMap>

  <resultMap id="selectTreeResultMap" type="amy.gs.sd.vo.bmp.SdCategoryBizVO"  extends="amy.gs.sd.dao.ICategoryDAO.BaseResultMapParam">
    <association property="categoryVO" javaType="amy.gs.sd.vo.CategoryVO" resultMap="amy.gs.sd.dao.ICategoryDAO.BaseResultMap" />
    <collection property="children" javaType="java.util.ArrayList" column="{fw_parent=pk_sdcategory}" select="selectTreeList"/>
  </resultMap>

  <select id="selectTreeList" parameterType="java.util.Map" resultMap="selectTreeResultMap">
    select <include refid="TbColumns" />
    from gs_sd_category
    <where>
      ifnull(dr,0) = 0
      <if test="fw_parent != null and fw_parent != ''"> AND "fw_parent" = #{fw_parent} </if>
    </where>

  </select>

Steps to reproduce

Stop from level 2

Expected result

Stop from level 2

Actual result

Comment From: harawata

Hello @liukefu2050 ,

I'm sorry about the late reply. You just need to use a different select statement for the nested select. Something like this.

<resultMap id="BaseResultMapParam" type="amy.gs.sd.vo.bmp.SdCategoryBizVO" >
  <id column="pk_sdcategory" property="pk_sdcategory" jdbcType="CHAR" />
</resultMap>

<resultMap id="selectTreeResultMapWithCategory"
    type="amy.gs.sd.vo.bmp.SdCategoryBizVO"
    extends="amy.gs.sd.dao.ICategoryDAO.BaseResultMapParam">
  <association property="categoryVO" javaType="amy.gs.sd.vo.CategoryVO"
    resultMap="amy.gs.sd.dao.ICategoryDAO.BaseResultMap" />
</resultMap>

<resultMap id="selectTreeResultMapWithCategoryAndChildren"
    type="amy.gs.sd.vo.bmp.SdCategoryBizVO"
    extends="selectTreeResultMapWithCategory">
  <collection property="children" javaType="java.util.ArrayList"
    column="{fw_parent=pk_sdcategory}" select="selectTreeList"/>
</resultMap>

<sql id="selectTreeListSql">
  select <include refid="TbColumns" />
  from gs_sd_category
  <where>
    ifnull(dr,0) = 0
    <if test="fw_parent != null and fw_parent != ''"> AND "fw_parent" = #{fw_parent} </if>
  </where>
</sql>

<select id="selectTreeListWithChildren" resultMap="selectTreeResultMap">
  <include refid="selectTreeListSql" />
</select>

<select id="selectTreeList" resultMap="selectTreeResultMapWithCategory">
  <include refid="selectTreeListSql" />
</select>

We have no plan to add a new option to set recursion level at this point.