Less Code TODO
This is the second in series of of Less Code Apps with Cloud SFSQL. Also in case you don't know I am the NON technical CO-founder of Cloud SFSQL yep that guy. I go by other names but suffice it say I am not a programmer. I know a few things but to put this into perspective I just found out about a switch statement an hour ago. So mistakes will be made, but the point being if I can creating a not so simple TODO app, Cloud SFSQL in the hands of you, a more proficient individual, can become a powerful tool.
This started out as a simple example of a to do list, there is no simple example of a ToDo List, once you get started you can't stop. So with that said lets get coding our “Less Code” SFSQL TODO.
Please take notice that I have not created any data structures or schemas and have not had to worry about documents or collections to hold documents. That's not saying there is no structure to the data, all data has a structure. With SFSQL data and its structure flows from the code we call it Radical Data Structures. So lets get this TODO list build
First lets create a todo. Here is the create todo modify command. This same modify command will be used for all TODOS created. As you can see it has both data and structure.
[
{
"modify": {
"data": {
"TODOS": {
"todo": [
{
"#append": {}
},
{
"title": "'.$title.'",
"status": "Open",
"comment":""
}
]
}
}
}
}
]This creates the basic initial data structure if it is not present and appends a todo object to the todo array. We can always add more primitive attributes to the todo objects later.
Each todo may have 1 or more tasks, here is the task modify command.
[
{
"modify": {
"data": {
"TODOS": {
"todo": [
{
"#append": {}
},
{
"__todooid": "!@result.oid@!",
"title": "'.$title.'",
"status": "Open",
"comment":""
}
]
}
}
}
},
{
"modify": {
"data": {
"o:.todo": {
"#update": {
"where": "$o:.todo.oid()='.$todo_id.'"
},
"status": "Open",
"task": [
{
"#append": {}
},
{
"#ref": "SELECT $o:.todo.oid() WHERE $o:.todo.oid()=!@__todooid@!"
}
]
}
}
}
}
]A task is also a todo. When a task is created, a todo object is created and the create task object is appended to the the task array of the task's parent todo. The appended task object is a reference to the todo object created. The oid of the created todo object is assigned to the !@__todooid@!variable and used in the "#ref" sub command.
Now we need to query the todos and tasks. We could have started with the query first but I find it easier to start with the creation first its just a matter of preference
[
{
"query": {
"sfsql": "SELECT $o:TODOS.todo.oid() as todo_id,$s:TODOS.todo.title as title,$s:TODOS.todo.status as status WHERE $s:TODOS.todo.status <> 'HIDDEN' '.$andstatus.$andtodo.' ORDER BY 'title' DESC"
}
}
]
***************************** EXAMPLE RESULT SET *********************
[
{
"data" : [
{
"todo_id" : 58,
"title" : "Build Fence",
"status" : "Open"
},
{
"todo_id" : 59,
"title" : "Build Shed",
"status" : "Open"
},
{
"todo_id" : 60,
"title" : "Clean House",
"status" : "Open"
}, ..................... And here is the get task query, Tasks can conditionally be filtered by the their parent TODO AND $o:.task.poid()='.$todooid
[
{
"query": {
"sfsql": "SELECT DISTINCT $o:.task.oid() as todo_id,$s:.task.title as title,$s:.task.status as status WHERE $s:.task.status <> 'HIDDEN''.$where.' ORDER BY 'todo_id' DESC"
}
}
]
***************************** Example Result Set *********************
[
{
"data" : [
{
"todo_id" : 61,
"title" : "Get Cleaning Supplies",
"status" : "Done"
},
{
"todo_id" : 62,
"title" : "Get Oustide Supplies",
"status" : "Done"
},
{
"todo_id" : 63,
"title" : "Get Cleaning Clothes",
"status" : "Done"
},
{
"todo_id" : 64,
"title" : "Get Power Washer",
"status" : "Done"
},................................We need a way to filter the TODOs to determine if they are TODOs or tasks. This is done with a simple “isTask ” query. The TODO query will return all todos even ones that are tasks, we check to see if the todos returned by the todo query are also tasks with this query, if it is not then it is displayed in the main TODO section.
[
{
"query": {
"sfsql": "SELECT $o:.task.oid() as oid, $s:.task.status as status WHERE $o:.task.oid()='.$oid.'"
}
}
]Now lets take a look at the main TODO screen, I went ahead and added some todos and tasks. We see the list of our TODOs and Tasks.
There is more going on in this interface. Because we have many parents TODOs to many TASK child relationships, interesting structures are created, as we start adding tasks and sub tasks.. Lets see what the current structure of our data looks like with the show attributes query.
[
{
"showattrs": {}
}
]
***************RESULTS*******************
[
{
"data" : [
"o:TODOS",
"o:TODOS.todo",
"s:TODOS.todo.comment",
"s:TODOS.todo.status",
"o:TODOS.todo.task",
"s:TODOS.todo.task.comment",
"s:TODOS.todo.task.status",
"o:TODOS.todo.task.task",
"s:TODOS.todo.task.task.comment",
"s:TODOS.todo.task.task.status",
"o:TODOS.todo.task.task.task",
"s:TODOS.todo.task.task.task.comment",
"s:TODOS.todo.task.task.task.status",
"s:TODOS.todo.task.task.task.title",
"s:TODOS.todo.task.task.title",
"s:TODOS.todo.task.title",
"s:TODOS.todo.title"
],
"success" : 1,
"cmdname" : "showattrs"
}
]Things are starting to get nested as shown by the structure. Cloud SFSQL is schema-less, so not all TODOs are deeply nested as shown but at least 1 TODO has three sub task, and TODOs may contain the same sub tasks. You may be wondering what about a recursive relationship. Yep that is possible and SFSQL does allow for it, we just need to handle it in the UI. The best way to handle it is not to allow for child tasks to be added that will cause a circular relationship.
First off is lets display the parents TODOs of each task. These are TODOs that have the task as as a first level child.
[
{
"query": {
"sfsql": "SELECT $o:.todo.oid() as todo_id, $s:.todo.title as title WHERE $s:.todo.status <> 'HIDDEN' AND $o:.todo.task.oid() = '.$oid.' ORDER BY title DESC"
}
}
]Now lets make this interface interesting and display all the counts of the immediate sub tasks for each TODO and task. Tasks are TODOs also and may have sub tasks as well.
function getTaskCounts($oid) {
$json=
'[
{
"query": {
"sfsql": "SELECT $o:.task.oid() as oid, $s:.task.status as status WHERE $s:.task.status <> 'HIDDEN' and $o:.task.poid()='.$oid.'"
}
}
]';
$tasks=runSFSQL($json,true);
$taskcount['total']=0;
$taskcount['open']=0;
$taskcount['done']=0;
if( !empty($tasks[0]['data']) ){
$taskcount['total']=count($tasks[0]['data']);
$taskvaluecount=array_count_values(array_column($tasks[0]['data'], 'status'));
$taskcount['open']= (!empty($taskvaluecount['Open']) ? $taskvaluecount['Open'] : 0) ;
$taskcount['done']= (!empty($taskvaluecount['Done']) ? $taskvaluecount['Done'] : 0) ;
}
return $taskcount;
}We pass the todo/task oid to this function and get pack all tasks that have this oid as a parent and then do some array wrangling, as I like to call it, and get back the the total, open and done sub tasks.
We also display the status of each todo and task, and optional display a green button that allows you to change the status of a todo and sub task. The change status button will only display if all sub tasks are done. This has a bubble up effect, as you start completing lower task green buttons start popping up. The counts function adjusts the counts each time a status is changed. Sorry it does not reverse itself. Changing a sub task status back to open will not revert the parent task previously market as competed to open. Lets not get crazy this is just a demo but yes it is possible and will probably be adding that feature by the time post is published.
Here is the status update command in case your interested.
[
{
"modify": {
"data": {
"o:.todo": {
"#update": {
"where": "$o:.todo.oid()='.$todo_id.'"
},
"status": "'.$status.'"
}
}
}
}
]Now on to the Detailed TODO/Task view
Here we see all the the Todo details, nested Tasks associated with the Todo(The Task Tree) and their counts, Parent Todos with counts, Children tasks with counts, the option to add a new task or select from an existing task, make updates to the todo, the ability to change the status of the todo.
We fire off three queries with one request to generate this UI
[
{
"query": {
"sfsql": "SELECT $s:.todo.title as 'title',$s:.todo.status as 'status',$$s:.todo.comment as 'comment' WHERE $s:.todo.status <> 'HIDDEN' and $o:.todo.oid()='.$todoid.'"
}
},
{
"query": {
"sfsql": "SELECT $o:.task.oid() as 'todo_id', $s:.task.title as 'title', $s:.task.status as 'status' WHERE $s:.task.status <> 'HIDDEN' and $o:.task.poid()='.$todoid.' ORDER BY 'todo_id' DESC"
}
},
{
"query": {
"sfsql": "SELECT DISTINCT $o:.todo.task.oid() as 'todo_id', $s:.todo.task.title as 'title' WHERE $s:.todo.task.status <> 'HIDDEN' and $o:.todo.task.oid() <> '.$todoid.' and $o:.todo.task.poid() <> '.$todoid.' '.$parentnotin.' ORDER BY 'todo_id' DESC"
}
}
]The first query gets the details of the current TODO where the oid equals the current TODO oid. The second query generates the child tasks directly under the TODO where the poid equals the current TODO oid. The third query is used to filter the drop down list of tasks that can be added to the current TODO. We do not want to include and tasks that has the current todo in its task tree, that will cause the TASK Tree UI to loop endlessly. SFSQL will catch this bur it still needs to be avoided.
Since the number of branches in the Task Tree is unknown a recursive function is used o generate the TASK Tree.
Here is the function which accepts an array of todo oids. The query finds the children task of each oid in the array. For each child task the oid, title and pos (position) are selected. I ended up not needing the position of the task. It loops thorough the results printing out the unordered list displayed in the TODO Task Tree UI along with each task's counts by calling the “taskcounts” function, it calls itself until all the task tree branches and are displayed. I am not going to lie it took me quite a few iteration to get this right, this was my first attempt at using a recursive function. In future releases of Cloud SFSQL we will give developers a better handle on accessing this type of structure, optionally returning the entire graph of the requested structure.
Function getbranchIds($todoid,$key){
foreach( $oid as $key => $todoid ) {
$json='[
{
"query": {
"sfsql": "SELECT $o:.task.oid() as toid, $o:.task.pos() as pos, $s:.task.title as title, $s:.task.status as status WHERE $o:.task.poid()='.$todoid.'"
}
}
]';
$query=runSFSQL($json,true);
$empty = [];
$toids=( empty($query[0]['data'][0]) ? $empty : array_column($query[0]['data'],'toid') );
$title=( empty($query[0]['data'][0]) ? $empty : array_column($query[0]['data'],'title') );
$status=( empty($query[0]['data'][0]) ? $empty : array_column($query[0]['data'],'status') );
$count=count($toids);
echo '';
}
return true;
} Ok now back to the drop down list. This ended up being more tricky than I expected. The main reason was because I was not able to get the recursive function to return what I was after. So i reverted to a client side JavaScript solution. First we get the results of that third todo query which return all the task less the current oid and getParentOids( todoss with the current task as a child)
where task.oid<>current todo.oid task.poid=<>current todo.oi and task.oi NOT in “getParentoid” GetParentOid query s below
[
{
"query": {
"sfsql": "SELECT $o:.todo.oid() as \'todo_id\', $s:.todo.title as \'title\' WHERE $s:.todo.status <> \'HIDDEN\' AND $o:.todo.task.oid() = '.$oid.' ORDER BY \'title\' DESC"
}
}
]We we write these into JavaScript variable “alltaskstasks” and then we call a modified version of task tree recursive function and write them into a JavaScript “childtasks”. Then we remove the values of “childtasks” from “alltaskstasks” and the result is the filtered task list drop down. In some cases this still does not filter all the task that can cause an infinite loop so we do a final check after the user submits a task to be added form the drop down. I know this is not idea but like I said I had issues with the recursive function. I woudl have preferred to properly filter the drop down list to avoid this, but your dealing with a NON Programmer here.
Here is the modify command that does the linking. It gets called after the final recursive function passes. If it does not aoss the user is prsented with a message.
[
{
"modify": {
"data": {
"o:.todo": {
"#update": {
"where": "$o:.todo.oid()='.$todo_id.'"
},
"status": "Open",
"task":[
{
"#append": {}
},
{
"#ref": "SELECT $o:.todo.oid() WHERE $s:.todo.title=\''.$link_to_title.'\'",
"status": "Open"
}
]
}
}
}
}
]