Getting page blocks

[emurad]

Hello,

I have three tables: 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.

[r937]

...grab the "left" and "right" blocks for a specific page?

SELECT bl.name    AS left_name
     , bl.content AS left_content
     , br.name    AS right_name
     , br.content AS right_content
  FROM pages
INNER
  JOIN pages_blocks AS pbl
    ON pbl.page_id = pages.id
   AND pbl.location = 'left'
INNER
  JOIN blocks AS bl
    ON bl.id = pbl.block_id
INNER
  JOIN pages_blocks AS pbr
    ON pbr.page_id = pages.id
   AND pbr.location = 'right'
INNER
  JOIN blocks AS br
    ON br.id = pbr.block_id
 WHERE pages.name = 'specific'

[emurad]

Hi Rudy,

This is returning duplicate row for me when I have for example 3 blocks, 2 on the left and 1 one on the right.

I'm getting:

block1 :: block2
block3 :: block2

Each side of my pages may have a different number of blocks and it changes from one page to another.

[r937]

those are ~not~ duplicate rows

would this suit you better? --

SELECT 'left' AS which_side
     , bl.name    
     , bl.content 
  FROM pages
INNER
  JOIN pages_blocks AS pbl
    ON pbl.page_id = pages.id
   AND pbl.location = 'left'
INNER
  JOIN blocks AS bl
    ON bl.id = pbl.block_id
 WHERE pages.name = 'specific'
UNION ALL
SELECT 'right' AS which_side
     , br.name    
     , br.content 
  FROM pages
INNER
  JOIN pages_blocks AS pbr
    ON pbr.page_id = pages.id
   AND pbr.location = 'right'
INNER
  JOIN blocks AS br
    ON br.id = pbr.block_id
 WHERE pages.name = 'specific'

[emurad]

Thanks it worked. But I'm unsure whether to use your approach or the following:

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

Please advise.

[r937]

yeah, you're right, go with yours

actually, i'm curious...why did you post your question if you already had this query?