MySQL: How to do a match all on linked items with our new table structure?

Question

We're migrating from seperate relational tables for Many-To-Many, One-To-Many and Many-To-One relations to one single relational table which can be read bidirectional.

We've converted almost all of the code and it seems to work perfectly, except for the GROUP BY HAVING COUNT as we do have 2 different columns in the new situation to check (instead of 1 in the old situation) and we don't know how to do this correctly.

Old tables:

`news`:
id | ...
1  | ...
2  | ...
3  | ...

`news_link`:
news_id | link_module | link_id
1       | category    | 1
1       | category    | 3
2       | category    | 1
3       | category    | 3

Old query:

SELECT * FROM `news` 
JOIN `news_link` 
    ON `news_link`.`news_id` = `news`.`id` 
    AND `news_link`.`link_id` IN (1,3) 
    AND `news_link`.`link_module` = 'category' 
GROUP BY `news`.`id` 
HAVING COUNT(DISTINCT `news_link`.`link_id`) = 2;

Old result:

`news`:
id | ...
1  | ...

Current tables:

`news`:
id | ...
1  | ...
2  | ...
3  | ...

`link`:
source_module | source_id | destination_module | destination_id
news          | 1         | category           | 1
news          | 1         | category           | 3
news          | 2         | category           | 1
news          | 3         | category           | 3

Current query:

SELECT * FROM `news` 
WHERE `id` IN (
    SELECT `link`.`source_id` AS `link_id`
    FROM `link`
    WHERE (`link`.`destination_module` = 'category' AND `link`.`destination_id` IN (1,3) AND `link`.`source_module` = 'news')
    UNION
    SELECT `link`.`destination_id` AS `link_id`
    FROM `link`
    WHERE (`link`.`source_module` = 'category' AND `link`.`source_id` IN (1,3) AND `link`.`destination_module` = 'news')
);

Current result:

`news`:
id | ...
1  | ...
2  | ...
3  | ...

At this point we're missing the GROUP BY HAVING COUNT which we did use to do a match all. For example, we only want to retrieve the news items which are linked to category 1 as well as 3. Not if they are only linked to category 1 or category 3.

What we've already tried:

@ids = SELECT `link`.`source_id` AS `link_id`
    FROM `link`
    WHERE (`link`.`destination_module` = 'category' AND `link`.`destination_id` IN (1,3) AND `link`.`source_module` = 'news')
    UNION
    SELECT `link`.`destination_id` AS `link_id`
    FROM `link`
    WHERE (`link`.`source_module` = 'category' AND `link`.`source_id` IN (1,3) AND `link`.`destination_module` = 'news';

SELECT * FROM `news` 
WHERE `id` IN (@ids)
AND COUNT(@ids) = 2;

With this solution we get back news ids 1,2,3 while we're checking for the category ids 1,3, so we get a AND 3 = 2, so we get no items back and have an invalid comparison as we're not comparing the amount of news items which are linked to both categories, but the amount of news items which are linked to either one of the categories.

Another try:

@ids = SELECT `link`.`source_id` AS `link_id`
    FROM `link`
    WHERE (`link`.`destination_module` = 'category' AND `link`.`destination_id` IN (1,3) AND `link`.`source_module` = 'news')
    UNION
    SELECT `link`.`destination_id` AS `link_id`
    FROM `link`
    WHERE (`link`.`source_module` = 'category' AND `link`.`source_id` IN (1,3) AND `link`.`destination_module` = 'news')
HAVING COUNT(DISTINCT `link`.`link_id`) = 2;

Error: Unknown column 'link.link_id' in 'having clause'

SELECT * FROM `news` 
WHERE `id` IN (@ids);

Has anyone an idea how to make it possible to convert the old query to the new table and having the same result as in the old situation?

EDIT:

Our goal

Our goal is like in the examples below to have code which retrieves the related modules from the link table for a single entity.

$News = $EntityManager->getRepository("News")->fetch(1);
var_dump($News->getLinks("Category"));

with output:

array(2) { [0]=> object(Category)#1 (2) { ["id"]=> int(1) ["title"]=> string(4) "Test" } [1]=> object(Category)#2 (2) { ["id"]=> int(3) ["title"]=> string(6) "Test 3" } }

and

$Category = $EntityManager->getRepository("Category")->fetch(1);
var_dump($Category->getLinks("News"));

with output:

array(2) { [0]=> object(News)#1 (2) { ["id"]=> int(1) ["title"]=> string(4) "News" } [1]=> object(News)#2 (2) { ["id"]=> int(2) ["title"]=> string(6) "News 2" } }

but also:

$Form = $EntityManager->getRepository("Form")->fetch(1);
var_dump($Form->getLinks("Field"));

with output:

array(2) { [0]=> object(Field)#1 (2) { ["id"]=> int(22) ["title"]=> string(8) "Field 22" } [1]=> object(Field)#2 (2) { ["id"]=> int(23) ["title"]=> string(8) "Field 23" } }

neither of your last queries runs in mysql – nbk Nov 02 '20 at 10:37 — nbk, Nov 02 '20 at 10:37

score 1 · Answer 1 · answered Nov 02 '20 at 10:45

1

I really don't get what you try to achieve

SELECT * FROM `news` 
JOIN `link` 
    ON `link`.`source_id` = `news`.`id` 
    AND `link`.`destination_id` IN (1,3) 
    AND `link`.`destination_module` = 'category' 
GROUP BY `news`.`id` 
HAVING COUNT(DISTINCT `link`.`destination_id`) = 2;

Your query is the almost the same.

your queries are not completely logical as link.source_module = 'category' doesn't exist. But you can go from there an make you way to the wanted result.

For example add

link.source_module = 'news'

sample

answered Nov 02 '20 at 10:45

nbk

45,398
8
30
47

Hi @nbk, thanks for your answer. I'll try to clarify what I want to achieve. Instead of creating multiple _link-tables for every module (f.e. news_link, category_link, faq_link) we've rewritten our code that we just use 1 single link table (link) in which you could save the relation between two modules. When you link category 1 to news 1 it's the same as linking news 1 to category 1. So we have created a "source" and a "destination" column which thus is bidirectional. That's the difference with the old tables which were onedirectional (news 1 is linked to category 1, but not the inverse) – Erwin iBiZZ Nov 02 '20 at 14:10
so you wqan a hierachicqal tree. (see http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) also you should normalize your table(instead of text like news you have another table with text, that save alot of space, laszt switch to myslq 8 or mariadb where you can use recursive cte see https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query, which makes your lfe much more simple – nbk Nov 02 '20 at 14:33
I definitely have learned something from the links above how to improve the parent-child relationships in our code, but it's not quite what I mean. We have tables for different modules (faq, news, category, form, field, etc.) and we want to connect those modules in a Many-To-Many or One-To-Many relation. F.e. Form 1 has Field 1 (= first name), Field 2 (= last name) and Field 3 (= email). But Field 1 (= first name) is part of Form 1 (Contact) and Form 2 (Support). – Erwin iBiZZ Nov 03 '20 at 10:18
sql gets fast very complicated, and more concepts you enter that are not really a sql thing make the life even more difficult, a sql query begins with what i want to get that is in my sselect part and then you mustt e´get the data from then FROM clause with all sql has to give, and sometmes you have to experiment. Without mysql8 it is a pain to get the right information from such trees. But as you now have everything you need, to succedd. You now have to try, nobody can help you, because nobody except you has all the in formation. – nbk Nov 03 '20 at 12:34

MySQL: How to do a match all on linked items with our new table structure?

EDIT:

1 Answers1