Lookup with advanced SQL

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 298
Joined: 2019-05-21 22:42
Contact:

Lookup with advanced SQL

Post by onoehring » 2019-07-14 17:09

Hi,

as it took me quite long so solve the problem - and I did not solve it myself, but pbötcher did give me the relevant hint. This, I want to share with you.

I used a custom query in AppGini to select some value from a lookup (dropdown) field
e17_2.png
e17_2.png (10.73 KiB) Viewed 194 times
The query ran fine in phpmyadmin/adminer so I was sure everything would be fine in the generated application as well.But it wasn't.

Several things happened or did not happen:
  • Dropdown does not load more entries when you scroll down (says loading more results, but nothing happens)
  • Search (ajax) in dropdown does not work, spinner keeps spinning or
  • Nothing in the dropdown at all
Any of these problems can occur independent of the others as it seems.

Let's just say: Behavior of the lookup was not working as expected.

My SQL code was a SELECT with a sub-SELECT. This sub-SELECT had a GROUP BY and an ORDER attached to it.

The tip from pbötcher ( viewtopic.php?f=2&t=3086&p=10551#p10546 ) was easy

Solution part I
in case you want to use the custom SQL, you need to make sure, that you do not use GROUP BY or ORDER BY within the query.
Solution part II
Another tip I got from him before is
your SQL must be in one line. It must not contain linebreaks
I solved my problem, by creating a view in the database from the sub-select SQL. Then I replaced the sub-select with something like

Code: Select all

SELECT * FROM view_name
to get the result I wanted.

The linebreaks are easy to remove. I used Notepad++, searched my structured and layouted SQL code for \r\n and replaced it with the space character (spacebar once). To get the code even cleaner, I repeated this and replaced double spaces with single spaces until no double spaces were left.
np.png
np.png (20.48 KiB) Viewed 194 times
Maybe this information should be mentioned in the docs around page 15 in the "Understanding lookup fields" section.

Maybe this helps others.
Olaf

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 298
Joined: 2019-05-21 22:42
Contact:

Re: Lookup with advanced SQL

Post by onoehring » 2019-07-15 04:59

Hi,

I forgot another problem source. This might be the solution for the
  • Nothing in the dropdown at all
mentioned before.

Solution part III
Check your SQL and make sure, it works. Run your SQL for example directly in phpmyadmin. It should run without any errors.
Olaf

Post Reply