SitePoint Sponsor

User Tag List

Results 1 to 24 of 24
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Control order of rows

    I'm building a simple photo gallery for my website, and I've run into a minor hitch. I can't seem to figure out how to get certain records to display in the order I want them to.

    My photo gallery will have an albums table (album_id, parent_album, album_name, album_desc), and albums can have "children" (thus the "parent_album" column). So I can have something like this:

    Christmas
    - 2012
    - 2011
    - 2010

    Christmas is an album, and the years are sub-albums within it. When I do a query, I want the sub-albums to display under their "parents", and not intermingled with all the other albums. This is the query I've started off with:

    Code:
    SELECT    albums.album_id
    	, albums.parent_album
    	, albums.album_name
    	, albums.album_desc
    	, a.album_name AS parent
    	, ( SELECT COUNT(pic_id)
    	    FROM photos
    	    WHERE pic_album = albums.album_id ) AS pics
    FROM albums
    LEFT JOIN albums AS a
    	ON a.album_id = albums.parent_album
    ORDER BY parent_album, album_name
    This gives me the name of the album, the children, and the number of pics in each album, but the ORDER BY clause doesn't do what I want. Is this something I need to handle in the code and not the query?
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    ... but the ORDER BY clause doesn't do what I want.
    what is it that you want it to do? what does it actually do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Well I want all the sub-albums to appear beneath their parent, like this:

    Christmas
    -2012
    -2011
    -2010
    Vacations
    - Greece
    - Scotland
    - South Africa

    And right now I get

    Chrismas
    Vacations
    -2012
    -2011
    -2010
    - Greece
    - Scotland
    - South Africa
    <cfset myblog = "http://cydewaze.org/">

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I'm guessing that in your query, the parent_album is NULL if the album has no parent. Use COALESCE to resolve that problem:
    Code:
    SELECT    
        children.album_id
      , COALESCE(children.parent_album, children.album_id) AS id
      , children.album_name
      , children.album_desc
      , parents.album_name AS parent
      , ( SELECT COUNT(pic_id)
          FROM photos
          WHERE pic_album = children.album_id ) AS pics
    FROM albums AS children
    LEFT JOIN albums AS parents
    ON parents.album_id = children.parent_album
    ORDER BY id, children.album_name
    P.S. I gave both tables an alias that made things a bit clearer to me.

  5. #5
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    I'm guessing that in your query, the parent_album is NULL if the album has no parent.
    Actually, it's 0, but I think that doesn't matter (or maybe it does?)

    Quote Originally Posted by guido2004 View Post
    Use COALESCE to resolve that problem:
    Many years of writing queries for MS Access had me forgetting that Coalesce existed.

    I'll give it a try when I get home tonight, thanks!
    <cfset myblog = "http://cydewaze.org/">

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by cydewaze View Post
    Actually, it's 0, but I think that doesn't matter (or maybe it does?)
    Well, no and yes. It does cause the problem, but the solution changes a bit:
    Code:
    SELECT    
        children.album_id
      , COALESCE(parents.album_id, children.album_id) AS id
      , children.album_name
      , children.album_desc
      , parents.album_name AS parent
      , ( SELECT COUNT(pic_id)
          FROM photos
          WHERE pic_album = children.album_id ) AS pics
    FROM albums AS children
    LEFT JOIN albums AS parents
    ON parents.album_id = children.parent_album
    ORDER BY id, children.album_name

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    FYI the correct value to use is NULL, not 0

    the reason is, with 0 you can never actually declare it as a foreign key (because there's no row with PK=0)

    if you use 0, you can use NULLIF inside of COALESCE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    FYI the correct value to use is NULL, not 0

    the reason is, with 0 you can never actually declare it as a foreign key (because there's no row with PK=0)
    The reason I was using 0 for parent albums was that the column was type INT and I was worried about getting invalid data type errors. In ColdFusion, if I cfparam something with a "" default, it throws an error if it's a numeric column type.

    But I guess null is neither a number or text, so the change shouldn't cause too many problems.
    <cfset myblog = "http://cydewaze.org/">

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    In ColdFusion, if I cfparam something with a "" default, it throws an error if it's a numeric column type.
    so, don't do that then
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    @ rudy

    Guido's first solution worked perfectly after I added a DESC to the ORDER BY. I also changed my zero default for the parent_album column to null default, and tweaked my code to match.

    I also got my upload feature to work (using CF's new multi-file upload box), creating thumbnails, medium, and full-size images.

    Now I just have to figure out how I want the album to look. Time to start Googling gallery layout examples.

    Thanks for the help you guys!
    <cfset myblog = "http://cydewaze.org/">

  11. #11
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I think I have an alphabetical problem with this query.

    Today I added some albums for concert pics. My parent album was called U2 360 Tour.

    I added the following sub-albums:
    - Wembley Stadium - London, England
    - Rogers Center - Toronto, ON
    - Hampden Park - Glasgow, Scotland
    - FedEx Field - Landover, MD

    But no matter what I do, Wembley appears first, like this:

    - Wembley Stadium - London, England
    U2 360 Tour
    - Rogers Center - Toronto, ON
    - Hampden Park - Glasgow, Scotland
    - FedEx Field - Landover, MD

    When it should appear like this:

    U2 360 Tour
    - Wembley Stadium - London, England
    - Rogers Center - Toronto, ON
    - Hampden Park - Glasgow, Scotland
    - FedEx Field - Landover, MD

    The only thing I can think of is that the order is screwed up because W (in Wembley) comes after U (in U2) and I have the order by DESC. But this still doesn't make total sense since I have another parent/children example with a similar condition that displays fine. I have NO clue what's causing this.

    btw, it has to be order by DESC otherwise the list is flipped upside down with the children above the parents.
    <cfset myblog = "http://cydewaze.org/">

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Sorry you lost me. Could you please post the latest version of the query, and the content of the tables please?

  13. #13
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Of course!

    Query:

    SELECT
    children.album_id
    , COALESCE(children.parent_album, children.album_id) AS id
    , children.album_name
    , children.album_desc
    , children.album_url
    , children.album_date
    , parents.album_name AS parent
    , parents.album_url AS parenturl
    , ( SELECT COUNT(pic_id)
    FROM photos
    WHERE pic_album = children.album_id ) AS pics
    FROM albums AS children
    LEFT JOIN albums AS parents
    ON parents.album_id = children.parent_album
    ORDER BY id, children.album_name DESC
    And the table (couldn't think of a better way to paste it):

    ID, album_url, album_date, parent_album, album_name, album_avatar, album_desc

    Code:
    "1", "temagami", "2013-01-07", NULL, "Temagami", "img_1013.jpg", "Temagami Pictures"
    "2", "summer_2008", "2008-07-25", "1", "Summer 2008", , "2008 Temagami Pics"
    "3", "summer_2010", "2010-07-26", "1", "Summer 2010", "img_1044.jpg", "2010 Temagami Pics"
    "5", "summer_2012", "2012-07-28", "1", "Summer 2012", , "2012 Temagami pics"
    "7", "greece2010", "2013-01-08", NULL, "Greece,  2010", "img_1491.jpg", "Our Trip to Greece"
    "11", "summer_2007", "2007-07-31", "1", "Summer 2007", NULL, "Pics from Temagami,  summmer 2007"
    "12", "uk2009", "2009-08-23", NULL, "London,  Glasgow,  Edinburgh", NULL, "Our trip to London and Scotland "
    "13", "u2360", "2009-08-23", NULL, "U2 360 Tour", NULL, "Following our favorite band "
    "14", "bikes", "2013-01-13", NULL, "Bikes", NULL, "Our bikes and cycling photos."
    "17", "scotland", "2009-08-23", "13", "Hampden Park - Glasgow,  Scotland", NULL, 
    "21", "toronto", "2013-01-13", "13", "Rogers Center - Toronto,  ON", NULL, 
    "22", "landover", "2013-01-13", "13", "FedEx Field - Landover,  MD", NULL, 
    "23", "raleigh", "2013-01-13", "13", "Carter-Finley Stadium - Raleigh,  NC", NULL, 
    "26", "london", "2013-01-14", "13", "Wembley Stadium - London,  England", NULL,
    <cfset myblog = "http://cydewaze.org/">

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    And the table (couldn't think of a better way to paste it):

    ID, album_url, album_date, parent_album, album_name, album_avatar, album_desc
    the better way is to do a SHOW CREATE TABLE

    btw, nice post count, dude
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    another thing i'd like to comment on --
    Code:
    FROM albums AS children
     LEFT JOIN albums AS parents
     ON parents.album_id = children.parent_album
    this code implicitly expects there to be children without parents

    which doesn't make sense if your database is properly set up, right?

    holler if you don't understand why

    meanwhile, i shall continue testing for you -- btw your posted sample data has holes in it

    if you would use the dump utility, it automatically produces the CREATE TABLE statement as well as proper INSERT statements for the data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I thought Guido wanted the actual contents of the table.

    Here's show create table:

    Code:
    CREATE TABLE `albums` (
     `album_id` int(11) NOT NULL AUTO_INCREMENT,
     `album_url` varchar(64) NOT NULL,
     `album_date` date NOT NULL,
     `parent_album` int(11) DEFAULT NULL,
     `album_name` varchar(64) NOT NULL,
     `album_avatar` varchar(64) DEFAULT NULL,
     `album_desc` varchar(254) NOT NULL,
     PRIMARY KEY (`album_id`),
     KEY `album_url` (`album_url`)
    ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8
    And here's the dump:

    Code:
    INSERT INTO `albums` (`album_id`, `album_url`, `album_date`, `parent_album`, `album_name`, `album_avatar`, `album_desc`) VALUES
    (1, 'temagami', '2013-01-07', NULL, 'Temagami', 'img_1013.jpg', 'Temagami Pictures'),
    (2, 'summer_2008', '2008-07-25', 1, 'Summer 2008', '', '2008 Temagami Pics'),
    (3, 'summer_2010', '2010-07-26', 1, 'Summer 2010', 'img_1044.jpg', '2010 Temagami Pics'),
    (5, 'summer_2012', '2012-07-28', 1, 'Summer 2012', '', '2012 Temagami pics'),
    (7, 'greece2010', '2013-01-08', NULL, 'Greece, 2010', 'img_1491.jpg', 'Our Trip to Greece'),
    (11, 'summer_2007', '2007-07-31', 1, 'Summer 2007', NULL, 'Pics from Temagami, summmer 2007'),
    (12, 'uk2009', '2009-08-23', NULL, 'London, Glasgow, Edinburgh', NULL, 'Our trip to London and Scotland '),
    (13, 'u2360', '2009-08-23', NULL, 'U2 360 Tour', NULL, 'Following our favorite band '),
    (14, 'bikes', '2013-01-13', NULL, 'Bikes', NULL, 'Our bikes and cycling photos.'),
    (17, 'scotland', '2009-08-23', 13, 'Hampden Park - Glasgow, Scotland', NULL, ''),
    (21, 'toronto', '2013-01-13', 13, 'Rogers Center - Toronto, ON', NULL, 'Rogers Center - Toronto, ON'),
    (22, 'landover', '2013-01-13', 13, 'FedEx Field - Landover, MD', NULL, ''),
    (23, 'raleigh', '2013-01-13', 13, 'Carter-Finley Stadium - Raleigh, NC', NULL, ''),
    (26, 'london', '2013-01-14', 13, 'Wembley Stadium - London, England', NULL, 'Wembley Stadium - London, England');
    The "holes" are probably columns where I don't have data. Like, there's a column called "album_avatar" which is the thumbnail pic shown when you view the main gallery page. If that's blank, the gallery page picks the first thumbnail in the album to show. If I don't like that pic, I can go in and pick another one, and that's what goes in that column.

    Other "holes" are things like album description that I just haven't filled out yet. Maybe I should default all those to NULLs to avoid the holes.

    But no, there are no children without parents, but there are parents with no children.

    Now that I understand COALESCE, I've tried playing with that query but my changes always make things worse. I think maybe while I understand what COALESCE does, I'm still having a hard time visualizing it.
    <cfset myblog = "http://cydewaze.org/">

  17. #17
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Don't use the album name to decide if it's a parent or a child. Use the parent_album value: if it's NULL it's a parent.
    Code:
    SELECT
    children.album_id
    , COALESCE(children.parent_album, children.album_id) AS id
    , children.album_name
    , children.album_desc
    , children.album_url
    , children.album_date
    , parents.album_name AS parent
    , parents.album_url AS parenturl
    , ( SELECT COUNT(pic_id)
    FROM photos
    WHERE pic_album = children.album_id ) AS pics
    FROM albums AS children
    LEFT JOIN albums AS parents
    ON parents.album_id = children.parent_album
    ORDER BY 
        id
      , CASE 
          WHEN children.parent_album IS NULL THEN 0
          ELSE 1
        END
      , children.album_name

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    here ya go, no join required, all albums in the desired sequence --
    Code:
    SELECT album_id
         , parent_album
         , album_name
         , album_desc
         , album_url
         , album_date
      FROM albums 
    ORDER 
        BY COALESCE(parent_album, album_id) 
         , parent_album
         , album_name
    results --
    Code:
     1   NULL   Temagami
    11     1    Summer 2007
     2     1    Summer 2008
     3     1    Summer 2010
     5     1    Summer 2012
     7   NULL   Greece,  2010
    12   NULL   London,  Glasgow,  Edinburgh
    13   NULL   U2 360 Tour
    23    13    Carter-Finley Stadium - Raleigh,  NC
    22    13    FedEx Field - Landover,  MD
    17    13    Hampden Park - Glasgow,  Scotland
    21    13    Rogers Center - Toronto,  ON
    26    13    Wembley Stadium - London,  England
    14   NULL   Bikes
    notice 3rd ORDER BY key puts children in alpha sequence, you could instead use album_id to put children into id sequence
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    another thing i'd like to comment on --
    Code:
    FROM albums AS children
     LEFT JOIN albums AS parents
     ON parents.album_id = children.parent_album
    this code implicitly expects there to be children without parents

    which doesn't make sense if your database is properly set up, right?

    holler if you don't understand why
    Holler
    Never mind. Great solution !

  20. #20
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I was playing and came up with this (with some columns removed so I can wrap my head around it):

    Code:
    SELECT
    	  albums.album_id
    	, albums.album_name
    	, albums.parent_album
    	, children.album_name AS child
    FROM albums
    LEFT JOIN albums AS children
    	   ON children.parent_album = albums.album_id
    WHERE  albums.parent_album IS NULL
    ORDER BY album_id
    Then I made an unordered list using CF's group attribute of cfoutput, and the results look promising. The only problem is that parents without children have "blank" children.

    Temagami
    - Summer 2008
    - Summer 2012
    - Summer 2010
    - Summer 200
    Greece, 2010
    - (blank list item)
    London, Glasgow, Edinburgh
    - (blank list item)
    U2 360 Tour
    - Rogers Center - Toronto, ON
    - Carter-Finley Stadium - Raleigh, NC
    - Hampden Park - Glasgow, Scotland
    - FedEx Field - Landover, MD
    - Wembley Stadium - London, England
    Bikes
    - (blank list item)

    Now I get to look at rudy's code to see how much better it is.
    <cfset myblog = "http://cydewaze.org/">

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    with my query, you can also use CFOUTPUT's GROUP= parameter, just be a little more crafty in how you print parent versus children

    you'll have to add the COALESCE back into the SELECT clause, alias it as "id"
    Code:
    <CFOUTPUT QUERY="myquery" GROUP="id">
    <!--- print parent here --->
    <CFOUTPUT>
    <CFIF LEN(parent_album)>
    <!--- print child here --->
    </CFIF>
    </CFOUTPUT>
    </CFOUTPUT>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    With a couple of tweaks, I have a PERFECT display! Can't ask for much more than that. Thanks rudy and Guido for helping me learn coalesce!

    Now my next task is paginating my main gallery page, which is no small task.
    <cfset myblog = "http://cydewaze.org/">

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    With a couple of tweaks...
    i'm guessing more cleverish code to decide whether to print the <ul> and </ul> tags that go around the children <li> tags
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Actually the <ul> stuff was just to help me understand things better. The final version is actually a table in the "CMS" that gives me control over the album. It tells me how many pics are in each album, lets me edit the album details, add new albums, pick a thumbnail as the album avatar, and I can delete an album and either specify a new album for all its pics to move to, or just delete them all.

    This all took me around 1/3 the time that it took just to figure out how to incorporate my old PHP gallery into my existing website template.

    The tedious part will be FTPing the photos from my old host down to my PC and going through them to delete the crappy ones. My new gallery allows me to either use the CMS (and <cffileupload>) to add pics, or FTP them up and assign them to an album in the CMS.
    <cfset myblog = "http://cydewaze.org/">


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
  •