Mysql - Help me alter this search query involving multiple joins and conditions to get the desired results

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');

Tutor Vivian Richards has created two Learning_packs - "Cricket 1" (active) and "Cricket 2" (inactive).

Requirement:

Now I want to search learning_packs, with AND logic on the search keywords. Help me modify the following query so that searching with any combination of these terms - pack name or tutor's name, surname results all active packs (either directly those packs or packs created by those tutors).

================================================================================== 1. Searching "Vivian Richards"

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. Searching "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

As you can see, searching "Cricket 1" returns that pack, but searching Vivian Richards does not return the same pack.

The reason the first query (Searching "Vivian Richards") returns no records is because of the join:

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

If you look, ttagrels (which is learning_packs_tag_relations), id_tutor contains:

id_tutor   
52
52
52
52

Whereas td (which is tutor_details) contains:

id_tutor
60
61
62

Because of this, no join can be made; none of the values in these two columns match.

Some opinion/commentary:

Looking through the tables it seems there are a lot of redundancies. For example, if a tutor, author, and user are all the same thing, and their details are in the users table, then all tables that refer to a tutor, author or user should link to users.id_user. The learning_packs_tag_relations table doesn't need an id_tutor column if it is linking packs to tags. It just needs pack_id and tag_id. This is because the pack, which is in learning_packs already links to the users table by way of the id_author column.

The tutor_details table is the one that may dispel my theory, because it appears to link tutors to users (making a distinction I did not extrapolate elsewhere), however the id_tutor values do not appear to be linked to anything.

Perhaps some database redesign is needed here; good luck.