Getting page blocks
I have three tab开发者_StackOverflow社区les: pages, blocks and pages_blocks.
My database schema looks as follow:
CREATE TABLE `blocks` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`name` char(40) NOT NULL,
`content` text NOT NULL
PRIMARY KEY (`id`)
);
CREATE TABLE `pages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` char(100) NOT NULL,
`content` mediumtext NOT NULL
PRIMARY KEY (`id`)
);
CREATE TABLE `pages_blocks` (
`page_id` int(11) DEFAULT NULL,
`block_id` smallint(6) DEFAULT NULL,
`location` enum('left','right') DEFAULT NULL,
`display_order` smallint(6) DEFAULT NULL
);
What would be the perfect SQL code to grab the "left" and "right" blocks for a specific page?
Your help is much appreciated.
One way would be two joins searching left and right:
select *
from pages p
left join
(
select *
from pages_blocks pb
join blocks b
on pb.block_id = b.id
where pb.location = 'left'
) left_block
on left_block.page_id = p.id
left join
(
select *
from pages_blocks pb
join blocks b
on pb.block_id = b.id
where pb.location = 'right'
) right_block
on right_block.page_id = p.id
where p.id = 42
This has the advantage of returning only one row without using group by
.
EDIT: if there can be multiple "right" and "left" blocks, you could query all blocks like:
select pb.location
, pb.display_order
, b.name
, b.content
from pages p
join pages_blocks pb
on pb.page_id = p.id
join blocks b
on pb.block_id = b.id
where p.id = 42
order by
pb.location
, pb.display_order
You could use a UNION:
select a.content
from blocks a, pages_blocks b
where a.id=b.block_id and b.page_id=123 and b.location='left'
union
select a.content
from blocks a, pages_blocks b
where a.id=b.block_id and b.page_id=123 and b.location='right'
精彩评论