Custom Query Builder Returning Error MySQL/PHP

Wappler General Bugs
#1

Wappler Version : 3.6.1
Operating System : W10
Server Model: PHP
Database Type: MySQL
Hosting Type: Linux/shared

Expected behavior

select ifnull(max(updated_at), '1970-01-01T00:00:00') 'last_updated', ifnull(max(created_at), '1970-01-01T00:00:00') 'last_created' from shop_products;

The table in above query is blank. It has 0 rows. Running it returns:
image

Actual behavior

When running the same in Wappler’s custom query builder:

image
image717×447 17.6 KB

How to reproduce

Have no idea how you will reproduce this on your end.
There are 2 tables which have these two columns - created_at & updated_at.
Both columns are of type varchar(30) and store a date time value with time zone like 2020-12-29T10:51:21+01:00.
I get the same output as above for both tables.
Can give remote to check the issue if you guys are unable to reproduce this at your end.

Wappler 3.7.0 Released
#2

Tried running the server action with the query and received this:

{
  "code": 0,
  "file": "\/home\/slashash\/domain.com\/dmxConnectLib\/modules\/dbupdater.php",
  "line": 126,
  "message": "Undefined offset: 0",
  "trace": "#0 \/home\/slashash\/domain.com\/dmxConnectLib\/modules\/dbupdater.php(126): exception_error_handler(8, 'Undefined offse...', '\/home\/slashash\/...', 126, Array)\n#1 [internal function]: modules\\dbupdater->modules\\{closure}(Array)\n#2 \/home\/slashash\/domain.com\/dmxConnectLib\/modules\/dbupdater.php(130): preg_replace_callback('\/((?<=[^:])[:@]...', Object(Closure), 'select ifnull(m...')\n#3 \/home\/slashash\/domain.com\/dmxConnectLib\/lib\/App.php(193): modules\\dbupdater->custom(Object(stdClass), 'queryLastProduc...')\n#4 \/home\/slashash\/domain.com\/dmxConnectLib\/lib\/App.php(157): lib\\App->execSteps(Object(stdClass))\n#5 \/home\/slashash\/domain.com\/dmxConnectLib\/lib\/App.php(127): lib\\App->execSteps(Array)\n#6 \/home\/slashash\/domain.com\/dmxConnectLib\/lib\/App.php(116): lib\\App->exec(Object(stdClass), false)\n#7 \/home\/slashash\/domain.com\/dmxConnectLib\/lib\/App.php(94): lib\\App->exec(Object(stdClass))\n#8 \/home\/slashash\/domain.com\/dmxConnect\/api\/Sync\/Shopify\/products.php(8): lib\\App->define(Object(stdClass))\n#9 {main}"
}
#3

Could you try COALESCE() rather than ifnull?

SELECT 
COALESCE(MAX(updated_at),'2000-01-01T00:00:00') as 'last_updated',
COALESCE(MAX(created_at),'2000-01-01T00:00:00') as 'last_created'
from shop_products;
#4

Same error with COALESCE.

#5

Are you looking to get the single last create/update for any product or a list for each product?

#6

An alternative option is creating a View in the DB which becomes the source of the query - you wouldn’t even need it to be a custom query then as the View would do the MAX/ifnull bits

#7

I have used Views numerous times when I need some complex query to be used in single/multiple/pages query step.
The purpose of custom query step is that I don’t have to create view for a single line of SQL which is not even complex.
Creating a view is an overkill and fixing the bug would be a better approach.

I have found a workaround for now in my server action & logic, but still want this to be fixed. Custom query should work for any query that works on the DB directly.

#8

Agreed, fixing the native custom query would be ideal.

I think that the query builder, that allows queries to be written for many Database types, can’t cope for aggregate functions when no records at all exist. I bet if you saved the custom query and ran it anyway it would work (you could put a single temporary row in the table to test and then remove it once the schema had been pulled).

Glad you have something working for now, though

#9

I tested this too right now. Its still not working. So table being empty or not is not the cause for this issue.

#10

I tried the Custom Query builder too - got an error when running the query within the editor but manually set the schema and saved:

image
image1474×854 44.2 KB

I output the result on the page:

image
image1030×110 5.99 KB

#11

Unfortunately, for me its not returning any value when running in browser.
You trying this on MySQL?
Also, maybe try to put a random datetime instead of Not Found?

#12

It is the datetime that’s causing it - set it to just a date and it works, set to datetime and it fails

@george, there seems to be an issue handling strings that are formatted like datetime in at least MySQL.

1 Like
#13

Well it depended on the database field you are querying- it has to be the same type otherwise the database server will give you an error.

So just format the field first.

#14

As explained in the original post, the field is a varchar(30). And the database server is NOT giving any error. The query is working fine when run on the server directly, but its Wappler that is unable to process it.

#15
#16

hello @sid
can you try adding limit 1 at the end of this query.

“Undefined offset” warning pops up when trying to access a nonexistent array element.

$ i [0] = 5;
echo $ i [1];

In the code above, only the zero element of the array has been defined and the value assigned, but when we want to access the element in the 1st index, we get an Undefined offset warning.

#17

Same error with limit 1.

#18

I think that I see the problem, please test the following update. Unzip file in dmxConnectLib/modules.

dbupdater.zip (1.2 KB)

#19

Don’t see the error anymore. Results are populating as expected. Thanks Patrick. :slight_smile:

1 Like
#20