Query Builder - How to sort by a column alias (aggregate)

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Query Builder - How to sort by a column alias (aggregate)

Postby huber » Mon Jan 14, 2019 6:58 pm

Hi

First of all a happy new year to you all, wishing good health, happiness, and many successful Servoy projects :D

I have the following SQL code and would like to rewrite it as QB Select. But I didn't find an example, docu or hint on how a QB sort is done by an alias like needed in the following SELECT.
Code: Select all
SELECT supplier, MAX(creation_date) as theDate
FROM invoices
GROUP BY supplier
ORDER BY theDate DESC;


The missing piece is how to write the query.sort
Code: Select all
var query = datasources.db.<name>.invoices.createSelect();
var i = query.columns;
query.result
   .add(i.supplier)
   .add(i.creation_date.max, 'creationDate');
query.groupBy
   .add(i.supplier);
query.sort
   .add(???);


Best regards,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm

Re: Query Builder - How to sort by a column alias (aggregate

Postby jcompagner » Tue Jan 22, 2019 2:46 pm

the docs should be quite clear what you can put in there:

query.sort.add(query.columns.orderid)

or

query.sort.add(query.columns.orderid.desc)
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Query Builder - How to sort by a column alias (aggregate

Postby huber » Wed Jan 23, 2019 12:43 pm

Ah, ok, a column with a method is a valid sort column, like .add(query.columns.creation_date.max.desc)

Regards,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 14 guests

cron