related records not sorted

Skip to first unread message


Feb 24, 2021, 9:06:59 AM2/24/21
to Xataface

while thrashing with trying to add a total to a list of related records, i'm bugged by the list not being sorted as the sql specifies in my relationships.ini:
__sql__ = "SELECT * FROM transactions  
 WHERE transactions.AccountID = '$lotNum'
 AND transactions.Date >=
     (SELECT lotOwnership.dateAquired from lotOwnership
        WHERE lotOwnership.lotNum = '$lotNum'                         
             order by dateAquired DESC LIMIT 1)
order by transactions.Date ASC"</pre>
whereas the same sql (with explicit lotnum) works as expected;-\

not a biggie, just a bugger:-}


Mar 12, 2021, 12:04:18 PM3/12/21
to Xataface
turns out the problem is the sql gets rewritten...order by appears in the select count sql:

Performing query: 'SELECT COUNT(*) as num from `transactions` where `AccountID` = '141' and `date` >= '2013-12-20' order by `transactions`.`Date` asc'

but is replaced in the actual retrieval sql:

Performing query: 'select `transactions`.`AccountID`, ifnull(convert_tz(`transactions`.`Date`,'SYSTEM','UTC'), `transactions`.`Date`) as `Date`, `transactions`.`transactionType`, `transactions`.`Amount`, `transactions`.`transID` from `transactions` where `AccountID` = '141' and `date` >= '2013-12-20' order by `transID` asc LIMIT 0,30'

Steve Hannah

Mar 12, 2021, 12:57:13 PM3/12/21
You shouldn't include the sort in the SQL query for a relationship.  Use Xataface's sorting configuration parameters instead.

You received this message because you are subscribed to the Google Groups "Xataface" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
To view this discussion on the web visit

Steve Hannah
Web Lite Solutions Corp.


Mar 12, 2021, 2:26:07 PM3/12/21
to Xataface
exxxcellent, thanx!
Reply all
Reply to author
0 new messages