View for a MANY_MANY_MANY relationship

Question

Let's say I have 3 main tables: category, tag, url

category and tag have a MANY_MANY relationship (category_tag).

tag and url have a MANY_MANY relationship (url_tag).

I would like for a given category to retrieve all the linked urls.

What is the easiest, most efficient way to do this?

Could I maybe build a "VIEW"? What would be the query?

I am using MySQL.

Answer 1

Encapsulating the queries into Views or not, is your choice. For every one of the below queries, you can create a view:

CREATE VIEW category_url AS
  ( Query
  ) ;

If you want only the IDs of the categories and URLs, the simplest (joining only 2 tables) is this - no need for joining the tag table, as long as the foreign key constraints are defined:

    SELECT DISTINCT 
        ct.category_id,
        ut.url_id 
    FROM  
        category_tag AS ct 
      JOIN 
        url_tag AS ut
          ON ut.tag_id = ct.tag_id 

or (the equivalent) which can be easily extended to include the number of tags relating a category with a url:

    SELECT 
        ct.category_id,
        ut.url_id,
        COUNT(*) AS number_of_joining_tags         --- optional
    FROM  
        category_tag AS ct 
      JOIN 
        url_tag AS ut
          ON ut.tag_id = ct.tag_id 
    GROUP BY 
        ct.category_id,
        ut.url_id 

---

If you also want columns from the category and url tables as well (which is very probable), you need to join those tables, too:

    SELECT 
        ct.category_id,
        ut.url_id,
        COUNT(*) AS number_of_joining_tags,        --- optional

        c.*,                                       --- the columns from `category`
        u.*                                        --- and `url` that you need
    FROM  
        category_tag AS ct 
      JOIN 
        url_tag AS ut
          ON ut.tag_id = ct.tag_id 

      JOIN 
        category AS c 
          ON c.id = ct.category_id
      JOIN 
        url AS u
          ON u.id = ut.url_id

    GROUP BY 
        ct.category_id,
        ut.url_id 

Answer 2

I created the following VIEW:

CREATE VIEW category_url AS
(SELECT DISTINCT category.id AS category_id, url.id AS url_id 
FROM category
JOIN category_tag ON category_tag.category_id=category.id
JOIN url_tag ON url_tag.tag_id = category_tag.tag_id
JOIN url ON url.id = url_tag.url_id)