实际开发中遇到的关联关系查询

关联关系查询

需求：要求column表关联channel；channel位于label子项中，需要根据特定条件查询。

需要的数据结构如下：

1、分页；

2、返回的记录为Column列表；

3、Column列表中包含channel列表(图中为channelLabel字段)

4、channel列表是从label子表根据条件查出

后台

最开始的逻辑存java逻辑：

ColumnServiceImpl.java实现类

// 1、根据查询条件分页查询column表
Page<TMstColumn> columnPage = columnMapper.selectPage(page, Wrappers.<TMstColumn>lambdaQuery()
	.like(StringUtils.isNoneBlank(columnChannelDTO.getName()),TMstColumn::getName, columnChannelDTO.getName())
	.eq(StringUtils.isNoneBlank(columnChannelDTO.getLanguage()),TMstColumn::getLanguage, columnChannelDTO.getLanguage()));

// 2、将分页数据拷贝到返回对象
// 创建一个page用于返回
Page<ColumnChannelDTO> finalColumnChannelDTOIPage = new Page<>();
List<ColumnChannelDTO> records = new ArrayList<>();
// 将column的分页数据拷贝到返回对象
BeanUtils.copyProperties(columnPage, finalColumnChannelDTOIPage);
// 清空records
finalColumnChannelDTOIPage.setRecords(null);
// 遍历column的records记录
columnPage.getRecords().forEach(action -> {
    TMstColumn column = (TMstColumn)action;
    // 将column表的records拷贝到返回数据
    ColumnChannelDTO temp = new ColumnChannelDTO();
    BeanUtils.copyProperties(column, temp);
    records.add(temp);
});
finalColumnChannelDTOIPage.setRecords(records);

// 3、中间表：根据column表的id去中间表进行匹配拿到channelId
finalColumnChannelDTOIPage.getRecords().forEach(record -> {
    // 根据column表的id字段匹配中间表的记录
    List<TColumnChannel> tColumnChannelList = tColumnChannelMapper.selectList(Wrappers.<TColumnChannel>lambdaQuery().eq(TColumnChannel::getColumnId, record.getId()));
    // channel用于绑定id，labels用于显示labelName（这里可以优化，但目前还不知道怎么做）
    List<String> channel = new ArrayList<>();
    List<TMstLabelL> labels = new ArrayList<>();
    tColumnChannelList.forEach(x -> {
        // 4、channel表：根据channelId去标签表找到对应标签
        channel.add(x.getChannelId());
        TMstLabelL label = sysLabelItemMapper.selectById(x.getChannelId());
        if (label != null) {
            labels.add(label);
        }
    });
    record.setChannel(channel);
    record.setChannelLabel(labels);
});

return finalColumnChannelDTOIPage;

很明显，这个存java编写的查询效率实在太低

后来换成sql

ColumnMapper接口mapper类

	/**
     * 分页查询专栏
     * @param page 分页
     * @param columnChannelDTO 语言/专栏名
     * @return 专栏+字典项 分页数据
     */
	IPage<ColumnChannelDTO> getColumnChannel(Page page, @Param("query")ColumnChannelDTO columnChannelDTO);

三张表：t_mst_column专栏表、t_mst_label_l标签子项表、t_column_channel专栏频道中间表

查询条件：

1、根据专栏id查标签项信息，先从标签子表(label_l)中找到channel。将其作为子列表（channel列表）【例子中：listlabelNameById】

2、根据查询条件找到column信息【例子中：getColumnChannel】

3、resultMap中包含一个collection，将查询到的channel列表放到这里（注意查询字段不要忘记给）【例子中：baseResultMap】

4、在Mapper接口的参数中直接使用Page就行，不必再XML中使用能自动分页

ColumnMapper.xml XML文件

    <select id="getColumnChannel" resultMap="baseResultMap">
        SELECT
        t.id as column_id,
        t.name,
        t.language,
        t.sort,
        t.description,
        t.icon,
        t.theme,
        t.tenant_id,
        t.create_time,
        t.update_time,
        t.del_flag
        FROM
        t_mst_column t
        <where>
            t.del_flag = 0
            <if test="query.name != null and query.name != ''">
                AND t.name LIKE CONCAT('%', #{query.name}, '%')
            </if>
            <if test="query.language != null and query.language != ''">
                AND t.language LIKE CONCAT('%', #{query.language}, '%')
            </if>
        </where>
        ORDER BY t.language, t.sort
    </select>

    <resultMap id="baseResultMap" type="com.sgti.manage.feign.dto.ColumnChannelDTO">
        <id column="column_id" property="id" />
        <result column="name" property="name" />
        <result column="language" property="language" />
        <result column="sort" property="sort" />
        <result column="description" property="description" />
        <result column="icon" property="icon" />
        <result column="theme" property="theme" />
        <result column="tenant_id" property="tenantId" />
        <result column="create_time" property="createTime" />
        <result column="update_time" property="updateTime" />
        <result column="del_flag" property="delFlag" />
        <collection property="channelLabel" ofType="com.sgti.manage.feign.entity.TMstLabelL"
                    select="com.sgti.manage.api.mapper.ColumnMapper.listlabelNameById"
                    column="column_id" />
    </resultMap>

    <!--  根据专栏id查标签项信息	-->
    <select id="listlabelNameById" resultType="com.sgti.manage.feign.entity.TMstLabelL">
		SELECT a.*
		FROM t_mst_label_l a JOIN t_column_channel b on a.id = b.channel_id
		WHERE b.column_id IN (#{columnId}) AND a.del_flag = 0
	</select>

前台

某条数据更新前的数据值：

添加2个值：

option.js中column主要代码

    {
      label: vm.$t('column.title.language'),
      prop: 'language',
      type: 'select',
      span: 12,
      width: 120,
      search: true,
      cascaderItem: ['channel'],
      dicUrl: '/common/dict/type/language_type',
      placeholder: vm.$t('column.tips.changeLanguage'),
      rules: [
        {
          required: true,
          message: vm.$t('column.tips.languageNull'),
          trigger: 'blur'
        }
      ]
    },
    {
      label: vm.$t('column.title.channel'),
      prop: 'channel',
      type: 'select',
      span: 24,
      width: 240,
      row: true,
      slot: true,
      multiple: true,
      overHidden: true,
      placeholder: vm.$t('advert.tips.changeChannel'),
      dicUrl: `/manage/label/list/SYS_CHANNEL?language={{language}}`,
      props: {
        label: 'labelName',
        value: 'id'
      }
    }

index.vue中取值

		<template>
			<avue-crud>
                <!-- channel表单展示 -->
                <template
                  slot="channel"
                  slot-scope="scope"
                >
                  <span
                    v-for="(item,index) in scope.row.channelLabel"
                    :key="index"
                  >
                    <el-tag>{{ item.labelName }} </el-tag>&nbsp;&nbsp;
                  </span>
                </template>
            </avue-crud>
		</template>

		<script>
			methods:{
                	// 获取分页列表
                    getList(page) {
                      this.tableLoading = true
                      fetchList(
                        Object.assign(
                          {
                            current: page.currentPage,
                            size: page.pageSize
                          },
                          this.searchForm
                        )
                      ).then(response => {
                        this.tableData = response.data.data.records
                        for (let i = 0; i < this.tableData.length; i++) {
                          // 这里将channel列表转化为channelId列表
                          this.tableData[i].channel = this.tableData[i].channelLabel.map(obj => obj.id).join(',').split(',')
                        }
                        console.log(this.tableData)
                        this.page.total = response.data.data.total
                        this.tableLoading = false
                      })
                   	},
                    // 修改前，观察row值
                    handleUpdate(row, index) {
                      this.iconList[0] = row.icon
                      this.themeList[0] = row.theme
                      // const old = row.channel
                      // row.channel = row.channelId
                      this.$refs.crud.rowEdit(row, index)
                      // row.channel = old
                    },
                    //修改后，观察row值
                    update(row, index, done, loading) {
                      this.trimString()
                      this.form.icon = this.iconList[0]
                      this.form.theme = this.themeList[0]
                      updColumn(this.form)
                        .then(() => {
                          this.getList(this.page)
                          done()
                          this.$notify.success(this.$t('tips.edit') + this.$t('tips.success'))
                        })
                        .catch(() => {
                          loading()
                        })
                    },
            }
		</script>

后台修改代码：

    /**
     * 修改专栏
     * @param columnChannelDTO 专栏表
     * @return Boolean
     */
    @Override
    public Boolean updColumn(ColumnChannelDTO columnChannelDTO) {
        // channel:["Technology", "Financial", "Economic", "Sports", "Amusement"] id:"1356116046139715586"
        if (columnChannelDTO != null){
            // 1、清空关于该channel的所有记录
            tColumnChannelMapper.delete(Wrappers.<TColumnChannel>lambdaQuery().eq(TColumnChannel::getColumnId, columnChannelDTO.getId()));

            // 2、拿到前台传来的channel列表
            columnChannelDTO.getChannel().forEach(channel -> {
                // 3、插入中间表数据
                TColumnChannel columnChannel = new TColumnChannel();
                columnChannel.setColumnId(columnChannelDTO.getId());
                columnChannel.setChannelId(channel);
                tColumnChannelMapper.insert(columnChannel);
            });
            // 4、插入column表数据
            TMstColumn tMstColumn = new TMstColumn();
            BeanUtils.copyProperties(columnChannelDTO, tMstColumn);
            columnMapper.updateById(tMstColumn);
            return Boolean.TRUE;
        }
        return Boolean.FALSE;
    }