This is a puzzle to me.
Transportation software... I have a Loads UX with a repeating section call Stops. The stops table contains all the data for the pickups and deliveries for each load.
I am querying the stops table to find all records that have a pickup scheduled on or before today, and the pickup status has not been confirmed.
Then I am returning the result set, which is the stops.Loads_FK, in a comma separated string to the Loads UX and executing a search.
Thus presenting the user with the records for all loads for which they need to confirm pickups.
Everything works fine except for the sort order of the result set. I want it to sort by the stops.StopDate, ascending.
Below is a simple ajax callback that queries a MySql table and returns a list of foreign key values, in a comma separated string.
The string is then used in a control in the Loads UX to execute a search.
Here is the key part of the script that I am running in the xBasic interactive window:
The result set that I get is 15772,15773,15774,15775,15780 which is sorted by stops.Loads.FK
The result set I am looking for is 15775, 15780, 15772, 15774, 15773 which is sorted by stops.StopDate ASC
If I run the same script without the GROUP_CONCAT, it sorts correctly, but of course it is not in a comma separated string.
So the question is.... how can I get a result set in a comma separated string, sorted by stops.StopDate ASC?
Or, is there another solution?
Transportation software... I have a Loads UX with a repeating section call Stops. The stops table contains all the data for the pickups and deliveries for each load.
I am querying the stops table to find all records that have a pickup scheduled on or before today, and the pickup status has not been confirmed.
Then I am returning the result set, which is the stops.Loads_FK, in a comma separated string to the Loads UX and executing a search.
Thus presenting the user with the records for all loads for which they need to confirm pickups.
Everything works fine except for the sort order of the result set. I want it to sort by the stops.StopDate, ascending.
Below is a simple ajax callback that queries a MySql table and returns a list of foreign key values, in a comma separated string.
The string is then used in a control in the Loads UX to execute a search.
Here is the key part of the script that I am running in the xBasic interactive window:
dim cn as sql::Connection
dim rs as sql::ResultSet
sql = <<%a%
SELECT
stops.StopDate, GROUP_CONCAT(stops.Loads_FK)
FROM stops
WHERE stops.StopDate <= CURDATE()and stops.PUorDel = "P" and stops.stopstatus <> "Confirmed"
ORDER BY
stops.StopDate ASC
%a%
cn.open("::Name::FreightzoneMySql")
cn.execute(sql)
rs = cn.ResultSet
Loads_FK = rs.data("GROUP_CONCAT(stops.Loads_FK)")
sql_resultset_preview(rs)
cn.FreeResult()
cn.close()
dim rs as sql::ResultSet
sql = <<%a%
SELECT
stops.StopDate, GROUP_CONCAT(stops.Loads_FK)
FROM stops
WHERE stops.StopDate <= CURDATE()and stops.PUorDel = "P" and stops.stopstatus <> "Confirmed"
ORDER BY
stops.StopDate ASC
%a%
cn.open("::Name::FreightzoneMySql")
cn.execute(sql)
rs = cn.ResultSet
Loads_FK = rs.data("GROUP_CONCAT(stops.Loads_FK)")
sql_resultset_preview(rs)
cn.FreeResult()
cn.close()
The result set that I get is 15772,15773,15774,15775,15780 which is sorted by stops.Loads.FK
The result set I am looking for is 15775, 15780, 15772, 15774, 15773 which is sorted by stops.StopDate ASC
If I run the same script without the GROUP_CONCAT, it sorts correctly, but of course it is not in a comma separated string.
So the question is.... how can I get a result set in a comma separated string, sorted by stops.StopDate ASC?
Or, is there another solution?
Comment