Обсуждение: ORDER BY with exception
I have a lookup table with a bunch of disciplines:
# SELECT id, name FROM discipline;
id | name
----+---------------------
1 | writing
2 | visual arts
3 | music
4 | dance
5 | film and television
6 | theatre
7 | media arts
8 | community
9 | fine craft
10 | other
(10 rows)
and a function that returns each discipline name along with the total
number of records in another table (showcase) that are related to each
discipline. Each showcase entry may have 0 or more items (showcase_item)
related to it, so ones that have no items are disregarded here. Also,
only showcases that have been accepted should be counted.
First, here's the working function:
CREATE FUNCTION getshowcasetotalsbydiscipline(OUT name text, OUT total
integer) RETURNS SETOF record
AS $$
DECLARE
rec record;
BEGIN
FOR rec IN
EXECUTE 'SELECT id, name, 1 AS total FROM discipline'
LOOP
name := rec.name;
SELECT INTO rec.total
-- a showcase may be in the DB but not accepted by an admin
SUM(CASE s.accepted WHEN TRUE THEN 1 ELSE 0 END)
FROM showcase AS s
WHERE s.id IN
-- a showcase may exist with no items, so should be ignored
(SELECT si.showcase_id FROM showcase_item AS si
WHERE si.discipline_id = rec.id);
-- If no showcase items have this discipline,
-- give it a total of zero
IF rec.total IS NULL THEN
SELECT INTO total 0;
ELSE
total := rec.total;
END IF;
RETURN NEXT;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
test=# SELECT * FROM getShowcaseTotalsByDiscipline();
name | total
---------------------+-------
writing | 130
visual arts | 252
music | 458
dance | 131
film and television | 102
theatre | 271
media arts | 83
community | 20
fine craft | 78
other | 59
(10 rows)
Works fine, but i'd like to order the disciplines alphabetically
*except* have 'other' fall at the end. So, should i loop a second time,
after summing the totals, and keep the 'other' row aside, then add it to
the end?
(btw, the output of this function is cached until a new showcase is
accepted)
Or, should i re-order the disciplines alphabetically in the lookup
trable, keeping 'other' to be last?
I could do the latter, although it would mean a fair bit of work because
the disciplines table relates to a bunch of other stuff, as well. Also,
there's always the chance that a new discipline will be added in the
future. I suppose i could write a trigger that bumped the 'other' id
above that of the new entry, then re-relate everything else in the DB
that's connected to the 'other' discipline. But that strikes me as kind
of a hack.
The third option is to re-order the resultset in the PHP script that
displays this. But that wasn't why i chose Postgres for this app ;-)
brian
On 6/21/07, brian <brian@zijn-digital.com> wrote: > I have a lookup table with a bunch of disciplines: > > # SELECT id, name FROM discipline; > id | name > ----+--------------------- > 1 | writing > 2 | visual arts > 3 | music > 4 | dance > 5 | film and television > 6 | theatre > 7 | media arts > 8 | community > 9 | fine craft > 10 | other > (10 rows) > > and a function that returns each discipline name along with the total > number of records in another table (showcase) that are related to each > discipline. Each showcase entry may have 0 or more items (showcase_item) > related to it, so ones that have no items are disregarded here. Also, > only showcases that have been accepted should be counted. > > First, here's the working function: > > CREATE FUNCTION getshowcasetotalsbydiscipline(OUT name text, OUT total > integer) RETURNS SETOF record > AS $$ > > DECLARE > rec record; > > BEGIN > FOR rec IN > EXECUTE 'SELECT id, name, 1 AS total FROM discipline' > LOOP > name := rec.name; > > SELECT INTO rec.total > > -- a showcase may be in the DB but not accepted by an admin > SUM(CASE s.accepted WHEN TRUE THEN 1 ELSE 0 END) > FROM showcase AS s > WHERE s.id IN > > -- a showcase may exist with no items, so should be ignored > (SELECT si.showcase_id FROM showcase_item AS si > WHERE si.discipline_id = rec.id); > > -- If no showcase items have this discipline, > -- give it a total of zero > > IF rec.total IS NULL THEN > SELECT INTO total 0; > ELSE > total := rec.total; > END IF; > > RETURN NEXT; > END LOOP; > > RETURN; > END; > $$ LANGUAGE plpgsql IMMUTABLE; > > test=# SELECT * FROM getShowcaseTotalsByDiscipline(); > name | total > ---------------------+------- > writing | 130 > visual arts | 252 > music | 458 > dance | 131 > film and television | 102 > theatre | 271 > media arts | 83 > community | 20 > fine craft | 78 > other | 59 > (10 rows) > > Works fine, but i'd like to order the disciplines alphabetically > *except* have 'other' fall at the end. So, should i loop a second time, > after summing the totals, and keep the 'other' row aside, then add it to > the end? > > (btw, the output of this function is cached until a new showcase is > accepted) > > Or, should i re-order the disciplines alphabetically in the lookup > trable, keeping 'other' to be last? > > I could do the latter, although it would mean a fair bit of work because > the disciplines table relates to a bunch of other stuff, as well. Also, > there's always the chance that a new discipline will be added in the > future. I suppose i could write a trigger that bumped the 'other' id > above that of the new entry, then re-relate everything else in the DB > that's connected to the 'other' discipline. But that strikes me as kind > of a hack. > > The third option is to re-order the resultset in the PHP script that > displays this. But that wasn't why i chose Postgres for this app ;-) > > brian > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > It seems to me you could replace it all with one query, something like this: SELECT discipline, COUNT(1) FROM showcase WHERE EXISTS (SELECT * FROM showcase_item WHERE showcase_id = showcase.id LIMIT 1) GROUP BY discipline ORDER BY (discipline != 'other'), discipline; - Josh
On Jun 21, 2007, at 17:35 , brian wrote:
> I have a lookup table with a bunch of disciplines:
To answer your ordering question first:
SELECT id, name
FROM discipline
ORDER BY name = 'other'
, name;
id | name
----+---------------------
8 | community
4 | dance
5 | film and television
9 | fine craft
7 | media arts
3 | music
6 | theatre
2 | visual arts
1 | writing
10 | other
(10 rows)
This relies on the fact that FALSE orders before TRUE. I don't always
remember which way, so I often have to rewrite it using <> or = to
get the behavior I want.
> and a function that returns each discipline name along with the
> total number of records in another table (showcase) that are
> related to each discipline. Each showcase entry may have 0 or more
> items (showcase_item) related to it, so ones that have no items are
> disregarded here. Also, only showcases that have been accepted
> should be counted.
>
> First, here's the working function:
I don't think you really need to use a function for this. I believe
you should be able to do this all in one SQL statement, something
like (if I've understood your query and intent correctly):
SELECT discipline.name, COUNT(showcase_id) AS total
FROM discipline
LEFT JOIN (
SELECT DISTINCT discipline_id, showcase.id as showcase_id
FROM showcase
JOIN showcase_item on (showcase.id = showcase_id)
WHERE accepted) AS accepted_showcases
ON (discipline.id = discipline_id)
GROUP BY discipline.name
ORDER BY discipline.name = 'other'
, discipline.name;
name | total
---------------------+-------
community | 0
dance | 0
film and television | 0
fine craft | 0
media arts | 0
music | 0
theatre | 0
visual arts | 1
writing | 2
other | 0
(10 rows)
This should give you the total number of showcases that have been
accepted for each discipline. (DDL and data below.)
As a general rule, it's generally better to let the server handle the
data in sets (i.e., tables) as much as possible rather than using
procedural code.
Hope this helps.
Michael Glaesemann
grzm seespotcode net
CREATE TABLE discipline
(
id INTEGER NOT NULL UNIQUE
, name TEXT PRIMARY KEY
);
INSERT INTO discipline (id, name) VALUES
(1, 'writing')
, (2, 'visual arts')
, (3, 'music')
, (4, 'dance')
, (5, 'film and television')
, (6, 'theatre')
, (7, 'media arts')
, (8, 'community')
, (9, 'fine craft')
, (10, 'other');
SELECT *
FROM discipline
ORDER BY name;
SELECT *
FROM discipline
ORDER BY name = 'other', name;
CREATE TABLE showcase
(
id INTEGER NOT NULL UNIQUE
, name TEXT PRIMARY KEY
, discipline_id INTEGER NOT NULL
REFERENCES discipline(id)
, accepted BOOLEAN NOT NULL
);
INSERT INTO showcase (id, name, discipline_id, accepted)
VALUES
(1, 'foo', 1, true)
, (2, 'bar', 2, true)
, (3, 'baz', 1, true)
, (4, 'quux', 1, false)
, (5, 'blurfl', 2, false);
CREATE TABLE showcase_item
(
id INTEGER NOT NULL UNIQUE
, description TEXT NOT NULL
, showcase_id INTEGER NOT NULL
REFERENCES showcase (id)
, PRIMARY KEY (description, showcase_id)
);
INSERT INTO showcase_item (id, description, showcase_id)
VALUES
(1, 'a', 1)
, (2, 'b', 1)
, (3, 'c', 1)
, (4, 'd', 2)
, (5, 'e', 2)
, (6, 'f', 2)
, (7, 'g', 3)
, (8, 'h', 3)
, (9, 'i', 4)
, (10, 'j', 5);
SELECT *
FROM showcase;
id | name | discipline_id | accepted
----+--------+---------------+----------
1 | foo | 1 | t
2 | bar | 2 | t
3 | baz | 1 | t
4 | quux | 1 | f
5 | blurfl | 2 | f
(5 rows)
SELECT *
FROM showcase
JOIN showcase_item ON (showcase.id = showcase_id);
id | name | discipline_id | accepted | id | description | showcase_id
----+--------+---------------+----------+----+-------------
+-------------
1 | foo | 1 | t | 1 | a
| 1
1 | foo | 1 | t | 2 | b
| 1
1 | foo | 1 | t | 3 | c
| 1
2 | bar | 2 | t | 4 | d
| 2
2 | bar | 2 | t | 5 | e
| 2
2 | bar | 2 | t | 6 | f
| 2
3 | baz | 1 | t | 7 | g
| 3
3 | baz | 1 | t | 8 | h
| 3
4 | quux | 1 | f | 9 | i
| 4
5 | blurfl | 2 | f | 10 | j
| 5
(10 rows)
Josh Tolley wrote: > It seems to me you could replace it all with one query, something like > this: > > SELECT discipline, COUNT(1) FROM showcase WHERE EXISTS (SELECT * FROM > showcase_item WHERE showcase_id = showcase.id LIMIT 1) GROUP BY > discipline ORDER BY (discipline != 'other'), discipline; > discipline is its own table, not a column, so i'd need to throw a join in there. I have another table, showcase_discipline to relate from showcases. I did it this way because there are several other tables that rely on disciplines (one to one and many to one). And thanks for the ORDER BY tip! I had no idea i could do that. I'll see if i can work that in. brian
Michael Glaesemann wrote: > On Jun 21, 2007, at 17:35 , brian wrote: > >> I have a lookup table with a bunch of disciplines: > > > To answer your ordering question first: > > SELECT id, name > FROM discipline > ORDER BY name = 'other' > , name; > id | name > ----+--------------------- > 8 | community > 4 | dance > 5 | film and television > 9 | fine craft > 7 | media arts > 3 | music > 6 | theatre > 2 | visual arts > 1 | writing > 10 | other > (10 rows) > > This relies on the fact that FALSE orders before TRUE. I don't always > remember which way, so I often have to rewrite it using <> or = to get > the behavior I want. > Of course! (slaps forehead) > I don't think you really need to use a function for this. I believe you > should be able to do this all in one SQL statement, something like (if > I've understood your query and intent correctly): > > SELECT discipline.name, COUNT(showcase_id) AS total > FROM discipline > LEFT JOIN ( > SELECT DISTINCT discipline_id, showcase.id as showcase_id > FROM showcase > JOIN showcase_item on (showcase.id = showcase_id) > WHERE accepted) AS accepted_showcases > ON (discipline.id = discipline_id) > GROUP BY discipline.name > ORDER BY discipline.name = 'other' > , discipline.name; > name | total > ---------------------+------- > community | 0 > dance | 0 > film and television | 0 > fine craft | 0 > media arts | 0 > music | 0 > theatre | 0 > visual arts | 1 > writing | 2 > other | 0 > (10 rows) > That's bang on, Michael, thanks a bunch. I never remember to explore joining on a select. I'm forever thinking in terms of joining on a table. Things to study this evening. > As a general rule, it's generally better to let the server handle the > data in sets (i.e., tables) as much as possible rather than using > procedural code. > > Hope this helps. It helped lots, thanks again. brian
On Jun 21, 2007, at 8:08 PM, brian wrote: > Michael Glaesemann wrote: >> On Jun 21, 2007, at 17:35 , brian wrote: >>> I have a lookup table with a bunch of disciplines: >> To answer your ordering question first: >> SELECT id, name >> FROM discipline >> ORDER BY name = 'other' >> , name; >> id | name >> ----+--------------------- >> 8 | community >> 4 | dance >> 5 | film and television >> 9 | fine craft >> 7 | media arts >> 3 | music >> 6 | theatre >> 2 | visual arts >> 1 | writing >> 10 | other >> (10 rows) >> This relies on the fact that FALSE orders before TRUE. I don't >> always remember which way, so I often have to rewrite it using <> >> or = to get the behavior I want. > > Of course! (slaps forehead) > >> I don't think you really need to use a function for this. I >> believe you should be able to do this all in one SQL statement, >> something like (if I've understood your query and intent correctly): >> SELECT discipline.name, COUNT(showcase_id) AS total >> FROM discipline >> LEFT JOIN ( >> SELECT DISTINCT discipline_id, showcase.id as showcase_id >> FROM showcase >> JOIN showcase_item on (showcase.id = showcase_id) >> WHERE accepted) AS accepted_showcases >> ON (discipline.id = discipline_id) >> GROUP BY discipline.name >> ORDER BY discipline.name = 'other' >> , discipline.name; >> name | total >> ---------------------+------- >> community | 0 >> dance | 0 >> film and television | 0 >> fine craft | 0 >> media arts | 0 >> music | 0 >> theatre | 0 >> visual arts | 1 >> writing | 2 >> other | 0 >> (10 rows) > > That's bang on, Michael, thanks a bunch. I never remember to > explore joining on a select. I'm forever thinking in terms of > joining on a table. Things to study this evening. One way to break yourself of that habit is to stop thinking in terms of tables when you query and replace that thinking with relations. Queries are made on relations and tables are only one kind of relation. Then just remember that the results of select queries are relations representing relationships between data in other relations so they can themselves be used in select queries (as well as updates, deletes and, as of 8.2, insert and copy statements). Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com