Good evening, there is a problem that I can’t solve. There are 3 tables:

  1. tm_posts - data about posts and pages
  2. tm_attributes - data on tags and categories
  3. tm_relationships - links 1 and 2 tables

Example structure:

tm_posts: id | name | text ------------------- 1 | kols | <ТЕКСТ> 2 | ando | <ТЕКСТ> 3 | krot | <ТЕКСТ> tm_attributes: id | sign | attr_name ------------------- 1 | tag | учебник 2 | tag | дождь 3 | cat | личное 4 | cat | опыт tm_relationships: id | id_attr | id_post ------------------- 1 | 1 | 2 2 | 2 | 2 3 | 3 | 2 4 | 4 | 2

I need to get this result by nick ando:

 id | name | text | tags | category | ----------------------------------------------------- 2 | ando | <ТЕКСТ> | учебник,дождь | личное,опыт |

Those. data from tm_posts table is taken by nick ando, a bunch of tags and categories are searched for by post id in tm_relationships table. Then we get the data from the tm_attributes table. This data is merged into a string.

That's what happened to do:

 SELECT t1.*, GROUP_CONCAT(t3.attr_name) As `tags` FROM tm_posts t1 JOIN tm_relationships t2 ON t1.id = t2.id_post JOIN tm_attributes t3 ON t3.id = t2.id_attr WHERE t1.name= 'ando' AND t3.sign = 'tag' GROUP BY t1.id

This code displays everything except categories. How do I add more categories here.

----------------------------- Added:

It is necessary to select all the records from the database with the category "personal", but at the same time, for each record you need to paste a column with a bunch of tags.

The structure of the tables remains the same. The result should produce something like this:

 id | name | text | tags | category | ---------------------------------------------------------------------- 2 | ando | <ТЕКСТ> | учебник,дождь | личное | 5 | ник | <ТЕКСТ> | какие-то теги | личное | 6 | ник | <ТЕКСТ> | какие-то теги | личное |

Those. selected all entries with tags, but only with a certain category of "personal"

    2 answers 2

    I think you can make two join to tm_attributes like

     SELECT t1.*, GROUP_CONCAT(t3.attr_name) As `tags`,GROUP_CONCAT(t4.attr_name) As `cats` FROM tm_posts t1 JOIN tm_relationships t2 ON t1.id = t2.id_post left JOIN tm_attributes t3 ON t3.id = t2.id_attr and t3.sign = 'tag' left JOIN tm_attributes t4 ON t4.id = t2.id_attr and t4.sign = 'cat' WHERE t1.name= 'ando' GROUP BY t1.id

    To select only the personal category, only such a fiddle request came to mind:

    1) First select all Post with category personal

    2) do join to categories

      select cat_data.*, GROUP_CONCAT(t3.attr_name) As `tags` from (select t1.*,t4.attr_name from tm_attributes t4 join tm_relationships t2 on id_attr=t4.id join tm_posts t1 on id_post=t1.id where t4.sign = 'cat' and t4.attr_name='личное' ) cat_data join tm_relationships t2 on id_post=cat_data.id left JOIN tm_attributes t3 ON t3.id = t2.id_attr and t3.sign = 'tag' group by cat_data.id
    • Corrected the answer and added a link to fiddle - Batanichek
    • Wow, thank you so much! I would not have thought of such a request, it is difficult. Everything works as it should! I also made a request, rearranging the tables in places, everything displays as it should, however, the Tags field displays all existing tags. If suddenly you are interested - sqlfiddle.com/#!9/5d0b3/1 - Pavel
     SELECT t1.*, GROUP_CONCAT(if(t3.sign='tag',t3.attr_name,NULL)) As `tags`, GROUP_CONCAT(if(t3.sign='cat',t3.attr_name,NULL)) As `cats` FROM tm_posts t1 JOIN tm_relationships t2 ON t1.id = t2.id_post JOIN tm_attributes t3 ON t3.id = t2.id_attr WHERE t1.name= 'ando' AND t3.sign in('tag','cat') GROUP BY t1.id

    The condition t3.sign in('tag','cat') can be removed if there are no other types except tag and cat and it is not foreseen. And I think tm_relationships should be glued to LEFT JOIN, in case some article can have neither tags nor categories.

    • @Batanichek group_concat is only in MySQL, so the choice of database is obvious. In oracle if replace with decode or for compatibility with other databases on case - Mike
    • Thank you for your reply. Will this option work faster than with two join tm_attributes? - Paul
    • @ Pavel yes. although I think the difference will be small enough - Mike