Mysql multi-table / multi-entity related content query

Question

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!

Answer 1

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