Mysql - Help me alter this search query involving multiple joins and conditions to get the desired r
- by sandeepan-nath
About the system -
We are following tags based search.
Tutors create packs - tag relations for tutors stored in tutors_tag_relations and those for packs stored in learning_packs_tag_relations. All tags are stored in tags table.
The system has 6 tables - tutors, Users (linked to tutor_details), learning_packs, learning_packs_tag_relations, tutors_tag_relations and tags
Please run the following fresh queries to setup the system :-
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');
===================================================================================
Requirement
Now I want to search learning_packs, with the same AND logic. Help me modify the following query so that searching pack name or tutor's name, surname results all active packs (either directly those packs or packs created by those tutors).
==================================================================================
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
As you can see, searching "Cricket 1" returns that pack but searching Vivian Richards does not return the same pack.
Please help
