Plugin not working - Githubissues

mpcref commented 3 months ago

I did everything according to the documentation using a very simple query but I keep getting this error:

Ht [Error]: Field 205 has no SQL option
    at /directus/node_modules/.pnpm/@premieroctet+directus-extension-sql-panel@0.4.4_@types+node@20.12.7_@unhead+vue@1.9.9_vue@3._z52xpt63kzhkvnshblnoyu2or4/node_modules/@premieroctet/directus-extension-sql-panel/dist/api.js:1:174914
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async /directus/node_modules/.pnpm/@premieroctet+directus-extension-sql-panel@0.4.4_@types+node@20.12.7_@unhead+vue@1.9.9_vue@3._z52xpt63kzhkvnshblnoyu2or4/node_modules/@premieroctet/directus-extension-sql-panel/dist/api.js:1:174692 {
  isClientSafe: true
}

I'm using Directus version 10.11.0.

quentingrchr commented 3 months ago

Hi, reproduced exactly your error on my Directus version 10.6.3. This error seems to happen when you save a "SQL Panel" field type in your data model without the required "SQL Query" field.

Could you check that no "SQL Panel" field was saved without a "SQL Query" by mistake?

mpcref commented 3 months ago

I'm not sure what you mean. This is how the field gets registered in the directus_fields table after adding it to the collection:

id	collection	field	special	interface	options	display	display_options	readonly	hidden	sort	width	translations	note	conditions	required	group	validation	validation_message
205	user	test	alias,no-data	interface-sql-panel	{"is_using_entity_id":true,"sql":"SELECT * FROM directus_files;"}	NULL	NULL	0	0	16	full	NULL	NULL	NULL	0	NULL	NULL	NULL

quentingrchr commented 3 months ago

This error occurs when the 'sql' property is not present in the field's options. To query the data of the field (in directus_fields table),` we use knex. If in the result of this knex query of the field we can't find the 'options.sql' property an error is thrown. https://github.com/premieroctet/directus-extension-sql-panel/blob/83439f85bfce9bb71a7f17ff74ed3f8c56080873/src/endpoints-sql-panel/utils.ts#L54

I would also suggest setting your "is_using_entity_id" to false since you seem not to be using it. This could cause an error (which is not related to the one in the issue but that you would have anyway).

mpcref commented 3 months ago

The sql property is right there in the options field. Changing is_using_entity_id has no effect.

want2know2 commented 2 months ago

Hi there, I'm experiencing the same issue. I maybe should share that I'm pretty much a noob to this whole field, I've set up Directus about five or six weeks ago without any experience in programming or relational databases or whatever, because I need something to create a database for a small project with. So chances are high, that I'm just doing it wrong.

Anyway, here's what's up:

What I'm running with:

Windows 10 + wsl 2 Ubuntu (WSL-Version: 2.1.5.0, Kernelversion: 5.15.146.1-2, WSLg-Version: 1.0.60, MSRDC-Version: 1.2.5105, Direct3D-Version: 1.611.1-81528511, DXCore-Version: 10.0.25131.1002-220531-1700.rs-onecore-base2-hyp, Windows-Version: 10.0.19045.4529)
Docker Desktop 4.30.0
Directus 10.10.5

In my data model I've already created collections like "Projekte", "Produkte" and such for testing purposes (stupid me to name them with uppercase letters).

What I did so far:

Installed the SQL panel extension (via npm install @premieroctet/directus-extension-sql-panel)
Created a new collection "testsql" in data model
Created a sql field "testtest" inside the collection "testsql"
Wrote a simple sql query into the sql panel sql query field
Went to the collection "testsql" in content mode
Created a new item
Got "unexpected error"

Here's the queries I tried plus the logs from Docker if that might help (also containing the Ht [Error]: Field 678 has no SQL option):

Query 1

current entity's id: activated

SELECT name FROM produkte;

Unexpected error

2024-06-19 19:51:53 directus-1  | [17:51:53] GET /notifications?limit=25&fields[]=id&fields[]=subject&fields[]=message&fields[]=collection&fields[]=item&fields[]=timestamp&sort[]=-timestamp&page=1&filter[_and][0][recipient][_eq]=a47f4868-719a-4340-8b6b-2bc4e64e48cc&filter[_and][1][status][_eq]=inbox 304 40ms
2024-06-19 19:51:53 directus-1  | [17:51:53] PATCH /users/me/track/page 204 36ms
2024-06-19 19:51:59 directus-1  | Ht [Error]: Field 678 has no SQL option
2024-06-19 19:51:59 directus-1  |     at /directus/node_modules/.pnpm/@premieroctet+directus-extension-sql-panel@0.4.4_@unhead+vue@1.8.10_pg@8.11.4_pinia@2.1.7/node_modules/@premieroctet/directus-extension-sql-panel/dist/api.js:1:174914
2024-06-19 19:51:59 directus-1  |     at async /directus/node_modules/.pnpm/@premieroctet+directus-extension-sql-panel@0.4.4_@unhead+vue@1.8.10_pg@8.11.4_pinia@2.1.7/node_modules/@premieroctet/directus-extension-sql-panel/dist/api.js:1:174692 {
2024-06-19 19:51:59 directus-1  |   isClientSafe: true
2024-06-19 19:51:59 directus-1  | }
2024-06-19 19:51:59 directus-1  | [17:51:59] POST /endpoints-sql-panel/678 400 22ms

Query 2

current entity's id: activated

SELECT Name FROM Produkte;

Unexpected error

2024-06-19 19:56:46 directus-1  | [17:56:46] GET /notifications?aggregate[countDistinct]=id 304 48ms
2024-06-19 19:56:46 directus-1  | [17:56:46] PATCH /users/me/track/page 204 27ms
2024-06-19 19:56:52 directus-1  | Ht [Error]: Field 678 has no SQL option
2024-06-19 19:56:52 directus-1  |     at /directus/node_modules/.pnpm/@premieroctet+directus-extension-sql-panel@0.4.4_@unhead+vue@1.8.10_pg@8.11.4_pinia@2.1.7/node_modules/@premieroctet/directus-extension-sql-panel/dist/api.js:1:174914
2024-06-19 19:56:52 directus-1  |     at async /directus/node_modules/.pnpm/@premieroctet+directus-extension-sql-panel@0.4.4_@unhead+vue@1.8.10_pg@8.11.4_pinia@2.1.7/node_modules/@premieroctet/directus-extension-sql-panel/dist/api.js:1:174692 {
2024-06-19 19:56:52 directus-1  |   isClientSafe: true
2024-06-19 19:56:52 directus-1  | }
2024-06-19 19:56:52 directus-1  | [17:56:52] POST /endpoints-sql-panel/678 400 14ms
2024-06-19 19:57:00 directus-1  | [17:57:00] POST /auth/refresh 200 67ms

Query 3

current entity's id: not activated

SELECT name FROM produkte;

Unexpected error

2024-06-19 19:59:11 directus-1  | Ht [Error]: Field 678 has no SQL option
2024-06-19 19:59:11 directus-1  |     at /directus/node_modules/.pnpm/@premieroctet+directus-extension-sql-panel@0.4.4_@unhead+vue@1.8.10_pg@8.11.4_pinia@2.1.7/node_modules/@premieroctet/directus-extension-sql-panel/dist/api.js:1:174914
2024-06-19 19:59:11 directus-1  |     at async /directus/node_modules/.pnpm/@premieroctet+directus-extension-sql-panel@0.4.4_@unhead+vue@1.8.10_pg@8.11.4_pinia@2.1.7/node_modules/@premieroctet/directus-extension-sql-panel/dist/api.js:1:174692 {
2024-06-19 19:59:11 directus-1  |   isClientSafe: true
2024-06-19 19:59:11 directus-1  | }
2024-06-19 19:59:11 directus-1  | [17:59:11] POST /endpoints-sql-panel/678 400 16ms
2024-06-19 19:59:19 directus-1  | [17:59:19] POST /auth/refresh 200 63ms

Query 4

current entity's id: not activated

SELECT Name FROM Produkte;

Unexpected error

2024-06-19 20:00:10 directus-1  | Ht [Error]: Field 678 has no SQL option
2024-06-19 20:00:10 directus-1  |     at /directus/node_modules/.pnpm/@premieroctet+directus-extension-sql-panel@0.4.4_@unhead+vue@1.8.10_pg@8.11.4_pinia@2.1.7/node_modules/@premieroctet/directus-extension-sql-panel/dist/api.js:1:174914
2024-06-19 20:00:10 directus-1  |     at async /directus/node_modules/.pnpm/@premieroctet+directus-extension-sql-panel@0.4.4_@unhead+vue@1.8.10_pg@8.11.4_pinia@2.1.7/node_modules/@premieroctet/directus-extension-sql-panel/dist/api.js:1:174692 {
2024-06-19 20:00:10 directus-1  |   isClientSafe: true
2024-06-19 20:00:10 directus-1  | }
2024-06-19 20:00:10 directus-1  | [18:00:10] POST /endpoints-sql-panel/678 400 14ms
2024-06-19 20:01:06 directus-1  | [18:01:05] POST /auth/refresh 200 62ms

If there is any way to make it run, I'd be more than glad - it seems to be the last thing that is still missing for me to be finally able to set up the database. O__o

premieroctet / directus-extension-sql-panel

Plugin not working #1

Query 1

Query 2

Query 3

Query 4