How? Sort lookup by different column

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

How? Sort lookup by different column

Post by onoehring » 2019-06-19 13:53

Hi,

I am using a custom SQL query in an advanced lookup.
Unfortunately AG always sorts by the column I have selected for view (see images)

Is there a way to force AG to not sort, but just use what it get's from my SQL?
Of course sorting is nice (in general) , but this is done in advance much more sophisticated by the SQL.

AG Setting
e17.png
e17.png (10.35 KiB) Viewed 4767 times
and here you can see, that the sorting is done not numerical (as I did in my query, see below)

Wrong:
e18.png
e18.png (4.71 KiB) Viewed 4767 times
Correct:
This is the correct sort I need (and which is returned by my SQL)
Lagerort_Kurz ASC
ID_posX ASC
ID_posY DESC
ID_posZ DESC
All this is reflected in the automatically created Location_Auto column - which should be shown in AG. ID_locationNum is PK and saved in the master table.
e19.png
e19.png (14.65 KiB) Viewed 4767 times
Olaf

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: How? Sort lookup by different column

Post by pbottcher » 2019-06-19 15:39

Hi,

can you post your custom sql that you use for the lookup.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How? Sort lookup by different column

Post by onoehring » 2019-06-19 16:05

Hi pbötcher,

sure - though not sure if it helps:

Code: Select all

SELECT *
FROM   (SELECT ecomo_Location.ID_LocationNum,
               ecomo_Location.Location_Auto,
               ecomo_Lagerorte.Lagerort_Kurz,
               ecomo_Location.ID_posX,
               ecomo_Location.ID_posY,
               ecomo_Location.ID_posZ
        FROM   (SELECT ecomo_Location.ID_Lagerorte,
                       ecomo_Location.ID_posX,
                       ecomo_Location.ID_posY,
                       Min(ecomo_Location.ID_posZ) AS Min_ID_posZ
                FROM   ecomo_Location
                       INNER JOIN (SELECT ecomo_Location.ID_LocationNum,
                                          Count(ecomo_Container.ID_Location) AS Count_ID_Location
                                   FROM   ecomo_Location
                                          LEFT JOIN ecomo_Container
                                                 ON ecomo_Container.ID_Location = ecomo_Location.ID_LocationNum
                                   GROUP  BY ecomo_Location.ID_LocationNum) Query1
                               ON ecomo_Location.ID_LocationNum = Query1.ID_LocationNum
                WHERE  ( Query1.Count_ID_Location = 0 )
                        OR ( ecomo_Location.ImmerZeigen = 'Ja' )
                GROUP  BY ecomo_Location.ID_Lagerorte,
                          ecomo_Location.ID_posX,
                          ecomo_Location.ID_posY,
                          Query1.Count_ID_Location,
                          ecomo_Location.ImmerZeigen) Query1
               INNER JOIN ecomo_Location
                       ON ecomo_Location.ID_Lagerorte = Query1.ID_Lagerorte
                          AND ecomo_Location.ID_posX = Query1.ID_posX
                          AND ecomo_Location.ID_posY = Query1.ID_posY
                          AND ecomo_Location.ID_posZ = Query1.Min_ID_posZ
               INNER JOIN ecomo_Lagerorte
                       ON ecomo_Location.ID_Lagerorte = ecomo_Lagerorte.ID_LagerorteNum
        ORDER  BY ecomo_Lagerorte.Lagerort_Kurz,
                  ecomo_Location.ID_posX,
                  ecomo_Location.ID_posY DESC,
                  ecomo_Location.ID_posZ DESC) ecomo_Location 
Olaf

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: How? Sort lookup by different column

Post by pbottcher » 2019-06-19 17:16

Hi,
just to be sure. This is the query that you have in the APPGini Advanced -> "User this SQL query .... " field.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How? Sort lookup by different column

Post by onoehring » 2019-06-19 18:07

Hi pbötcher,

yes, that is correct. Of course I created the query with another program. The query runs fine in phpmyadmin and shows the output above in picture 3.
The query also runs find in AG - but it seems AG forces an alphabetical sort on the first column of the query (not even the selected output column).
How I know: I put Location_Auto for a test in the last position. AG shows now the first column (Lagerort_Kurz) in the generated application, even though I selected ID_LocationNum in AG (picture 1)

Olaf

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: How? Sort lookup by different column

Post by pbottcher » 2019-06-19 20:26

Hi,

can you try this:

Code: Select all

SELECT *
FROM   (SELECT ecomo_Location.ID_LocationNum,
               ecomo_Location.Location_Auto,
               ecomo_Lagerorte.Lagerort_Kurz,
               ecomo_Location.ID_posX,
               ecomo_Location.ID_posY,
               ecomo_Location.ID_posZ
        FROM   (SELECT ecomo_Location.ID_Lagerorte,
                       ecomo_Location.ID_posX,
                       ecomo_Location.ID_posY,
                       Min(ecomo_Location.ID_posZ) AS Min_ID_posZ
                FROM   ecomo_Location
                       INNER JOIN (SELECT ecomo_Location.ID_LocationNum,
                                          Count(ecomo_Container.ID_Location) AS Count_ID_Location
                                   FROM   ecomo_Location
                                          LEFT JOIN ecomo_Container
                                                 ON ecomo_Container.ID_Location = ecomo_Location.ID_LocationNum
                                   GROUP  BY ecomo_Location.ID_LocationNum) Query1
                               ON ecomo_Location.ID_LocationNum = Query1.ID_LocationNum
                WHERE  ( Query1.Count_ID_Location = 0 )
                        OR ( ecomo_Location.ImmerZeigen = 'Ja' )
                GROUP  BY ecomo_Location.ID_Lagerorte,
                          ecomo_Location.ID_posX,
                          ecomo_Location.ID_posY,
                          Query1.Count_ID_Location,
                          ecomo_Location.ImmerZeigen) Query1
               INNER JOIN ecomo_Location
                       ON ecomo_Location.ID_Lagerorte = Query1.ID_Lagerorte
                          AND ecomo_Location.ID_posX = Query1.ID_posX
                          AND ecomo_Location.ID_posY = Query1.ID_posY
                          AND ecomo_Location.ID_posZ = Query1.Min_ID_posZ
               INNER JOIN ecomo_Lagerorte
                       ON ecomo_Location.ID_Lagerorte = ecomo_Lagerorte.ID_LagerorteNum
) ecomo_Location
        ORDER  BY ecomo_Lagerorte.Lagerort_Kurz,
                  ecomo_Location.ID_posX,
                  ecomo_Location.ID_posY DESC,
                  ecomo_Location.ID_posZ DESC
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How? Sort lookup by different column

Post by onoehring » 2019-06-20 07:59

Hi pbötcher,
no, your code did not work (and not in phpmyadmin).
I think I corrected your code (below) - but this also throws a never ending search (image below)
problem was in the last ORDER you took the wrong table name
ORDER BY ecomo_Lagerorte.Lagerort_Kurz,
ecomo_Location.ID_posX,
ecomo_Location.ID_posY Desc,
ecomo_Location.ID_posZ Desc

Code: Select all

Select
    ID_LocationNum,
    Location_Auto,
    Lagerort_Kurz,
    ID_posX,
    ID_posY,
    ID_posZ
From
    (Select
         ecomo_Location.ID_LocationNum,
         ecomo_Location.Location_Auto,
         ecomo_Lagerorte.Lagerort_Kurz,
         ecomo_Location.ID_posX,
         ecomo_Location.ID_posY,
         ecomo_Location.ID_posZ
     From
         (Select
              ecomo_Location.ID_Lagerorte,
              ecomo_Location.ID_posX,
              ecomo_Location.ID_posY,
              Min(ecomo_Location.ID_posZ) As Min_ID_posZ
          From
              ecomo_Location Inner Join
              (Select
                   ecomo_Location.ID_LocationNum,
                   Count(ecomo_Container.ID_Location) As Count_ID_Location
               From
                   ecomo_Location Left Join
                   ecomo_Container On ecomo_Container.ID_Location = ecomo_Location.ID_LocationNum
               Group By
                   ecomo_Location.ID_LocationNum) Query1 On ecomo_Location.ID_LocationNum = Query1.ID_LocationNum
          Where
              (Query1.Count_ID_Location = 0) Or
              (ecomo_Location.ImmerZeigen = 'Ja')
          Group By
              ecomo_Location.ID_Lagerorte,
              ecomo_Location.ID_posX,
              ecomo_Location.ID_posY,
              Query1.Count_ID_Location,
              ecomo_Location.ImmerZeigen) Query1 Inner Join
         ecomo_Location On ecomo_Location.ID_Lagerorte = Query1.ID_Lagerorte
                 And ecomo_Location.ID_posX = Query1.ID_posX
                 And ecomo_Location.ID_posY = Query1.ID_posY
                 And ecomo_Location.ID_posZ = Query1.Min_ID_posZ Inner Join
         ecomo_Lagerorte On ecomo_Location.ID_Lagerorte = ecomo_Lagerorte.ID_LagerorteNum) ecomo_Location
Order By
    ecomo_Location.Lagerort_Kurz,
    ecomo_Location.ID_posX,
    ecomo_Location.ID_posY Desc,
    ecomo_Location.ID_posZ Desc
ec20.png
ec20.png (3.61 KiB) Viewed 4740 times
I then tried your embeded query suggestion from another thread that I started with this SQL (below) ... did not work either - it shows the problematic (wrong) sort order (image below).

Code: Select all

select * from
(Select
    a.ID_LocationNum,
    a.Location_Auto,
    a.Lagerort_Kurz,
    a.ID_posX,
    a.ID_posY,
    a.ID_posZ
From
    (Select
         ecomo_Location.ID_LocationNum,
         ecomo_Location.Location_Auto,
         ecomo_Lagerorte.Lagerort_Kurz,
         ecomo_Location.ID_posX,
         ecomo_Location.ID_posY,
         ecomo_Location.ID_posZ
     From
         (Select
              ecomo_Location.ID_Lagerorte,
              ecomo_Location.ID_posX,
              ecomo_Location.ID_posY,
              Min(ecomo_Location.ID_posZ) As Min_ID_posZ
          From
              ecomo_Location Inner Join
              (Select
                   ecomo_Location.ID_LocationNum,
                   Count(ecomo_Container.ID_Location) As Count_ID_Location
               From
                   ecomo_Location Left Join
                   ecomo_Container On ecomo_Container.ID_Location = ecomo_Location.ID_LocationNum
               Group By
                   ecomo_Location.ID_LocationNum) Query1 On ecomo_Location.ID_LocationNum = Query1.ID_LocationNum
          Where
              (Query1.Count_ID_Location = 0) Or
              (ecomo_Location.ImmerZeigen = 'Ja')
          Group By
              ecomo_Location.ID_Lagerorte,
              ecomo_Location.ID_posX,
              ecomo_Location.ID_posY,
              Query1.Count_ID_Location,
              ecomo_Location.ImmerZeigen) Query1 Inner Join
         ecomo_Location On ecomo_Location.ID_Lagerorte = Query1.ID_Lagerorte
                 And ecomo_Location.ID_posX = Query1.ID_posX
                 And ecomo_Location.ID_posY = Query1.ID_posY
                 And ecomo_Location.ID_posZ = Query1.Min_ID_posZ Inner Join
         ecomo_Lagerorte On ecomo_Location.ID_Lagerorte = ecomo_Lagerorte.ID_LagerorteNum) a
Order By
    a.Lagerort_Kurz,
    a.ID_posX,
    a.ID_posY Desc,
    a.ID_posZ Desc) ecomo_Location
ec21.png
ec21.png (4.11 KiB) Viewed 4740 times
It seems, that AG breaks the SQL when it ends (at least in my case) with an ORDER.

Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How? Sort lookup by different column

Post by onoehring » 2019-06-20 08:13

Hi,

I checked, at least in ajax_combo.php the query shows up the way I entered - without any additional sorting ... :-(

Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How? Sort lookup by different column

Post by onoehring » 2019-06-20 14:16

Hi,

I am investigating the problem. It seems to me ... I would like some feedback on this ... that the file ajax_combo.php is containing and start of the problem.
If you look into that file, you will find

Code: Select all

if(preg_match('/ order by (.*)$/i', $combo->Query, $qm)){
			$custom_order_by = $qm[1];
			$combo->Query = preg_replace('/ order by .*$/i', '', $combo->Query);
		}
When I use my (for testing adjusted with an extra order by) custom query

Code: Select all

select * from (	
	Select
    ecomo_Location.ID_LocationNum,
    ecomo_Location.Location_Auto,
    ecomo_Lagerorte.Lagerort_Kurz,
    ecomo_Location.ID_posX,
    ecomo_Location.ID_posY,
    ecomo_Location.ID_posZ
From
    (Select
         ecomo_Location.ID_Lagerorte,
         ecomo_Location.ID_posX,
         ecomo_Location.ID_posY,
         Min(ecomo_Location.ID_posZ) As Min_ID_posZ
     From
         ecomo_Location Inner Join
         (Select
              ecomo_Location.ID_LocationNum,
              Count(ecomo_Container.ID_Location) As Count_ID_Location
          From
              ecomo_Location Left Join
              ecomo_Container On ecomo_Container.ID_Location = ecomo_Location.ID_LocationNum
          Group By
              ecomo_Location.ID_LocationNum) Query1 On ecomo_Location.ID_LocationNum = Query1.ID_LocationNum
     Where
         (Query1.Count_ID_Location = 0) Or
         (ecomo_Location.ImmerZeigen = 'Ja')
order by Query1.Count_ID_Location
     Group By
         ecomo_Location.ID_Lagerorte,
         ecomo_Location.ID_posX,
         ecomo_Location.ID_posY,
         Query1.Count_ID_Location,
         ecomo_Location.ImmerZeigen) Query1 Inner Join
    ecomo_Location On ecomo_Location.ID_Lagerorte = Query1.ID_Lagerorte
            And ecomo_Location.ID_posX = Query1.ID_posX
            And ecomo_Location.ID_posY = Query1.ID_posY
            And ecomo_Location.ID_posZ = Query1.Min_ID_posZ Inner Join
    ecomo_Lagerorte On ecomo_Location.ID_Lagerorte = ecomo_Lagerorte.ID_LagerorteNum
Order By
    ecomo_Lagerorte.Lagerort_Kurz,
    ecomo_Location.ID_posX,
    ecomo_Location.ID_posY Desc,
    ecomo_Location.ID_posZ Desc
) ecomo_Location
and test the regex (which seems to check if there is a custom order set) I get no results - it does not see, that my query is ordered already.
I tried

Code: Select all

 order by (.*)$
on https://regex101.com and on https://regexr.com

When I change the regex (on the mentioned pages to)

Code: Select all

(?:order\ by)
with modifiers global, multiline and case-insensitive /gim the order clause is found. Note: All order clauses are found (if you implement more than one) - which actually I do not see as a problem.
If a programmer can create an SQL for an advanced lookup field, he will be able to sort that field by SQL to his liking and is not in need for the Gini at this point.

If I change the regex to

Code: Select all

(?:order\ by)(.*)$
also the characters after the order by in the same line (!) are matched.
I am not sure right now what is done there in the code, if the order by is added when none is found or if the order contents (what is ordered by) is added to the string.

Please note, that this regex breaks the search box for me when I change it in ajax_combo.php.

Sidenote:
(the code above is followed by

Code: Select all

		if(preg_match('/ where (.*)$/i', $combo->Query, $qm)){
			$custom_where = $qm[1];
			$combo->Query = preg_replace('/ where .*$/i', '', $combo->Query);
		}
which might be the same problem for WHERE)

Please some insight from others on this. Thank you.

Olaf

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: How? Sort lookup by different column

Post by pbottcher » 2019-06-20 14:51

Hi,

thanks for correcting the code.
I cannot follow your point here. I tested on my testsystem and AppGini would present the results as I expected.

Can you post the result of

Code: Select all

Select
    ID_LocationNum,
    Location_Auto,
    Lagerort_Kurz,
    ID_posX,
    ID_posY,
    ID_posZ
From
    (Select
         ecomo_Location.ID_LocationNum,
         ecomo_Location.Location_Auto,
         ecomo_Lagerorte.Lagerort_Kurz,
         ecomo_Location.ID_posX,
         ecomo_Location.ID_posY,
         ecomo_Location.ID_posZ
     From
         (Select
              ecomo_Location.ID_Lagerorte,
              ecomo_Location.ID_posX,
              ecomo_Location.ID_posY,
              Min(ecomo_Location.ID_posZ) As Min_ID_posZ
          From
              ecomo_Location Inner Join
              (Select
                   ecomo_Location.ID_LocationNum,
                   Count(ecomo_Container.ID_Location) As Count_ID_Location
               From
                   ecomo_Location Left Join
                   ecomo_Container On ecomo_Container.ID_Location = ecomo_Location.ID_LocationNum
               Group By
                   ecomo_Location.ID_LocationNum) Query1 On ecomo_Location.ID_LocationNum = Query1.ID_LocationNum
          Where
              (Query1.Count_ID_Location = 0) Or
              (ecomo_Location.ImmerZeigen = 'Ja')
          Group By
              ecomo_Location.ID_Lagerorte,
              ecomo_Location.ID_posX,
              ecomo_Location.ID_posY,
              Query1.Count_ID_Location,
              ecomo_Location.ImmerZeigen) Query1 Inner Join
         ecomo_Location On ecomo_Location.ID_Lagerorte = Query1.ID_Lagerorte
                 And ecomo_Location.ID_posX = Query1.ID_posX
                 And ecomo_Location.ID_posY = Query1.ID_posY
                 And ecomo_Location.ID_posZ = Query1.Min_ID_posZ Inner Join
         ecomo_Lagerorte On ecomo_Location.ID_Lagerorte = ecomo_Lagerorte.ID_LagerorteNum) ecomo_Location
Order By
    ecomo_Location.Lagerort_Kurz,
    ecomo_Location.ID_posX,
    ecomo_Location.ID_posY Desc,
    ecomo_Location.ID_posZ Desc
in the direct phpmyadmin sql
and then in what AppGini displays.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How? Sort lookup by different column

Post by onoehring » 2019-06-20 15:31

Hi pbötcher,

direct SQL output of your query:
ec23.png
ec23.png (26.62 KiB) Viewed 4723 times
AppGini output of your query (new record):
ec24new.png
ec24new.png (3.89 KiB) Viewed 4723 times
Existing record:
ec24old.png
ec24old.png (5.77 KiB) Viewed 4723 times
My query (wrong sort):
ec25.png
ec25.png (5.96 KiB) Viewed 4723 times
Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How? Sort lookup by different column

Post by onoehring » 2019-06-20 15:32

Hi again

the point I was trying to make is, that I question the correctness of the regex that is used in the ajax_combo.php.

What if the order colums are in the next line (after the return) - this way the regex does not seem to find anything.

Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How? Sort lookup by different column

Post by onoehring » 2019-07-13 07:39

Hi again,

the setting
The problem above is embedded in this setting:
I have over sea ISO containers that are placed in a certain location. This location is divided in coordinates (row -> ID_posX, depth -> ID_posY, level -> ID_posZ). As there might be more than one area this is also attached as Lagerort. All this data is concated to "place": Lagerort*Reihe-Tiefe/Ebene so for example 8*1-2/3. This would be a place in area 8, row 1, depth (from the forlift drivers point of view) 2 and level 3. Below that are already two other containers (of course, as no container can hover in midair).
Now the forklift driver takes a container and takes it to another location. At this point my application is used to change that location. The driver therefore selects a new location from the list.
This list should of course be ordered and show (old solution): only places that are a) not empty or should always be show (ImmerZeigen=Ja) and b) not in midair, but the lowest available.
The new version (see below) is: only places that are a) have less containers than allowed (BelegtBei) in this place and b) not in midair, but the lowest available.


old version solution
pbötcher wrote me a PM after the problem was not solved here and suggested some code, that works (thank you very much here again):
First of all all custom SQL has to be a one line string without line breaks! This is very important, otherwise the regex will not find your ORDER.
This code sorts correctly and loads the rest of the list, when I scroll down, works with the ajax search and shows only the empty locations.

This is his code (I am using an image here, so you see, there is not much difference to the new code below, I also use SQL to make helping easier by copy&paste)
alt.png
alt.png (24.64 KiB) Viewed 4605 times
again as sql:

Code: Select all

SELECT *
FROM   (SELECT e1.ID_LocationNum,
               e1.Location_Auto,
               e1.ID_Lagerorte,
               e1.ID_posX,
               e1.ID_posY,
               Min(e1.ID_posZ) AS id_posZ,
               e1.Lagerort_Kurz
        FROM   (SELECT e.Location_Auto,
                       e.ID_Lagerorte,
                       e.ID_posX,
                       e.ID_posY,
                       e.ID_posZ,
                       e.ID_LocationNum,
                       e.ImmerZeigen,
                       ecomo_Lagerorte.Lagerort_Kurz
                FROM   ecomo_Location e
                       LEFT JOIN ecomo_Lagerorte
                              ON ecomo_Lagerorte.ID_LagerorteNum = e.ID_Lagerorte
                HAVING ( e.ID_LocationNum NOT IN (SELECT ecomo_Container.ID_Location
                                                  FROM   ecomo_Container) )
                        OR ( e.ImmerZeigen = 'Ja' )) e1
        GROUP  BY e1.ID_posX,
                  e1.ID_posY,
                  e1.Lagerort_Kurz) ecomo_Location
ORDER  BY ecomo_Location.Lagerort_Kurz,
          ecomo_Location.ID_posX,
          ecomo_Location.ID_posY DESC,
          ecomo_Location.id_posZ DESC 
the new (not working) solution
Now I changed my database to make locations not show if they are empty of ImmerZeigen=ja as above, but the user would be able to set a certain number that defines, when a location is full BelegtBei. A location will be shown in the dropdown list, if is has less entries in the table than the BelegtBei value is. This results in this SQL which works fine in phpmyadmin, returning the correct records in the correct order:
neu.png
neu.png (44.41 KiB) Viewed 4605 times
again as sql:

Code: Select all

SELECT *
FROM   (SELECT e1.ID_LocationNum,
               e1.Location_Auto,
               e1.ID_Lagerorte,
               e1.ID_posX,
               e1.ID_posY,
               Min(e1.ID_posZ) AS id_posZ,
               e1.Lagerort_Kurz
        FROM   (SELECT e.Location_Auto,
                       e.ID_Lagerorte,
                       e.ID_posX,
                       e.ID_posY,
                       e.ID_posZ,
                       e.ID_LocationNum,
                       e.ImmerZeigen,
                       ecomo_Lagerorte.Lagerort_Kurz
                FROM   ecomo_Location e
                       LEFT JOIN ecomo_Lagerorte
                              ON ecomo_Lagerorte.ID_LagerorteNum = e.ID_Lagerorte
                HAVING ( e.ID_LocationNum IN (SELECT aLocation.ID_LocationNum
                                              FROM   ecomo_Location aLocation
                                                     INNER JOIN (SELECT e.ID_LocationNum,
                                                                        Count(ecomo_Container.ID_ContainerNum) AS c
                                                                 FROM   ecomo_Location e
                                                                        LEFT JOIN ecomo_Container
                                                                               ON ecomo_Container.ID_Location = e.ID_LocationNum
                                                                 GROUP  BY e.ID_LocationNum) aZahlContVorhanden
                                                             ON aZahlContVorhanden.ID_LocationNum = aLocation.ID_LocationNum
                                              WHERE  aZahlContVorhanden.c < aLocation.BelegtBei) )) e1
        GROUP  BY e1.ID_posX,
                  e1.ID_posY,
                  e1.Lagerort_Kurz) ecomo_Location
ORDER  BY ecomo_Location.Lagerort_Kurz,
          ecomo_Location.ID_posX,
          ecomo_Location.ID_posY DESC,
          ecomo_Location.id_posZ DESC 
the problem with the new SQL in the generated AppGini code
As you see, I marked two sections with a border in the images. This is the only difference and the result of the complete SQL is exactly the same.
But with the new SQL the generated AppGini files do not work anymore!
The Order is still correct and loading more from the list works too, but AJAX search does not work - the spinner keeps spinning but does not suggest anything as can bee seen in the picture below.
ec64.png
ec64.png (6.82 KiB) Viewed 4605 times
As the SQL does work in Mysql I am requesting your help. I can not see any reason, why this happens but I think something is strange with the AJAX search.

Olaf

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: How? Sort lookup by different column

Post by pbottcher » 2019-07-13 21:37

Hi,

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.

It only works at the end of the query. Otherwise the query will be broken, as discribed in the post.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How? Sort lookup by different column

Post by onoehring » 2019-07-14 06:15

Hi pbötcher,

thank you for that hint. I will try, try to reformulate my SQL and report back.

Probably you are correct - but if so, this is something I believe should be mentioned in the docs around page 15 in the "Understanding lookup fields" section.

Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How? Sort lookup by different column

Post by onoehring » 2019-07-14 16:38

Hi pbötcher,

once again, your help is very much appreciated and was indeed correct. I solved it by taking

Code: Select all

SELECT aLocation.ID_LocationNum
     FROM   ecomo_Location aLocation
            INNER JOIN (SELECT e.ID_LocationNum,
                               Count(ecomo_Container.ID_ContainerNum) AS c
                        FROM   ecomo_Location e
                               LEFT JOIN ecomo_Container
                                      ON ecomo_Container.ID_Location = e.ID_LocationNum
                        GROUP  BY e.ID_LocationNum) aZahlContVorhanden
                    ON aZahlContVorhanden.ID_LocationNum = aLocation.ID_LocationNum
     WHERE  aZahlContVorhanden.c < aLocation.BelegtBei
out of my query and created a view (view_FreiePlaetze) inside the database from that. I then replaced it in the query with

Code: Select all

SELECT ID_LocationNum FROM  view_FreiePlaetze
Works perfectly.

Olaf

Post Reply