Paging problem in PageHelper plug in when implementing one to many query

One-to-many query scenarios are often used in projects, but PageHelper does not support this nested query enough. If it is a one-to-many list query, the paging results returned are wrong

Refer to the instructions on Github: https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/Important.md

For 1-to-many list queries, there are two ways to solve them

1. Deal with it in code. Modify the resultMap of the paged query separately, remove the collection tag, and then traverse the results in the code to query a subset

2. Use the method provided by mybatis, as follows

Define two resultMap, one for paging queries and one for other queries

<resultMap id="BaseMap" type="com.xx.oo.Activity">
  <id column="id" property="id" jdbcType="INTEGER"/>
    ....
</resultMap>

<resultMap id="ResultMap" type="com.xx.oo.Activity" extends="BaseMap">
  <collection property="templates" ofType="com.xx.oo.Template">
    <id column="pt_id" property="id" jdbcType="INTEGER"/>
    <result column="pt_title" property="title" jdbcType="VARCHAR"/>
  </collection>
</resultMap>

<resultMap id="RichResultMap" type="com.xx.oo.Activity" extends="BaseMap">
  <!--property : Corresponding JavaBean Fields in the -->
  <!--ofType : Corresponding JavaBean Type of -->
  <!--javaType The type of the corresponding return value -->
  <!--column : Corresponding database column Is not a field of JavaBean Fields in the -->
  <!--select Object corresponding to a subset of the query sql-->
  <collection property="templates" ofType="com.xx.oo.Template" javaType="java.util.List" column="id" select="queryTemplateById">
    <id column="pt_id" property="id" jdbcType="INTEGER"/>
    <result column="pt_title" property="title" jdbcType="VARCHAR"/>
  </collection>
</resultMap>

<resultMap id="template" type="com.xx.oo.Template">
  <id column="pt_id" property="id" jdbcType="INTEGER"/>
  <result column="pt_title" property="title" jdbcType="VARCHAR"/>
</resultMap>

For queries that require paging, use RichResultMap. First define an sql for a subset of queries

<!-- Here's #{id} The parameter is collection Object defined in the column Field -->
<select id="queryTemplateById" parameterType="java.lang.Integer" resultMap="template">
  select id pt_id, title pt_title
  from t_activity_template where is_delete=0 and activity_id = #{id}
  order by sort_number desc
</select>
<select id="queryByPage" parameterType="com.xx.oo.ActivityPageRequest" resultMap="RichResultMap">
  SELECT t.*,t1.real_name creator_name
  FROM t_activity t
  left join user t1 on t1.user_id = t.creator
  <where>
    t.is_delete = 0
    <if test="criteria != null and criteria.length()>0">AND (t.activity_name like concat("%",#{criteria},"%"))</if>
  </where>
  ORDER BY t.id desc
</select>

Common queries that do not require paging, using ResultMap

<select id="queryById" parameterType="java.lang.Integer" resultMap="ResultMap">
  SELECT t.*, t6.id pt_id, t1.title pt_title
  FROM t_activity t
  left join t_activity_template t1 on t.id=t6.activity_id and t1.is_delete=0
  WHERE t.is_delete = 0 AND t.id = #{id}
</select>