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.
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:
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.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)unsigned.Does this help?
1:
select p.* from posts p, post_categories c
where p.category_id=c.id and category='something'
2:
select c.* from post_categories, posts p
where p.category_id=c.id and post_id=something
3:
select p.* from post_categories, posts p
where p.category_id=c.id and post_id=something and category='something'
select a.* from posts a, post_categories b, post_category_bridge c
WHERE
b.category="mycat" AND
b.id=c.category_id AND
a.id = c.post_id;
select b.category from posts a, post_categories b, post_category_bridge c,
WHERE
c.post_id='PostID' AND
b.id = c.category_id;
select a.* from posts a, post_categories b, post_category_bridge c
WHERE
b.category="mycat" AND
c.category_id = b.id AND
c.post_id = 'MyID' AND
a.id = c.post_id;