Radar chart query (M:N Problem)


(Labs) #1

Need some help here, guys.

I need to make a radar chart (chartjs) . My data structure is like this:

{
    tasks: [
      {
        uid: "0x1",
        label: "TaskOne",
        tags:[
            {__is_tags: true, __is_classificator: true, tag: "Cool"}, 
            {__is_tags: true, __is_classificator: true, tag: "Simple"}
        ],
        categories:[
            {__is_categories: true, __is_classificator: true, categ: "Code Red"}, 
            {__is_categories: true, __is_classificator: true, categ: "No charge"}
        ],
        status: [
            {__is_status: true, __is_classificator: true, status: "Ongoing"}
        ]
      },
      {
        uid: "0x2",
        label: "TaskTwo",
        tags:[
            {tag: "Boring"}, {tag: "Simple"}, {tag: "Customers"}
        ],
        categories:[
            {categ: "Bug"}, 
        ],
        status: [
            {status: "Onhold"}
        ]
      },
      etc...
] 

How to write a query that will fill the radar with tags as radar points (or labels) and status as datasets?

I mean:
Dataset 1 => “How many tasks are ‘Ongoing’ that are ‘Boring’, ‘Cool’, …”,
Dataset 2 => “How many tasks are ‘Onhold’ that are ‘Boring’, ‘Cool’, …”,

Any help, please?


(Michel Conrado (Support Engineer)) #2

Can you show what is the JSON format accepted by chartjs, in this case of yours. Mean the desired “Body”.


(Labs) #3

Yes, I need to provide something like that:

data: {
    labels: ['Boring', 'Cool', 'Simple', 'Customers', 'No Charge', 'Bug'],
    datasets: [
        {
            data: [20, 10, 4, 2, 1, 0]  // ongoing
        },
       {
            data: [3, 0, 21, 5, 0, 2]   // onhold
        },
    ]
}

There is a little bit of complication that I forgot: in this view, “tags” and “categories” need to be treated as one.

In my “schema”, tags, categories and status all have the predicate “__is_classificators” besides “__is_tags”, “__is_status” and “__is_categories” of each one.

Thanks


(Michel Conrado (Support Engineer)) #4

So, “Ongoing” is a unique node?

This seems to me


 status: [
            {__is_status: true, __is_classificator: true, status: "Ongoing"}
        ]

status --> status

Update:
This structure is not a Dgraph one. status cannot be string and the same time UID.


(Labs) #5

Sorry for this kind of mistake, I tried to make a simple scenario to facilitate.

You can safely change the predicates name to “description”, for example.

Thanks for your help.


(Michel Conrado (Support Engineer)) #6

We have some issues that I can not particularly solve based on what was presented.

First of all there’s no way the Dgraph counts each category and tags by name (strings). There is only “count” to UID and predicates. Secondly, there is no way to sort exactly the way you want (if sorting is important).

Third, this is too way complex why? LOL

I tried something below, but nothing guaranteed. It’s better query for tags and categories for later in application level or some Python of life, you arrange it the way you want.

But let’s continue to analyze the case
I’ll be here.

Actually I believe this query is not good for anything.

{
var(func: eq(status, "Onhold")) {
    ~ status {
    	OnholdVar as uid
    }  
  }

var(func: eq(status, "Ongoing")) {
    ~ status {
    	OngoingVar as uid
    }  
  }


  var (func: uid(OnholdVar))   {
    tags @filter( eq(tag, ['Boring', 'Cool', 'Simple', 'Customers', 'No Charge', 'Bug'])) {
         ~ tags {
    	OnholdVarT as uid
    }  
    } 
   categories @filter( eq(categ, ['Boring', 'Cool', 'Simple', 'Customers', 'No Charge', 'Bug'])) {
    	 ~ categories {
    	OnholdVarC as uid
    }  
    }  
 }


  var (func: uid(OngoingVar)) {
     tags @filter( eq(tag, ['Boring', 'Cool', 'Simple', 'Customers', 'No Charge', 'Bug'])) {
    	~ tags {
    	OngoingVarT as uid
    }  
    } 
   categories @filter( eq(categ, ['Boring', 'Cool', 'Simple', 'Customers', 'No Charge', 'Bug'])) {
    	~ categories {
    	OngoingVarC as uid
    }  
    }  
  }  

  ongoing(func: uid(OngoingVarT, OngoingVarC)) {
   uid
    count(uid)
  }
  ongoing(func: uid(OnholdVarT, OnholdVarC)) {
   uid
    count(uid)
  }

}

(Michel Conrado (Support Engineer)) #7

You could combine this with “uid_in”
https://docs.dgraph.io/query-language/#uid-in

{
A as var (func: eq(tag, 'Boring')) {
  ~tags {
   Boring as count(uid)
}
 ~categories {
   Boring2 as count(uid)
}
 totalboring as math(Boring + Boring2)
}

B as var (func: eq(tag, 'Cool')) {
  ~tags {
   Cool as count(uid)
}
 ~categories {
   Cool2 as count(uid)
}
totalCool as math(Cool + Cool2)
}

C as var (func: eq(tag,'Simple')) {
  ~tags {
   Simple as count(uid)
}
 ~categories {
   Simple2 as count(uid)
}
totalSimple as math(Simple + Simple2)
}

D as var (func: eq(tag,'Customers')) {
  ~tags {
   Customers as count(uid)
}
 ~categories {
  Customers2 as count(uid)
}
totalCustomers as math(Customers + Customers2)
}

E as var (func: eq(tag, 'No Charge')) {
  ~tags {
   Charge as count(uid)
}
 ~categories {
   Charge2 as count(uid)
}
totalCharge as math(Charge + Charge2)
}

F as var (func: eq(tag, 'Bug')) {
  ~tags {
   Bug as count(uid)
}
 ~categories {
   Bug2 as count(uid)
}
totalBug as math(Bug + Bug2)
}

 all_of_it(func: uid(A, B, C, D, E, F) {

  Boring : val(totalBoring)
  Cool : val(totalCool)
  Simple : val(totalSimple)
  Customers : val(totalCustomers)
  Charge : val(totalCharge)
  Bug : val(totalBug)

}

}

(Labs) #8

Thanks for you help, I’ll try it to get somewhere, but let me explain better.

  1. Scenario:
  • the user can create tasks;
  • a task can be in one status at time;
  • many tasks can be at many status at once;
  • the user can create categories;
  • the user can create tags;
  • the user can assign an arbitrary number of tags and / or categories to any task;
  1. Asked:
  • show me how many tasks per tag / category there are for each status on a given time;
    ** Obs: it is known that the same task will count more than once by being tagged with many tags, but it must count only once for a given tag;
  1. Remarks:
  • As you see, I do not know how many nor the names of the tags / categs because they are introduced by the user at runtime, so I cannot assume names on my queries;
  • The status are known at desenv time, but I do consider a bad practice to not assume them as dynamic as the tags are, so, I wouldn’t pick them by name either;
  • As far as complexity goes, I really think this is very common n:m:n situation and I must confess that I’m a little surprised that I couldn’t figure it out in a simpler way, as for me this is exactly the kind of flexibility (over a dozen SQL joins) that I was supposed to find in DGraph;
  1. Simplified schema: (Obs: __is_classifier is applied either on status / tags / categories nodes)
tasks           uid reverse
status          uid reverse
tags            uid reverse
categories      uid reverse
description     string [fulltext,term,trigram] 
__is_tasks      int
__is_status     int
__is_categories int
__is_tags       int
__is_classifier int
  1. Desired data format at the end:
    **Obs: this final format does not need necessarily be produced by DGraph, some level of post processing its ok. The problem right now is that I’m doing IT ALL by post processing.
data: {
    tags_categs: ['Boring', 'Cool', 'Simple', 'Customers', 'No Charge', 'Bug'],
    counts: [
        {
            data: [20, 10, 4, 2, 1, 0]  // status1
        },
       {
            data: [3, 0, 21, 5, 0, 2]   // statusN...
        },
    ]
}

Any further help will be happily accepted =]

Thanks for your help!