Getting page blocks

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.


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'

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.

those are ~not~ duplicate rows :slight_smile:

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'

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.

yeah, you’re right, go with yours :slight_smile:

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