Date / Data Filter help needed

htatd · October 18, 2021, 1:38pm

Hi.

I’m trying to filter data by dates dynamically. It displays how it should in preview, but when loading in browser there is an error which doesn’t allow the page to load even.

In the database query I created a condition that it should match dates between the two GET values:

The two GET values are connected to two variables

In the variables I have only static values for now:

It works perfectly in the preview:

When loading the page in the browser, it gives this error message on the browser:

{"status":"500","message":"Undefined binding(s) detected when compiling SELECT. Undefined column(s): [sales.DateDay] query: select \"DateDay\" as \"x\", count(\"DateDay\") as \"y\" from \"sales\" where \"sales\".\"DateDay\" between ? and ? group by \"DateDay\"","stack":"Error: Undefined binding(s) detected when compiling SELECT. Undefined column(s): [sales.DateDay] query: select \"DateDay\" as \"x\", count(\"DateDay\") as \"y\" from \"sales\" where \"sales\".\"DateDay\" between ? and ? group by \"DateDay\"\n at QueryCompiler_PG.toSQL (/opt/node_app/node_modules/knex/lib/query/compiler.js:98:13)\n at Builder.toSQL (/opt/node_app/node_modules/knex/lib/query/builder.js:97:44)\n at /opt/node_app/node_modules/knex/lib/runner.js:31:36\n at /opt/node_app/node_modules/knex/lib/runner.js:277:24\n at async App._exec (/opt/node_app/lib/core/app.js:449:30)\n at async App._exec (/opt/node_app/lib/core/app.js:420:17)\n at async App.exec (/opt/node_app/lib/core/app.js:389:9)\n at async App.define (/opt/node_app/lib/core/app.js:379:9)"}

When using static values in the conditions all works perfectly, so the query is right for sure. So I guess I’m making a mistake with the binding?

Does anyone have an idea what I’m doing wrong? I tried to use set value and other stuff in the server connect but nothing works. I keep getting this error.

sid · October 7, 2021, 1:16am

The query generated by the builder seems wrong.
It should be “sales.DateDay” in the select columns list I think.
Maybe try to create another query step with just a few columns selected and no aggregate functions or conditions. Compare that with the one you have above if it works.

Not sure why it would work in preview but not in browser, because preview itself is pretty much the browser itself.

htatd · October 7, 2021, 1:40am

Thank you for your reply.
But why does it work when in the conditions I type the dates? I think it’s a problem in the binding of the variables.

sid · October 7, 2021, 3:15am

Oh. I missed your point number 6.
Maybe try printing the get values using a set value to be sure that values are being received in server action?

patrick · October 7, 2021, 7:56am

One or both expressions used for the parameters returned undefined, at least that is what the error is telling.

If you are values from user input it is best to also validate it and perhaps have a default value like $_GET.date_start.default('2021-01-01').

htatd · October 7, 2021, 3:20pm

Hi! I tried this too, it gave the same error…

htatd · October 7, 2021, 3:21pm

Hi, so at the moment it’s not from a user input, I just filled in the values already, it’s static. Somehow it doesn’t bind the value to the server connect query. Don’t exactly know what to do else

htatd · October 7, 2021, 6:34pm

I tried all without using grouping and aliases etc. The problem is that the the $_GET values are not binding when the page loads.

How does everyone work with filtering queries if this doesn’t work?

Hyperbytes · October 8, 2021, 11:03am

My gut feeling is that the dates you specify may be seen as strings so the comparison with the date field fails

Try defining the two dates using a Set value step with something like "2021-09-05’.toDate() to ensure they are dates and not strings then use the “Set Value” variables as your between conditions.
Remember $_GETs are passed as strings