Mysql - 帮助我更改此涉及多个联接和条件的搜索查询以获得所需的结果

Question

关于系统：

• 我们正在遵循基于标签的搜索。

• 教师创建包 - 存储在tutorials_tag_relations中的教师的标签关系以及存储在learning_packs_tag_relations中的包的标签关系。所有标签都存储在标签表中。

• 系统有 6 个表 - 导师、用户（链接到 coach_details）、learning_packs、learning_packs_tag_relations、tutorials_tag_relations 和标签。

  系统有 6 个表 - 导师、用户（链接到

请运行以下新查询来设置系统：

CREATE TABLE IF NOT EXISTS learning_packs_tag_relations ( id_tag int(10) unsigned NOT NULL DEFAULT '0', id_tutor int(10) DEFAULT NULL, id_lp int(10) unsigned DEFAULT NULL, KEY Learning_Packs_Tag_Relations_FKIndex1 (id_tag), KEY id_lp (id_lp), KEY id_tag (id_tag) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS learning_packs ( id_lp int(10) unsigned NOT NULL AUTO_INCREMENT, id_status int(10) unsigned NOT NULL DEFAULT '2', id_author int(10) unsigned NOT NULL DEFAULT '0', name varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (id_lp) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;

CREATE TABLE IF NOT EXISTS tutors_tag_relations ( id_tag int(10) unsigned NOT NULL DEFAULT '0', id_tutor int(10) DEFAULT NULL,

KEY Tutors_Tag_Relations (id_tag), KEY id_tutor (id_tutor), KEY id_tag (id_tag) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS users ( id_user int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(100) NOT NULL DEFAULT '', surname varchar(155) NOT NULL DEFAULT '',

PRIMARY KEY (id_user)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=52 ;

CREATE TABLE IF NOT EXISTS tutor_details ( id_tutor int(10) NOT NULL AUTO_INCREMENT, id_user int(10) NOT NULL, PRIMARY KEY (id_tutor) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=60 ;

CREATE TABLE IF NOT EXISTS tags ( id_tag int(10) unsigned NOT NULL AUTO_INCREMENT, tag varchar(255) DEFAULT NULL, PRIMARY KEY (id_tag), UNIQUE KEY tag (tag) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

ALTER TABLE learning_packs_tag_relations ADD CONSTRAINT Learning_Packs_Tag_Relations_ibfk_1 FOREIGN KEY (id_tag) REFERENCES tags (id_tag) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE learning_packs

ADD CONSTRAINT Learning_Packs_ibfk_2 FOREIGN KEY (id_author) REFERENCES users (id_user) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE tutors_tag_relations ADD CONSTRAINT Tutors_Tag_Relations_ibfk_1 FOREIGN KEY (id_tag) REFERENCES tags (id_tag) ON DELETE NO ACTION ON UPDATE NO ACTION;

INSERT INTO test.users ( id_user , name , surname ) VALUES ( NULL , 'Vivian', 'Richards' ), ( NULL , 'Sachin', 'Tendulkar' );

INSERT INTO test.users ( id_user , name , surname ) VALUES ( NULL , 'Don', 'Bradman' );

INSERT INTO test.tutor_details ( id_tutor , id_user ) VALUES ( NULL , '52' ), ( NULL , '53' );

INSERT INTO test.tutor_details ( id_tutor , id_user ) VALUES ( NULL , '54' );

INSERT INTO test.tags ( id_tag , tag ) VALUES ( 1 , 'Vivian' ), ( 2 , 'Richards' );

INSERT INTO test.tags (id_tag, tag) VALUES (3, 'Sachin'), (4, 'Tendulkar'); INSERT INTO test.tags (id_tag, tag) VALUES (5, 'Don'), (6, 'Bradman');

INSERT INTO test.learning_packs (id_lp, id_status, id_author, name) VALUES ('1', '1', '52', 'Cricket 1'), ('2', '2', '52', 'Cricket 2');

INSERT INTO test.tags (id_tag, tag) VALUES ('7', 'Cricket'), ('8', '1'); INSERT INTO test.tags (id_tag, tag) VALUES ('9', '2');

INSERT INTO test.learning_packs_tag_relations (id_tag, id_tutor, id_lp) VALUES ('7', '52', '1'), ('8', '52', '1'); INSERT INTO test.learning_packs_tag_relations (id_tag, id_tutor, id_lp) VALUES ('7', '52', '2'), ('9', '52', '2');

导师 Vivian Richards 创建了两个 Learning_pack -“Cricket 1”（活动）和“Cricket 2”（非活动）。

要求：

现在我想搜索learning_packs，对搜索关键字使用 AND 逻辑。帮助我修改以下查询，以便使用这些术语的任意组合进行搜索 - 包名称或导师的姓名、姓氏结果所有活动包（直接是那些包或由这些导师创建的包）。

=================================================== ================================= 1.搜索“维维安·理查兹”

select lp.*

from Learning_Packs AS lp

LEFT JOIN Learning_Packs_Tag_Relations AS lptagrels ON lp.id_lp = lptagrels.id_lp

LEFT JOIN Tutors_Tag_Relations as ttagrels ON lp.id_author = ttagrels.id_tutor LEFT JOIN Tutor_Details AS td ON ttagrels.id_tutor = td.id_tutor LEFT JOIN Users as u on td.id_user = u.id_user

JOIN Tags as t on (t.id_tag = lptagrels.id_tag) or (t.id_tag = ttagrels.id_tag)

where lp.id_status = 1 AND ( t.tag LIKE "%Vivian%" OR t.tag LIKE "%Richards%" )
group by lp.id_lp HAVING count(lp.id_lp) > 1 limit 0,20

2.搜索“Cricket 1”

select lp.*

from Learning_Packs AS lp

LEFT JOIN Learning_Packs_Tag_Relations AS lptagrels ON lp.id_lp = lptagrels.id_lp

LEFT JOIN Tutors_Tag_Relations as ttagrels ON lp.id_author = ttagrels.id_tutor LEFT JOIN Tutor_Details AS td ON ttagrels.id_tutor = td.id_tutor LEFT JOIN Users as u on td.id_user = u.id_user

JOIN Tags as t on (t.id_tag = lptagrels.id_tag) or (t.id_tag = ttagrels.id_tag)

where lp.id_status = 1 AND ( t.tag LIKE "%Cricket%" OR t.tag LIKE "%1%" ) group by lp.id_lp HAVING count(lp.id_lp) > 1 limit 0,20

如您所见，搜索“Cricket 1”会返回该包，但搜索 Vivian Richards 不会返回相同的包。

Answer 1

第一个查询（搜索“Vivian Richards”）没有返回任何记录的原因是由于连接：

LEFT JOIN Tutor_Details AS td ON ttagrels.id_tutor = td.id_tutor

如果您查看，ttagrels< /code> （即 learning_packs_tag_relations），id_tutor 包含：

id_tutor   
52
52
52
52

而 td （即 tutor_details）包含：

id_tutor
60
61
62

因为其中，不能进行任何连接；这两列中的值都不匹配。

一些意见/评论：

查看表格，似乎有很多冗余。例如，如果导师、作者和用户都是同一个人，并且他们的详细信息都在 users 表中，则引用导师、作者或用户的所有表都应链接到 users.id_user。如果 learning_packs_tag_relations 表将包链接到标签，则不需要 id_tutor 列。它只需要 pack_id 和 tag_id。这是因为 learning_packs 中的包已通过 id_author 列链接到 users 表。

tutor_details 表可能会推翻我的理论，因为它似乎将导师与用户联系起来（我没有在其他地方推断出这一区别），但是 id_tutor 值确实如此似乎与任何东西都没有联系。

也许这里需要重新设计一些数据库；祝你好运。