Leaderboard Chart not rendering (unauthorised access to table)

[Christopher_Bagard]

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

[Nicolas_Sailly]

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

[Christopher_Bagard]

Hi Nicolas,

Thank you for your fast answer. Here are the screenshots

[Nicolas_Sailly]

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

[Christopher_Bagard]

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.

[vince]

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

[Christopher_Bagard]

@vince

NODE_ENV='development'

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

[vince]

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

[bqst]

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

[Arnaud_Moncel]

Hi @bqst welcome to our community.
Hi @Christopher_Bagard 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

I will keep you update when the fix was implemented.

[Christopher_Bagard]

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

[Arnaud_Moncel]

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.

[Christopher_Bagard]

@Arnaud_Moncel

rest-express-sequelize >

awesome ! this works, thanks a lot !