Related posts: Get records by tags from different tables

I'm working on 开发者_如何学运维a news site. Like every news site there will be news, columns, videos and photo galleries. I'm planning to keep these different types of records in different tables but relate them with tags. Here is a simple schema:

Tables: News, Videos, Galleries, Columns, Tags, Post_to_tags

Post_to_tags:

- tagid

- postid

- posttype [news,video,gallery,column]

Now what I need to do is get related records for a post in a single query. It's easy to join one table and get related posts but when it comes to different tables... Any idea?

You may want to do it in two queries, as doing it in one query will be quite ugly, and may not buy you much in terms of speed.

So you would use the tags to get all the postids that relate, then just do a join to get the articles and associated records that relate to the postids.

select n.*, g.*, v.* FROM News n 
   INNER JOIN Galleries g ON(g.postid=n.postid) 
   INNER JOIN Videos v ON(v.postid=n.postid) 
   WHERE n.postid IN(
     (SELECT p.postid FROM Post_to_tags p WHERE ...)
   )

This should be a starting point, but I see your problem, as you have posttype.

Why not ignore posttype for this query and just use the same postid, for the story, so they have a way to be searched easily.

If your query is getting the same number of fields with the same datatypes, you can use a UNION.

SELECT fielda, fieldb FROM news n 
  JOIN post_to_tags p ON (n.post_id=p.post_id) 
  where p.tag_id='x' and p.post_type='news'
UNION
SELECT fielda, fieldb FROM videos v 
  JOIN post_to_tags p ON (v.post_id=p.post_id) 
  where p.tag_id='x' and p.post_type='videos'

But if this is the case, you may want to reconsider your schema. Something like:

• Post table with fields common to all post types, and a flag field for post type
• Details_X tables with fields specific to type X posts

will allow a single query:

SELECT fielda, fieldb FROM posts p 
  JOIN post_to_tags pt ON (p.post_id=pt.post_id) 
  where pt.tag_id='x';