Leaderboard Chart not rendering (unauthorised access to table)

Help me!
1

Hello,
I’m fairly new to Forest
I am trying to render a leaderbord Chart on my forest admin Dashboard showing the Number of tasks created per User…

Expected behavior

Render a Leaderboard chart

Actual behavior

Forest Admin cannot render this chart, syntax error at or near “.” The other charts on the page render correctly and I am able to render LeaderBoards on other tables. (this seems to be due to an unauthorised access to the users table)

Failure Logs

When I look at the Chrome console, I get a 401 on this request “forest/stats/users” with the following error message "detail: “Forest cannot authenticate the user for this request.”

Context

“body-parser”: “1.19.0”,
“chalk”: “~1.1.3”,
“cookie-parser”: “1.4.4”,
“cors”: “2.8.5”,
“debug”: “~4.0.1”,
“dotenv”: “~6.1.0”,
“express”: “~4.16.3”,
“express-jwt”: “5.3.1”,
“forest-express”: “^7.4.0”,
“forest-express-sequelize”: “^6.0.0”,
“morgan”: “1.9.1”,
“require-all”: “^3.0.0”,
“sequelize”: “~5.15.1”,
“pg”: “~8.2.2”

Would you have any idea what might be causing this error ?

Thanks a lot for your help
Chris

2

Hello @Christopher_Bagard !
Can you share screenshots of your error logs (both in browser/console) and of your chart configuration ?

3

Hi Nicolas,

Thank you for your fast answer. Here are the screenshots

Capture d’écran 2020-12-03 à 11.19.32
Capture d’écran 2020-12-03 à 11.19.32974×580 51.8 KB
Capture d’écran 2020-12-03 à 11.21.11
Capture d’écran 2020-12-03 à 11.21.111438×278 60.1 KB
Capture d’écran 2020-12-03 à 11.23.16
Capture d’écran 2020-12-03 à 11.24.01
Capture d’écran 2020-12-03 à 11.24.01662×584 31.3 KB

4

Can you please share the query that is triggered on your liana ? You should have one in dev looking like the following:

image
image1380×384 17 KB

5

In dev, I directly get a Sequelize error

POST /forest/stats/users 500 103 - 786.491 ms [forest] 🌳🌳🌳 Unexpected error: syntax error at or near "." SequelizeDatabaseError: syntax error at or near "."

I am not sure where I can find for the raw sequel query.

1 Like
6

set NODE_ENV='development' in your .env file and you should be able to see the raw query :wink:

7

Thanks got it !

SELECT COUNT("tasks"."id") as "value", user.lastname as "key"
FROM "tasks"
INNER JOIN "users" AS "user"
        ON "user"."id" = "tasks"."user_id"
    
GROUP BY user.lastname
ORDER BY "value" DESC
LIMIT 20
1 Like
8

Hmm and if you replace GROUP BY user.lastname by GROUP BY key does it work :thinking:?

9

Same problem here, it seems that there was a problem with the auto-generated SQL query from leaderboard chart:

SELECT COUNT("tasks"."id") as "value", user.email as "key"
FROM "tasks"
INNER JOIN "users" AS "user"
ON "user"."id" = "tasks"."user_id"
GROUP BY user.email
ORDER BY "value" DESC
LIMIT 10

Fixed by adding the missing quotes around user.email and by manually filling in the “QUERY” field.

SELECT COUNT("tasks"."id") as "value", "user"."email" as "key"
FROM "tasks"
INNER JOIN "users" AS "user"
ON "user"."id" = "tasks"."user_id"
GROUP BY "user"."email"
ORDER BY "value" DESC
LIMIT 10
10

Hi @bqst :wave: welcome to our community.
Hi @Christopher_Bagard :wave: I reproduced your issue, the problems here is user is a reserved word on some DBMS. To access your database we must use quotes.
So I will open a bug report on our side to fix this issue.

I’m happy to see you found a workaround :+1:

I will keep you update when the fix was implemented.

2 Likes
11

Hey,
thanks all (@Arnaud_Moncel, @bqst, @bqst, @vince) for your prompts answers and the workound ! Will try it it out

1 Like
12

Hi guys (@bqst, @Christopher_Bagard) a fix has been released, make sure you installed the forest-express-sequelize >= 6.5.1.
Let me know if the issue persist.

1 Like
13

awesome ! this works, thanks a lot !

2 Likes