RE: [HACKERS] Counting bool flags in a complex query

Why don't you adjust the ids of your system folders, such that they are
ordered properly? You should have a fixed number of system folders, so you
can guarantee the ids that they will receive. So make the Inbox -4. Then
you just order by folder id, ascending. -4 comes first, with the user
folders always coming after the system folders.

Alternatively, you can sort by an expression, something like:

ORDER BY (if(folderid < 0) then return(abs(folderid)) else
return(folderid+max(abs(MIN(folderid)))))

What this does is shift all the ids up to ensure that they all fall into the
positive range, while inverting the order of the negative ids, which seems
like it's what you want. Of course, this isn't legal SQL. You would
probably have to write a function to implement this. This will work no
matter what folders you add, system or user, and will always give you the
oldest folders (i.e.: those with the lowest absolute id) first, for each
group.
The MAX will make it slow though, except, of course, that in a function, you
can store the value, instead of repeatedly looking it up.

So:

SELECT folderid,
foldername,
count(*) as "messgaes",
sum(bool2int(flagnew)) as "newmessages",
sum(contentlength) as "size"
FROM usermail,folders
WHERE usermail.loginid='michael' AND
folders.loginid=usermail.loginid AND
usermail.folder = folders.folderid
GROUP BY folderid,foldername

UNION ALL

SELECT folderid,
foldername,
0,
0,
0
FROM folders
WHERE loginid='michael' AND
NOT EXISTS (SELECT folder
FROM usermail
WHERE loginid='michael' AND
folder=folderid
)

ORDER BY get_effective_order(folderid);

And then define the function get_effective_order using pgsql to return the
value described above.

However, I don't think that you are going to get away from the UNION ALL.

BTW
If you are going to do this:
>> fastmail=> select 1 as "test" order by (test<9);
then why not just do this:
select 1 as "test" order by (1<9);
If you actually have a field, then you would be able to put it in. If you
have an expression like this:
select x+y\z as "some_number" from test order by (somenumber>9);
then you could just as easily do this:
select x+y\z as "some_number" from test order by (x+y\z>9);
That's why the expression will not evaluate properly, I think.

MikeA

>>
>> My folder numbers are: negative numbers are system folders
>> such as New
>> mail, trash, drafts and sentmail. I wanted to order the
>> tuples so that the
>> folderids were sorted from -1 to -4, then 1 to x. This way the system
>> folders would always appear first in the list.

<big snip>

>> Using a column name within an expression in the order by
>> does not seem to
>> work...
>> Or a much simpler example to illustrate the bug:
>> fastmail=> select 1 as "test" order by (test<9);
>> ERROR: attribute 'test' not found
>>
>> fastmail=> select 1 as "test" order by test;
>> test
>> ----
>> 1
>> (1 row)
>>
>>

<not so big snip>

>>
>> Do I need outer joins to make this work instead of the
>> screwed up union
>> method I'm trying here, or is it just a series of bugs?
>>
>> -Michael
>>
>>