Search code examples
mysqlrelated-content

Mysql multi-table / multi-entity related content query

I've got the following tables:

  • magazine_tags
  • news_tags
  • page_tags
  • content_tags
  • faq_tags

They all have exactly the same structure:

uid_local - id of the entity
uid_foreign - id of the tag

Each one of them maps to a Model in PHP.

I need to get related content based on multiple tags. Say I'm viewing an "faq" tagged with 1 and 4, I need to get all items tagged with either 1 or 4, but those tagged with both should be at the top.

I also need to be able to distinguish between the different entities, so I can load them up using their corresponding model in PHP after I run the query.

How would I go about building this query?

Thanks!

Solution

  • I can't test it because I'm mobile but the following should do the trick:

    SELECT uid_local, uid_foreign, count(*) as frequency FROM faq_tags WHERE uid_foreign IN (1,4) GROUP BY uid_local ORDER BY frequency DESC