Only Select queries are allowed

Help me!
1

:warning:This is a template you must use to report issues. :warning:
You can also drag images, videos and include Preformatted text.

Feature(s) impacted

Dasbhoard

Observed behavior

Can’t run sql query with using WITH as ( select * from XXX) select * from XXX;

Expected behavior

To be working because it’s SQL standard

Failure Logs

Only SELECT queries are allowed.

Context

Please provide in this mandatory section, the relevant information about your configuration:

  • Project name: …
  • Team name: …
  • Environment name: Production
  • Agent type & version:
  • Recent changes made on your end if any: …
2

Hi @Bastien_Blanc !
Can I ask more about your use case ? Why do you want to use this syntax ?
I’ll push your request to our product team !

3

Hi @anon94532230 ,
I want to use this request because it’s a more efficient way to aggregate data and to order it on the way we want. I don’t understand why it’s not working because it’s a select at the end.

Bastien

4

@Bastien_Blanc can you share the complete query you are trying to use, and the type of chart you are trying to setup ?

5

@anon94532230 I send you a private message

6

Hi @Bastien_Blanc,

Can’t you create a subquery instead of a WITH ? Or use a view ?

7

Hi @vince

No I can’t

8

Hi, I have the same request.

I used an unoptimized query (that takes 15 seconds to run):

SELECT u.id FROM "User" u
JOIN "Typebot" t ON u.id = t."ownerId"
WHERE (SELECT count(*) FROM "Result" r WHERE r."typebotId" = t.id LIMIT 30) >= 30

And I’ve optimized it like so:

WITH result_count AS (
SELECT r."typebotId", count(*) FROM "Result" r GROUP BY r."typebotId"
)
SELECT u.id FROM "User" u
JOIN "Typebot" t ON u.id = t."ownerId"
JOIN result_count r ON r."typebotId" = t.id

But it fails on Forest Admin

9

I’ve created a view:

CREATE VIEW active_users AS
WITH result_count AS (
SELECT r."typebotId", count(*) FROM "Result" r GROUP BY r."typebotId"
)
SELECT u.id FROM "User" u
JOIN "Typebot" t ON u.id = t."ownerId"
JOIN result_count r ON r."typebotId" = t.id

Then add it to Forest Admin:

SELECT id FROM active_users

(This query works well when testing manually)

It still throws an error:

Object { isAdapterError: true, stack: "i@https://app.forestadmin.com/assets/vendor-aced6f74adaa0feb5a4c7819c3db2832.js:149:229579\nn@https://app.forestadmin.com/assets/vendor-aced6f74adaa0feb5a4c7819c3db2832.js:149:230337\nhandleResponse@https://app.forestadmin.com/assets/vendor-aced6f74adaa0feb5a4c7819c3db2832.js:149:236468\nhandleResponse@https://app.forestadmin.com/assets/client-f86a9449e70706f242d0470e24f0ef7f.js:1:4020776\nx@https://app.forestadmin.com/assets/vendor-aced6f74adaa0feb5a4c7819c3db2832.js:149:241063\najax/</u.error/s<@https://app.forestadmin.com/assets/vendor-aced6f74adaa0feb5a4c7819c3db2832.js:149:237741\najax/</u.error@https://app.forestadmin.com/assets/vendor-aced6f74adaa0feb5a4c7819c3db2832.js:149:237752\nl@https://app.forestadmin.com/assets/vendor-aced6f74adaa0feb5a4c7819c3db2832.js:48:24786\nfireWith@https://app.forestadmin.com/assets/vendor-aced6f74adaa0feb5a4c7819c3db2832.js:48:25534\nk@https://app.forestadmin.com/assets/vendor-aced6f74adaa0feb5a4c7819c3db2832.js:48:76509\nsend/n/<@https://app.forestadmin.com/assets/vendor-aced6f74adaa0feb5a4c7819c3db2832.js:48:78952\ni@https://app.forestadmin.com/assets/chunk.8.ba9810089db8238eff28.js:1:45893\n", description: undefined, fileName: "https://app.forestadmin.com/assets/vendor-aced6f74adaa0feb5a4c7819c3db2832.js", lineNumber: 149, message: "The adapter rejected the commit because it was invalid", name: "Error", number: undefined, errors: (1) […] }
chunk.8.ba9810089db8238eff28.js:1:47538
10

Sorry to up this issue, I really really need to make this work. Is this a known issue or am I doing something wrong?

If you confirm that this is an issue, I would be ready to create a PR for this.

11

Hi @baptisteArno :wave: Sorry for the late response…
Where do you use these query? Inside a dashboard chart? If yes what is the chart type?
Can you give me the exact error please?
The log is not relevant.
Can you share with us the anonymized payload response of the request?

16

Hey! Sorry I didn’t update the thread. It was indeed an issue from my end. You can totally execute a query from a view on Forest Admin :slight_smile:

1 Like