Unanswered: Multiple relation tables and selections
I'm trying to build a search engine that will search all words in any table that has a relation with my "t_programs" table.
It is always an any-any relation. My programs have multiple writers, multiple themes, multiple cycle ...
I tried this :
t_programs AS P
, t_writers AS T0
, r_programs_writers AS R0
, t_themes AS T1
, r_programs_themes AS R1
, t_cycles AS T2
, r_programs_cycles AS R2
AND ( P.program_id = R0.fk_program_id AND R0.fk_writer_id = T0.writer_id )
AND ( P.program_id = R1.fk_program_id AND R1.fk_theme_id = T1.theme_id )
AND ( P.program_id = R2.fk_program_id AND R2.fk_cycle_id = T2.cycle_id )
T0.writer_label LIKE "%word1%"
OR T1.theme_label LIKE "%word1%"
OR T2.cycle_label LIKE "%word1%"
) AND (
T0.writer_label LIKE "%word2%"
OR T1.theme_label LIKE "%word2%"
OR T2.cycle_label LIKE "%word2%"
I want the programs related to word1 AND word2. This request works fine, as soon as the programs have at least one relation with all the tables. But if the two words are in the t_themes.theme_label, and that the program has no cycle, it won't return. Is there a way to return to results two ? I'm not familiair with the JOIN syntax ...
from t_programs as P
join r_programs_writers as R0
on P.program_id = R0.fk_program_id
join t_writers as T0
on R0.fk_writer_id = T0.writer_id
join r_programs_themes as R1
on P.program_id = R1.fk_program_id
join t_themes as T1
on R1.fk_theme_id = T1.theme_id
join r_programs_cycles as R2
on P.program_id = R2.fk_program_id
join t_cycles as T2
on R2.fk_cycle_id = T2.cycle_id
T0.writer_label like "%word1%"
or T1.theme_label like "%word1%"
or T2.cycle_label like "%word1%"
T0.writer_label like "%word2%"
or T1.theme_label like "%word2%"
or T2.cycle_label like "%word2%"
Thanks a lot lot lot !
It's working very fine, and returns exactly the results i wanted !
I'll just try to optimize my tables a little more to see if i can make it faster (but it's already great, 1 second for 9 tables + relations and 3 words)
Is there a way to make it even faster ? All i did was to set the primary keys and the foreign keys as INDEX ...