Display One To Many Mapping Using Sql In Mybatis

I have to tables Tasks: id title Description Task_reply_mapping task_id parent_id I have written the following query to get the data select t1.id,t1.title,t1.description,t

Solution 1:

The 2nd join has to be with the Task_reply_mapping table:

select t1.id,
       t1.title,
       t1.description,
       t2.id,
       t2.title,
       t2.description
  from tasks t1
       left join Task_reply_mapping trm
           on t1.id = trm.task_id 
       left join tasks t2
           on t2.id = trm.parent_id

Here is a demo.

Solution 2:

Not sure where the problem is.

I think maybe the query and the mapping are not correct.

Try this:

select 
    t1.id
    ,t1.title
    ,t1.description
    ,t2.id as id_2
    ,t2.title as title_2
    ,t2.description as description_2
from tasks t1
    left join Task_reply_mapping trm on t1.id =  trm.task_id 
    left join tasks t2 on t2.id = t1.id
order by fe.created_at desc limit 0,10

Mapping

<resultMap id="TaskResultMap" type="com.mycom.myproj.bean.TaskBean">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="title" jdbcType="VARCHAR" property="title" />
    <result column="description" jdbcType="VARCHAR" property="description" />
    <collection ofType="com.mycom.myproj.bean.TaskBean" property="replyTask">
    <id column="id_2" jdbcType="BIGINT" property="id" />
        <result column="title_2" jdbcType="VARCHAR" property="title" />
        <result column="description_2" jdbcType="VARCHAR" property="description" />
   </collection>
</resultMap>

In you query, you have to id, two ´titleand twodescription` so the problem could be there.