Best practices of implementing pagination for one-to-many results in SQL

By | June 24, 2020
0 Comment

Introduction

One-to-many results paging is a prevalent scenario, for example, we want to query the product and product picture information. But many people will encounter the misunderstanding of paging here and get incorrect results. Come to analyze and solve this problem today.

Problem analysis

Let’s first create a simple commodity table and the corresponding commodity picture relationship table. There is a one-to-many relationship between them:

Then I wrote some products and the pictures corresponding to these products respectively. The left join query below shows that there is an apparent one-to-many relationship between them.

SELECT P.PRODUCT_ID, P.PROD_NAME, PI.IMAGE_URL
FROM PRODUCT_INFO P
         LEFT JOIN PRODUCT_IMAGE PI
                   ON P.PRODUCT_ID = PI.PRODUCT_ID

Typically, our paging statement will be written like this:

    <resultMap id="ProductDTO" type="it.entity.ProductDTO">
        <id property="productId" column="product_id"/>
        <result property="prodName" column="prod_name"/>
        <collection property="imageUrls"  ofType="string">
            <result column="image_url"/>
        </collection>
    </resultMap>

    <select id="page" resultMap="ProductDTO">
        SELECT P.PRODUCT_ID, P.PROD_NAME,PI.IMAGE_URL
        FROM PRODUCT_INFO P
                 LEFT JOIN PRODUCT_IMAGE PI
                           ON P.PRODUCT_ID = PI.PRODUCT_ID
        LIMIT #{current},#{size}
    </select>

When I passed in (0, 2) as expected and wanted to get the first two products’ data, the result was not what I expected:

2020-04-21 23:35:54.515 DEBUG 10980 --- [main] c.f.m.mappers.ProductInfoMapper.page     : ==>  Preparing: SELECT P.PRODUCT_ID, P.PROD_NAME,PI.IMAGE_URL FROM PRODUCT_INFO P LEFT JOIN PRODUCT_IMAGE PI ON P.PRODUCT_ID = PI.PRODUCT_ID limit ?,? 
2020-04-21 23:35:54.541 DEBUG 10980 --- [main] c.f.m.mappers.ProductInfoMapper.page     : ==> Parameters: 0(Long), 2(Long)
2020-04-21 23:35:54.565 DEBUG 10980 --- [main] c.f.m.mappers.ProductInfoMapper.page     : <==      Total: 2
page = [ProductDTO{productId=1, prodName='Cups', imageUrls=[https://google.com/cup1.png, https://google.com/cup2.png]}]

The two data I expected were cups and notebooks, but the result was only one. It turns out that when one-to-many mapping, the result set will be output according to the more side (expect four pieces of data. there will be seven pieces), and the first two will only show the cup (as shown above), after the merge. There is only one result, so the pagination is not right. So how can we achieve the desired paging effect?

Right way

The right way is that the main table should be paginated first and then query from the associated table.

Regardless of the framework, our SQL should first perform a paging query on the product table and then query the left associated picture table:

SELECT P.PRODUCT_ID, P.PROD_NAME, PI.IMAGE_URL
FROM (SELECT PRODUCT_ID, PROD_NAME
      FROM PRODUCT_INFO
      LIMIT #{current},#{size}) P
         LEFT JOIN PRODUCT_IMAGE PI
                   ON P.PRODUCT_ID = PI.PRODUCT_ID

The advantage of this way of writing is that it is universal. But MyBatis provides a relatively elegant style, and the ideas are still the ones mentioned at the beginning. Only we need to modify the above Mybatis XML configuration:

<resultMap id="ProductDTO" type="it.entity.ProductDTO">
    <id property="productId" column="product_id"/>
    <result property="prodName" column="prod_name"/>
    <collection property="imageUrls" ofType="string" select="selectImagesByProductId" column="product_id"/>
</resultMap>
<select id="page" resultMap="ProductDTO">
    SELECT PRODUCT_ID, PROD_NAME
    FROM PRODUCT_INFO
    LIMIT #{current},#{size}
</select>
<select id="selectImagesByProductId" resultType="string">
    SELECT IMAGE_URL
    FROM PRODUCT_IMAGE
    WHERE PRODUCT_ID = #{productId}
</select>

Conclusion

In most cases, pagination is easy, but there are still some small traps for one-to-many scenarios. Once we understand the mechanism, it is not difficult to solve 🙂

Related Posts