Creating a chart for a relationship with a join table

Help me!
1

Hi!
We’re trying to create a Leaderbord chart to show what content is mot popular within our app.

To calculate this we need to join some data together.
Basically we have:
Course --hasMany--> CourseVersion --hasMany--> CourseCompletion

What we want to do is to count the CourseCompletion objects grouped by Course and show them in a leaderboard with column headers:
“Course name | Course completion count”.

From what I gather the only possible relationships that we can use in the chart builder is the ones defined as “hasMany” in the “models/model-name.js”-file.
On top of that, I cannot find any way of using join tables for the relationships there, instead we’ve used routes to get it working.

So my question is, is there some way of creating this kind of a join query in the chart builder?

Best,
David

2

Hi @svensson-david,

Unfortunately with their default configuration, leaderboard charts are not meant to be used with several joins.

However, there is two workaround for this purpose:

  • The first one is the one you said you implemented, using routes and a code defined leaderboard, meaning you cannot edit it in the UI but you have no restrictions on what you are returning to populate your chart.
  • The second one consist of using the query definition directly in the UI. This allows you to define the SQL query to retrieve your data directly in the UI.

Here’s an example of a query charts counting the number of users on every projects.

Screenshot 2020-07-01 at 17.22.38
Screenshot 2020-07-01 at 17.22.38682×797 48.9 KB

It is to be noted that this second solution is only available in development environments for testing purpose or in production if you subscribed to our business plan.

1 Like
3

Hello @anon37102731!
Thanks for getting back to me.
I’ll try creating a custom route for it and see if I can get it working.

Thanks!

2 Likes