Announcement

Collapse
No announcement yet.

SQL question

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL question

    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.
    There's no place like 127.0.0.1

  • #2
    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.
    Chuck
    秋音的爸爸

    Comment


    • #3
      Originally posted by cjolley View Post
      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'.

      Originally posted by cjolley View Post
      *such as ", status INTEGER default 'Not Done' not null)
      if SQLite supports default values and not null on columns.
      It does.
      There's no place like 127.0.0.1

      Comment


      • #4
        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.
        Last edited by cjolley; 23 April 2007, 04:27.
        Chuck
        秋音的爸爸

        Comment


        • #5
          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.
          Last edited by Lucid; 23 April 2007, 04:56.
          There's no place like 127.0.0.1

          Comment


          • #6
            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

            Code:
            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.
            Last edited by cjolley; 23 April 2007, 08:25. Reason: added "All Started"
            Chuck
            秋音的爸爸

            Comment


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

              Code:
              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
              There's no place like 127.0.0.1

              Comment


              • #8
                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.
                Last edited by cjolley; 23 April 2007, 09:35.
                Chuck
                秋音的爸爸

                Comment


                • #9
                  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
                  Last edited by Lucid; 23 April 2007, 09:47.
                  There's no place like 127.0.0.1

                  Comment


                  • #10
                    Originally posted by Lucid View Post

                    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
                    Chuck
                    秋音的爸爸

                    Comment

                    Working...
                    X