Mybatis坑之collection单属性list

àì夳堔傛蜴生んèń 2023-05-21 07:08 27阅读 0赞

今天写了一个一对多的查询用到了collection，首先是这样的

这样的

<select id="findByPageForSupplier" resultMap="userParamMap">
        select
        u.id as u_id, u.company_id as companyId, u.name,u.sex, u.mobile, u.email, u.id_card as id_card, u.work_code as work_code, u.position, u.if_delete as if_delete, u.update_time as update_time,
        ur.supplier_user_id as supplier_user_id,ur.supplier_id as supplier_id,orgr.supplier_organization_id as orgrm_supplier_organization_id
        from user u
        left join user_relation ur on u.id = ur.user_id
        left join user_organization_relation uor on u.id = uor.user_id
        left join organization_relation orgr on uor.organization_id = orgr.organization_id
          where  orgr.supplier_id = #{supplierId}
        and u.company_id = #{companyId} and orgr.company_id=#{companyId}  and uor.company_id = #{companyId}
          <if test="name != '' and name != null">
            and u.name like '%#{name}%'
          </if>
          <if test="time != null">
            and u.update_time > #{time}
          </if>
        </select>

查询结果是这样的

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNDU0ODg0_size_16_color_FFFFFF_t_70][]

仔细看会发现，我的id和supplierOrganzationIdList里面的值一样，WTFK，我各种修改我的collection，各种查资料，感觉我的collection写的没毛病啊，但是结果始终是这样，最后机缘巧合将我的sql改了一下，把我的list查询条件调到了第一位，也就是这样

<select id="findByPageForSupplier" resultMap="userParamMap">
        select
        orgr.supplier_organization_id as orgrm_supplier_organization_id,
        u.id as u_id, u.company_id as companyId, u.name,u.sex, u.mobile, u.email, u.id_card as id_card, u.work_code as work_code, u.position, u.if_delete as if_delete, u.update_time as update_time,
        ur.supplier_user_id as supplier_user_id,ur.supplier_id as supplier_id
        from user u
        left join user_relation ur on u.id = ur.user_id
        left join user_organization_relation uor on u.id = uor.user_id
        left join organization_relation orgr on uor.organization_id = orgr.organization_id
          where  orgr.supplier_id = #{supplierId}
        and u.company_id = #{companyId} and orgr.company_id=#{companyId}  and uor.company_id = #{companyId}
          <if test="name != '' and name != null">
            and u.name like '%#{name}%'
          </if>
          <if test="time != null">
            and u.update_time > #{time}
          </if>
        </select>

惊喜的发现查询的结果对了，坑爹啊！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzNDU0ODg0_size_16_color_FFFFFF_t_70]: /images/20230521/07005def7ebc4fcb8704d798c4f37bf2.png