Re: Effective query for listing flags in use by messages in a folder

If you cannot change the message table and only want something more elegant than union & limit 1 then could produce array of flags and unnest them ... dont know what performance would be like ... i

select
-- folder_id,
flags
from (
select
folder_id,
unnest(
array[
case when max(is_seen::int)=1 then 'is_seen' end,
case when max(is_replied::int)=1 then 'is_replied' end,
case when max(is_forwarded::int)=1 then 'is_forwarded' end,
case when max(is_deleted::int)=1 then 'is_deleted' end,
case when max(is_draft::int)=1 then 'is_draft' end,
case when max(is_flagged::int)=1 then 'is_flagged' end
]
) as flags
from message
group by 1
)x
where folder_id=1 and flags is not null;

?

Hector Vass

+44(0)7773 352 559
* Metametrics, International House, 107 Gloucester Road, Malmesbury, Wiltshire, SN16 0AJ
* www.metametrics.co.uk<http://www.metametrics.co.uk/>

________________________________
From: pgsql-sql-owner(at)postgresql(dot)org <pgsql-sql-owner(at)postgresql(dot)org> on behalf of Hector Vass <hector(dot)vass(at)metametrics(dot)co(dot)uk>
Sent: 18 March 2015 13:19
To: Andreas Joseph Krogh; pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Effective query for listing flags in use by messages in a folder

My recommendation is to hold the data as key value pairs not as a table with columns for each flag..

Taking your table message ... and loading into this key value pair table messaage2..

drop type if exists myflags cascade;
create type myflags as enum('is_seen','is_replied','is_forwarded','is_deleted','is_draft','is_flagged');
drop table if exists message2;
create table message2(
folder_id integer not NULL,
msg varchar(200),
is_flag myflags
);
insert into message2 select folder_id,msg,'is_seen' from message where is_seen is TRUE;
insert into message2 select folder_id,msg,'is_replied' from message where is_replied is TRUE;
insert into message2 select folder_id,msg,'is_forwarded' from message where is_forwarded is TRUE;
insert into message2 select folder_id,msg,'is_deleted' from message where is_deleted is TRUE;
insert into message2 select folder_id,msg,'is_draft' from message where is_draft is TRUE;
insert into message2 select folder_id,msg,'is_flagged' from message where is_flagged is TRUE;

select is_flag from message2 where folder_id=1 group by 1;
?
work=# select is_flag from message2 where folder_id=1 group by 1;
is_flag
------------
is_seen
is_deleted
is_replied
(3 rows)

Hector Vass

+44(0)7773 352 559
* Metametrics, International House, 107 Gloucester Road, Malmesbury, Wiltshire, SN16 0AJ
* www.metametrics.co.uk<http://www.metametrics.co.uk/>

________________________________
From: pgsql-sql-owner(at)postgresql(dot)org <pgsql-sql-owner(at)postgresql(dot)org> on behalf of Andreas Joseph Krogh <andreas(at)visena(dot)com>
Sent: 18 March 2015 13:01
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Effective query for listing flags in use by messages in a folder

På onsdag 18. mars 2015 kl. 13:54:50, skrev Hector Vass <hector(dot)vass(at)metametrics(dot)co(dot)uk<mailto:hector(dot)vass(at)metametrics(dot)co(dot)uk>>:

OK I get it ..

drop type if exists myflags cascade;
create type myflags as enum('is_seen','is_replied','is_forwarded','is_deleted','is_draft','is_flagged');
drop table if exists message;
create table message(
folder_id integer not NULL,
msg varchar(200),
is_flag myflags
);
insert into message values
(1,'msg b','is_seen'),
(1,'msg c','is_seen'),
(1,'msg d','is_seen'),
(1,'msg d','is_replied'),
(1,'msg e','is_seen'),
(1,'msg e','is_replied'),
(1,'msg h','is_deleted')
;

Not quite; you have 2 entries, one for each flag, for "msg d". I must have one tuple per message in this table.

--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com<mailto:andreas(at)visena(dot)com>
www.visena.com<https://www.visena.com>
[cid:part_F764646621869HEPVJS]<https://www.visena.com>