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:


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?

what is it that you want it to do? what does it actually do?

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

I’m guessing that in your query, the parent_album is NULL if the album has no parent. Use COALESCE to resolve that problem:


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.

Actually, it’s 0, but I think that doesn’t matter (or maybe it does?)

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!

Well, no and yes. It does cause the problem, but the solution changes a bit:

SELECT    
    children.album_id
  , COALESCE([B]parents.album_id[/B], 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

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

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.

so, don’t do that then :smiley:

:smashy: @ 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. :slight_smile:

Thanks for the help you guys!

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.

Sorry you lost me. Could you please post the latest version of the query, and the content of the tables please?

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

"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,

the better way is to do a SHOW CREATE TABLE

btw, nice post count, dude :smiley:

another thing i’d like to comment on –

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

I thought Guido wanted the actual contents of the table.

Here’s show create table:

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:

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.

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.


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

here ya go, no join required, all albums in the desired sequence –


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 –

 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

Holler :slight_smile:
Never mind. Great solution !

I was playing and came up with this (with some columns removed so I can wrap my head around it):

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