Custom query having to add[0] to dynamic value to display it

Wappler General Bugs
,
1

Hi,
I have a custom MySQL query, to format a date field to Saturday, 6th November, 2023 for example.
There reason why I am using MySQL is that apparently its easier to get the day suffix (like th, st, nd etc)

After I create the custom query which has an alias ‘event_date_format’ it will have an empty value when I try to display it on the app side dynamic value picker.

Its only when I add a ‘set value’ on the server action and set a value from the mysql query output and I manually add “[0]” to the value. When I use the dynamic data picker in the Set Value Properties page on the server action it doesn’t include a [0] and it won’t display anything on the app page.

This will then display on the page.

Is there a reason for this?

Screen Shot 2023-11-02 at 3.19.00 pm
Screen Shot 2023-11-02 at 3.19.00 pm3250×2246 534 KB
Screen Shot 2023-11-02 at 3.16.32 pm
Screen Shot 2023-11-02 at 3.16.32 pm3150×466 203 KB
Screen Shot 2023-11-02 at 3.15.20 pm
Screen Shot 2023-11-02 at 3.15.20 pm3210×1696 440 KB
!

1 Like
2

Custom queries return an array… So even if it returns only 1 record you have to pull it with [0]

2 Likes
3

Here @George, the user gave reproducible instructions even if this is not an intentional bug report

@cpuser in this case, the right option is using Database Single Query instead of Database Query, though it should’ve added [0] automatically anyway

1 Like
4

I had a production issue beacuse of this just yesterday.

Had to manually fix around 60 entries in the DB because of the missed zero index from custom query in picker.

Didn’t think much of it, but seeing the reports, it looks like a serious bug.

2 Likes
5

Topic moved to bug reports, concerning the incorrect variable type identification

1 Like
6

Thanks for explaining @famousmag. The issue here as mentioned in my post is that Wappler isn’t generating the correct code and placing the [0], we are having to go in and edit it manually which defeats the purpose.

Thanks @Apple and @sid for flagging this and moving it to bugs.

Loving Wappler and how awesome it’s is and love the community support.

3 Likes
7

If you use a database single query, then there is no need for [0] as it is only one record. That is why it isn’t added.

8

@George I am using a Custom query and it only returns a single record, but I still have to manually enter the [0] for it to work.

9

I think that we all ask for the custom query to behave as a multiple records and not like a single record query by default…

11

@George This is still an issue in 6.4.1
Not sure if it has been fixed in 6.5.x

12

Well actually custom queries just as regular queries always return an array of records. So that is why the index.

Only single query returns a single record.

We don't have a custom queries that return a single record only - well even if they do - it will always be an array with one record inside.

if you have a special sql that always return just one query maybe we should make a separate action for it, single record custom query ....

13

Sorry, I commented on an incorrect post.
I completely understand the reason for needing to add the index. I don't thing a single custom query would make much sense, given low use case. But not a bad idea.

The problem I actually have right now, is that it does not actually add this index, when selecting from data picker.
I think there is another post in the community about it. Its a bug.

14

I remember, before single queries, dealing with this all the time.
The easiest way was to use a repeat action (even though there is only 1 record) then the value can be referenced without the [0]

1 Like
15

I also think using a repeat action is the safest way to handle the database results. What happens when it didn't return 1 result, you can add LIMIT 1 to the query to prevent more records, but what when there is 0 results? With a repeat action it will just skip when there are no records and it also handles single or multiple records nicely.

1 Like
16

Yeah.. I see and my apologies for that statement!
I was obviously talking for the time I joined Wappler community...
Well, now we have 4 type of queries:

As I see it the only one of them restricted with limit 1 is the first one, the Single query.
All the others are fetching all the records matching the search criteria.

So I suppose we talk about a fifth type of query... A "single custom query"

1 Like
17

It seems there is some confusion here.
@George This post is about custom query NOT adding [0] index when selecting from data picker.
Which it has been doing since the beginning - irrespective of whether the output of custom query is one or multiple rows.

But in Nov 23, this broke with some update, and until 6.4.1, which I am on right now, its still a problem.
The custom query steps requires [0] to be added if someone tries to select it directly in action picker.
Hope this makes the problem here, clearer.
CC: @patrick

An additional single custom query would be welcomed, but issue with existing custom query needs to be fixed before that.

18

Ooh definitely a confusion, i thought it was the other way around. Will investigate

1 Like
19

Ping here.
Spent hour trying to understand:

  1. Why output is Array, even though it's a single record output
  2. Why Wappler doesn't understand that output is Array and doesn't add [0] when selecting Query Output.

Is should be fixed one way or another.

20

Because the database server returns an "Array" of rows, even if it's just one row. Mutating the data structure to a single record would require an additional option to perform such to be implemented.

Unknown, bug