SQL question

[Lucid]

I've been working on this little mobile app that tracks tasks, whatever they may be.

The main view has a grid containing task lists, each of which have a title and a status indicator, for example:

TITLE STATUS
Tasks1 Done
Tasks2
Tasks3 All done

Selecting a task list shows all the tasks on that list one by one. The user can mark each task as Done (1) or Not done (0), or just move to the next one (leave as NULL).

If all tasks have been marked Done, the status on the grid is All done. However, if any task has been marked Not done, the status is (just) Done. If any task has been left unmarked, the status is empty.

I have two tables:
CREATE TABLE tasklists (id_list INTEGER, title TEXT)
CREATE TABLE tasks (id_list INTEGER, id_task INTEGER, description TEXT, status INTEGER)

Currently the grid is populated with "SELECT * FROM tasklists", then for each row I fetch the total number of tasks, number of tasks marked Done and number of tasks marked Not done, and finally update the status accordingly.

Well, it works, but it's not very elegant, especially if I'd want to show only some of the task lists (e.g. ones not marked All done).

So I'm wondering if there is a way to populate the grid with a single SQL statement. I've looked at CASE expressions, but haven't figured out how to handle the three-state status column..

Or maybe I should add a status column to the tasklists table, but then I'd need a neat way to update that..

Any tips? I'm using the SQLite provider, if it matters.

[cjolley]

you could
select l.id, l.title, max(t.status) list_status
from tasklists l, tasks t
where t.task_id = l.id
group by l.id, l.title


as long as you get rid of your nulls on the status (make sure it is always populated starting when a row is first created*) you can filter on whether the max is Done or Not Done.

this won't work well if each title has lots of tasks.


*such as ", status INTEGER default 'Not Done' not null)
if SQLite supports default values and not null on columns.

[Lucid]

as long as you get rid of your nulls on the status (make sure it is always populated starting when a row is first created*) you can filter on whether the max is Done or Not Done.

Thanks, but Max() doesn't quite cut it, because the logic is as follows:
Let's say I have a task list called 'My tasks' that consists of three tasks 'Do this', 'Do that' and 'Do something'.
When all three are marked Done -> My tasks are 'All done'.
When all three are marked either Done or Not done -> My tasks are 'Done'.
Otherwise the status shows empty.
I guess it is a bit confusing A tasks status is like an acknowledgement, and a list is completed ('Done' or 'All done') when all tasks have been acknowledged. However, the user may need to go back to lists marked as just 'Done'.

*such as ", status INTEGER default 'Not Done' not null)
if SQLite supports default values and not null on columns.

It does.

[cjolley]

the point is that the max() or min() (depending on what your flags are) will change when they all get done:

id1, 'not done'
id2, 'not done'
id3, 'not done'
----------------
max = 'not done'

id1, 'not done'
id2, 'not done'
id3, 'done'
----------------
max = 'not done'

id1, 'not done'
id2, 'done'
id3, 'done'
----------------
max = 'not done'

id1, 'done'
id2, 'done'
id3, 'done'
----------------
max = 'done'

then end the query with 'having max(status) like :my_filter

where my_filter is
'not done' for list unfinished
'done' for list complete
'%' for any (whatever the SQLite wild card is)



not that you'd use 'not done' and 'done'
I'd use a flag like 'Y' & 'N' and set the default not null value of the column to 'N'

a boolean would work too if you only have two states.
I'm used to Oracle which does not have boolean data type columns.

[Lucid]

The problem is that status needs to have three states. Both 'done' and 'not done' are set by the user. By default status should be empty or null.

As an example, a task could be "Pick up item x". If the user can't do the task for some reason, like "door is locked, nobody there, can't pick up the item", the he/she marks it 'Not done'. He/she could try again later and if successful, mark the task 'Done'.

The rule for the task list view again:
When all tasks (in a task list) are marked Done -> task list is 'All done'.
When all tasks (in a task list) are marked either Done or Not done -> task list is 'Done'.
Otherwise (that is, if even one task is neither) the status shows empty.

[cjolley]

same deal


update task_list set status = 0 where status is null;
alter table task_list modify(status default 0 not null);

0 = not started
1 = not done
2 = done

select l.id,
         l.title,
         case 
            when max(t.status) = 0 then 'Not Started'
            when max(t.status) = 1 and min(t.status) = 1 then 'All Started'
            when max(t.status) = 1 then 'Some Started'
            when max(t.status) = 2 and min(t.status) = 0 the 'Some Not Started and Some Done'
            when min(t.status) = 2 then 'All Done'
            when min(t.status) = 1 then 'All Started'
            else 'etc'
          end list_status
from tasklists l, tasks t
where t.task_id = l.id
group by l.id, l.title;

Just pick the logic and results you want.
You can also fold in count(distinct status) and stuff like that.
The alternative is to use triggers to maintain a summary status in the parent table, but this doesn't seem complicated enough to justify that.

N.B.
You really can't reliably do three (and greater) state logic if one of the states is null because null isn't anything.
It just won't act the same as the other statuses.
Things like "where status = null" don't make sense and WILL give you problems.

[Lucid]

Oh yes, that's it! Looks neat and works great. Thank you, Chuck!

select l.id_list id,
         l.title title,
         case min(t.status)
            when 2 then 'All done'
            when 1 then 'Done'
            else ''
          end status
from tasklists l, tasks t
where t.id_list = l.id_list
group by l.id_list

[cjolley]

You may still wind up having problems depending on what SQLite does with null regarding max, min & etc

what does max or min mean if a member is null?
null isn't anything. It's not large or small!


does SQLite support an NVL() function?
(replace nulls with a value)

eg:
that nvl( x, -1) = x (where x is not null)
and nvl(null, -1) = -1


if so then it a simple "case max(nvl(status, -1))" to avoid the problem.

[Lucid]

There is:

coalesce(X,Y,...) Return a copy of the first non-NULL argument. If all arguments are NULL then NULL is returned. There must be at least 2 arguments.

and

ifnull(X,Y) Return a copy of the first non-NULL argument. If both arguments are NULL then NULL is returned. This behaves the same as coalesce() above.

But tasks.status is now defined with not null and default 0, so that should be unnecessary. Then again, it's probably good practise

[cjolley]

But tasks.status is now defined with not null and default 0, so that should be unnecessary. Then again, it's probably good practise

If the column is not null with a default value then don't bother.
It would just make the code confusing to someone in the future trying to figure out why the heck the code checked for nulls on a not null column