related records not sorted

18 views
Skip to first unread message

airdrummer

unread,
Feb 24, 2021, 9:06:59 AMFeb 24
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:
<pre>[transactions]
__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:-}

airdrummer

unread,
Mar 12, 2021, 12:04:18 PMMar 12
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

unread,
Mar 12, 2021, 12:57:13 PMMar 12
to xata...@googlegroups.com
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 xataface+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/xataface/36aeb930-0cd6-4424-a599-9f79da5f4f40n%40googlegroups.com.


--
Steve Hannah
Web Lite Solutions Corp.

airdrummer

unread,
Mar 12, 2021, 2:26:07 PMMar 12
to Xataface
exxxcellent, thanx!
Reply all
Reply to author
Forward
0 new messages