Query returns different results in wappler vs sqlite db manager app

[stinknox]

Built a query in wappler for sqlite db that has two tables. Products and categories. Products has a category field and the query ties them to together on a left join. Below is a screenshot from wappler query result vs Db Browser. DB Browser returns what I would expect, wappler does not with results that have blank fields that are not blank in the DB.

Query:

select `products`.`id`, `products`.`name`, `products`.`descrip`, `products`.`category`, `products`.`modifier`, `products`.`price`, `categories`.`id`, `categories`.`name`, `categories`.`price`, `categories`.`descrip` from `products` left join `categories` on `categories`.`id` = `products`.`id` order by `products`.`category` ASC, `products`.`name` ASC

Wappler result:

DB Browser result:

[Teodor]

Have you checked the results in the browser by running the server action?

[stinknox]

thats how i noticed the issue.

[stinknox]

i listed another issue about oddities with your db manager. it essentially breaks after adding records for a while. videos included.

Database manager sqlite add record error

Using the gui db manager to add record to a table in database manager. It was fine until i got to around 40 records. After adding a few records I notice i’ll click the Add new button and it will add a row that I can’t type in. This error then happens hitting “Add New” again: TypeError: Cannot read properties of undefined (reading 'keyCode') at w2grid.editField (file:///usr/share/wappler/resources/app/Shared/DMXzone/dmxAppCreator/UI/w2ui/w2ui.min.js:11:102405) at w2grid.onAdd (file:///usr/share/…

[stinknox]

this definitely a wappler problem. changed to mariadb docker, recreated the tables. recreated the api action with query. same result. however running the same query in mysql cli returns the correct data. see screen shot.

[stinknox]

My join was incorrect but it still doesn’t explain why the two different results between wappler and the db browser app. I have my query working as expected but the behavior was odd.

[patrick]

I don't think the result is wrong, the problem I see is that you output columns with the same name from both tables. The DB Browser app probably shows those duplicate folders while in Wappler when a column has the same name it overwrites the previous one. Small update to the query should fix it:

select `products`.`id`, `products`.`name`, `products`.`descrip`, `products`.`category`, `products`.`modifier`, `products`.`price`, `categories`.`id` as categoryId, `categories`.`name` as categoryName, `categories`.`price` as categoryPrice, `categories`.`descrip` as categoryDescrip from `products` left join `categories` on `categories`.`id` = `products`.`id` order by `products`.`category` ASC, `products`.`name` ASC