solr配置多表关联索引

前言：多表关联索引不是多表索引，注意区分。多表索引正在研究，多表索引的条件限制就是主键的类型，如果是自增主键那么多表索引会出现后者覆盖前者的问题。如果是UUID等唯一的主键则可忽略此问题。

业务需求：将课程的相关信息添加索引到solr。

涉及到的数据表和课程先关信息：

1.课程基本信息表-课程基本信息；

2.课程章节表-课程一共的章节；

3.讲师表-课程的讲师信息；

4.课程价格表-课程的价格信息；

data-config.xml文件配置：

<?xml version="1.0" encoding="UTF-8" ?>
<dataConfig>
    <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
        url="jdbc:mysql://172.168.3.39:3306/reading5_test" user="reading_test" password="reading_test" batchSize="1000" />
    <document>
        <entity name="tb_pay_course" pk="id"
                query="SELECT tpc.id,tpc.course_name,tpc.transverse_pic,tpc.vertical_pic,tpc.modify_time,tpc.price_id,tpc.lecturer_id 
						FROM tb_pay_course tpc 
						JOIN tb_course_chapter tcc ON tcc.course_id = tpc.id AND tcc.state = 1 AND tcc.node_num &gt; 0
						JOIN tb_nodes tn ON tn.course_chapter_id = tcc.id AND tn.state = 1 AND tn.start_time &lt; NOW() AND tn.duration &gt; 0
						WHERE tpc.validity_time &gt; NOW() AND tpc.state = 2 GROUP BY tpc.id"
				deltaImportQuery="SELECT tpc.id,tpc.course_name,tpc.transverse_pic,tpc.vertical_pic,tpc.modify_time,tpc.price_id,tpc.lecturer_id 
						FROM tb_pay_course tpc 
						JOIN tb_course_chapter tcc ON tcc.course_id = tpc.id AND tcc.state = 1 AND tcc.node_num &gt; 0
						JOIN tb_nodes tn ON tn.course_chapter_id = tcc.id AND tn.state = 1 AND tn.start_time &lt; NOW() AND tn.duration &gt; 0
						WHERE tpc.id = ${dataimporter.delta.id} AND tpc.validity_time &gt; NOW() AND tpc.state = 2 GROUP BY tpc.id"
				deltaQuery="SELECT id FROM tb_pay_course where modify_time &gt; ${dataimporter.last_index_time}">
				<field column="id" name="id"/>
				<field column="course_name" name="sCourseName"/>
                <field column="transverse_pic" name="sTransversePic"/>
                <field column="vertical_pic" name="sVerticalPic"/>
                <field column="modify_time" name="sModifyTime"/>
				
					<entity name="tb_lecturer" query="SELECT tl.id,tl.lecturer_name FROM tb_lecturer tl WHERE id = ${tb_pay_course.lecturer_id}">
						<field column="id" name="sLecturerId"/>
						<field column="lecturer_name" name="sLecturerName"/>
					</entity>
					
					<entity name="tb_price_strategy" query="SELECT tps.id,tps.price FROM tb_price_strategy tps WHERE id = ${tb_pay_course.price_id} AND tps.deleted = 0">
						<field column="id" name="sCoursePriceId"/>
						<field column="price" name="sCoursePrice"/>
					</entity>
					
					<entity name="tb_course_chapter" query="SELECT SUM(duration) AS duration,COUNT(id) AS chapterNum,course_id 
									FROM tb_course_chapter WHERE course_id = ${tb_pay_course.id} AND duration &gt; 0 AND node_num &gt; 0 AND state = 1 GROUP BY course_id">
						<field column="duration" name="sCourseDuration"/>
						<field column="chapterNum" name="sCourseChapterNum"/>
					</entity>
					
        </entity>
    </document>
	</dataConfig>

解释：一个大的<entity></entity>标签主表查询语句与查询字段，连接几个表就在内部创建几个<entity></entity>标签，注意查询语句里的where后面的连接条件。大于小于等于号一律转义。

结果：

结语：本来想将书籍的信息和课程的信息索引到一个core后来由于主键的原因没解决所以放到了两个core里，总觉得有点浪费资源。后期看看能不能改进，也欢迎大神提出意见或者建议。一旦采用，将有机会得到500w的奖励。