Displaying one to many images using sql in mybatis

Question

I have tables

Tasks: id title Description Task_reply_mapping task_id parent_id 

I wrote the following query to get data

  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 = t1.id order by fe.created_at desc limit 0,10 

It seems to be working fine but is not populating the data correctly. I want to know if this query is correct?

In my mapper file I have

  <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" jdbcType="BIGINT" property="id" /> <result column="title" jdbcType="VARCHAR" property="title" /> <result column="description" jdbcType="VARCHAR" property="description" /> </collection> </resultMap> 

Or am I doing something wrong in the mapping class.

Entries in objects are placed, as in the first index, its last task, whether it is an answer or a new task, etc.

He should insert records like this

  Task1 --1st reply task --2nd reply task Task 2 --1st reply task --2nd reply task 

Please let me know if further information is required.

Answer 1

The second join should be with the table Task_reply_mapping :

 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 

Below is a demo .

Answer 2

I don’t know where the problem is.

I think that maybe the query and mapping is wrong.

Try the following:

 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 the request, you must id , two `title and two description`, so the problem may be there.