如何查询多对多

Question

I'm trying to do a many to many query on these fields. I'd like to get:

1) all the posts that are in a category

2) all the categories that are in a post

3) all the posts that are in a category that have a specific id

    posts
+-------------+--------------+
| id          | int(11)      |
| title       | varchar(255) |
| body        | text         |
| parent_id   | int(11)      |
| category_id | int(11)      |
+-------------+--------------+
    post_categories 
+----------+--------------+
| id       | int(11)      |
| category | varchar(255) |
+----------+--------------+
    post_category_bridge
+-------------+-------------+
| id          | int(11)     |
| post_id     | int(11) |
| category_id | int(11) |
+-------------+-------------+

One thing I'm worried about is that I'm using PHP's PDO on a MySQL DB for development but I will be transferring the site to an SQL Server on launch day. I know there are differences between MySQL and SQL Server. Will POD take care of those differences or will I need to re-write these queries.

Thanks in advance.

Answer 1

I'm using the verbose join syntax to be more clear on how the tables are related.

1) all the posts that are in a category

Given the category name, you need to join all three tables.

select p.*
  from post_category c
    join post_category_bridge b on c.id = b.category_id
    join posts p                on p.id = b.post_id
  where c.category = ?

2) all the categories that are in a post

Given the post id, you only need to join the bridge and category tables.

select c.*
  from post_category_bridge b
    join post_category c        on c.id = b.category_id
  where b.post_id = ?

3) all the posts that are in a category that have a specific id

I think you mean looking up posts by category.id here (as opposed to category.name) which is similar to (1) but does not need to join on the category table, as you already know the id; you only need to join the bridge and post tables.

select p.*
  from post_category_bridge b
    join posts                  on p.id = b.post_id
  where b.category_id = ?

I will be transferring the site to an SQL Server on launch day...Will POD take care of those differences or will I need to re-write these queries.

This depends on the queries that end up in your system. If you're writing your own SQL then it will matter if you use features or syntax unique to MySQL during development. I highly recommend testing on SQL Server Long before launch day or you may find launch postponed for a while. You can download a free evaluation version for just this purpose.

Points mentioned in the comments that bear repeating:

• as @freddy mentions, you don't need the posts.category_id field. In many-to-many relationships, the bridge (aka 'junction', 'join', 'map', 'link', etc) table links posts to multiple categories - a single field on the posts table would be used if only one category were allowed.
• as @JamieWong mentions, you should keep types consistent between table keys and foreign keys, e.g. if posts.id is int(11), then post_category_bridge.post_id should also be int(11). Most (all?) databases that enforce foreign key constraints will require this (including MySQL). Why? If a there can be 4294967295 posts (as supported by the 4 byte int) there's little point in a bridge table that only supports linking to 255 posts (as supported by the 1 byte tinyint)
• While your at it... might as well make the IDs (and FKs to those IDs) unsigned.