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'
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