SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Getting page blocks

    Hello,

    I have three tables: pages, blocks and pages_blocks.

    My database schema looks as follow:

    Code SQL:
    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by emurad View Post
    ...grab the "left" and "right" blocks for a specific page?
    Code:
    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'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    those are ~not~ duplicate rows

    would this suit you better? --
    Code:
    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'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Nov 2010
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks it worked. But I'm unsure whether to use your approach or the following:

    Code:
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yeah, you're right, go with yours

    actually, i'm curious...why did you post your question if you already had this query?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •