Query ResultSet VS Dislay Order

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
RentProperty
Posts: 345
Joined: Mon Nov 12, 2012 9:08 pm
Location: South Africa

Query ResultSet VS Dislay Order

Post by RentProperty »

Hi Guys,

So we are trying to find an efficient way to FIND the last 10 Transactions and display them in a Query in ASC order.
To Find the last 10 you need to do 'ORDER BY ID DESC TAKE BEST 10' However we want the Display of these 10 Items to be in ASC order. :-)

Any (Uncomplicated and Performance Friendly) ideas? Previously we Used a Counter on each transaction and then Used that in the query. However I just feel there must be a simpler more efficient way. Would be Great to have a 'TAKE LAST 10' option in the Queries.

Kind Regards

Hein
Hein Hanekom & Werner Hanekom
Sinov8.net
AwareIM Version 5.9 | 6.0 | 7.0 | 7.1 (Windows EC2 R2012 & MySQL)
RocketRod
Posts: 907
Joined: Wed Aug 06, 2008 4:22 am
Location: Melbourne

Re: Query ResultSet VS Dislay Order

Post by RocketRod »

Try creating a DB view for the capture of the last n objects. Then create a BO based on that view. Then a query on the BO to display/sort in asc order.

Cheers Rod
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: Query ResultSet VS Dislay Order

Post by tford »

I'm not sure this is much better than the counter you were using, but you could FIND the 10 items, then update an attribute that flags the found items. Then the found items could be displayed in ID order.

You could also probably construct a rule that fires when new instances are added that goes out and reads the last 10 items and marks them in the flag attribute accordingly. Then they would always be ready to display without any additional processing.
Tom - V8.8 build 3137 - MySql / PostGres
johntalbott
Posts: 619
Joined: Wed Jun 17, 2015 11:16 pm
Location: Omaha, Nebraska
Contact:

Re: Query ResultSet VS Dislay Order

Post by johntalbott »

Is this V6 or V7?

If V7, you can run the query you described above and then use jQuery to sort the data by ID in ascending order on the client-side.
VocalDay Solutions - Agility - Predictability - Quality

We specialize in enabling business through the innovative use of technology.

AwareIM app with beautiful UI/UX - https://screencast-o-matic.com/watch/crfUrrVeB3t
RentProperty
Posts: 345
Joined: Mon Nov 12, 2012 9:08 pm
Location: South Africa

Re: Query ResultSet VS Dislay Order

Post by RentProperty »

Thnx for the Suggestions Guys! Appreciate it. I was hoping there is some way to do it Directly in the Query using some (Maybe not so common) SQL syntax. :-)

It's version 7 so the JQuery sorting might work... but will have to figure that out since we are not Javascript Experts.

Hope you all have a splendid day!
Hein Hanekom & Werner Hanekom
Sinov8.net
AwareIM Version 5.9 | 6.0 | 7.0 | 7.1 (Windows EC2 R2012 & MySQL)
johntalbott
Posts: 619
Joined: Wed Jun 17, 2015 11:16 pm
Location: Omaha, Nebraska
Contact:

Re: Query ResultSet VS Dislay Order

Post by johntalbott »

RentProperty wrote:Thnx for the Suggestions Guys! Appreciate it. I was hoping there is some way to do it Directly in the Query using some (Maybe not so common) SQL syntax. :-)

It's version 7 so the JQuery sorting might work... but will have to figure that out since we are not Javascript Experts.

Hope you all have a splendid day!
Try this in the Render Script section of the query:

var grid = $("#" + parser.m_widgetInfo.markupId).data("kendoGrid");
var ds = grid.dataSource;
ds.sort({ field: "ID", dir: "asc" });
VocalDay Solutions - Agility - Predictability - Quality

We specialize in enabling business through the innovative use of technology.

AwareIM app with beautiful UI/UX - https://screencast-o-matic.com/watch/crfUrrVeB3t
johntalbott
Posts: 619
Joined: Wed Jun 17, 2015 11:16 pm
Location: Omaha, Nebraska
Contact:

Re: Query ResultSet VS Dislay Order

Post by johntalbott »

Actually this is better ....

Put this in the Initialisation Script of the query:

var ds = config.dataSource;
ds.sort = {field: "ID", dir: "asc"};
VocalDay Solutions - Agility - Predictability - Quality

We specialize in enabling business through the innovative use of technology.

AwareIM app with beautiful UI/UX - https://screencast-o-matic.com/watch/crfUrrVeB3t
BLOMASKY
Posts: 1470
Joined: Wed Sep 30, 2015 10:08 pm
Location: Ocala FL

Re: Query ResultSet VS Dislay Order

Post by BLOMASKY »

John, I have to come over and pick your brain. This is the stuff that I am very stupid with. (you don't live near CT, do you???)


Bruce
johntalbott
Posts: 619
Joined: Wed Jun 17, 2015 11:16 pm
Location: Omaha, Nebraska
Contact:

Re: Query ResultSet VS Dislay Order

Post by johntalbott »

No, but I'm just a Skype call away.
VocalDay Solutions - Agility - Predictability - Quality

We specialize in enabling business through the innovative use of technology.

AwareIM app with beautiful UI/UX - https://screencast-o-matic.com/watch/crfUrrVeB3t
RentProperty
Posts: 345
Joined: Mon Nov 12, 2012 9:08 pm
Location: South Africa

Re: Query ResultSet VS Dislay Order

Post by RentProperty »

Thanks John! Would be ideal if something like this could work :-)

Are there any prerequisites for this to work? i.e. that the ID field must be one of the column headers etc? I tried both variations and it doesn't seem to be doing anything.

I tried it in both the init script and the render script, custom queries and normal queries, with the sorting field displayed and not. Not sure what I am missing.

Thanks again, appreciate the help
Hein Hanekom & Werner Hanekom
Sinov8.net
AwareIM Version 5.9 | 6.0 | 7.0 | 7.1 (Windows EC2 R2012 & MySQL)
johntalbott
Posts: 619
Joined: Wed Jun 17, 2015 11:16 pm
Location: Omaha, Nebraska
Contact:

Re: Query ResultSet VS Dislay Order

Post by johntalbott »

I'm not sure why it's not working for you. I'm just using a normal query that is displayed as a table. btw .. I'm assuming that you are using ID as your sorting column.

This has to do with how Aware converts a query to SQL, but without getting into the details ..
-If you are using the BEST operator, you WILL have to select the sorting column to be displayed in AwareIM
-If you are not using BEST, you WILL NOT need to select the sorting column to be displayed in AwareIM

If don't want the column to display, you can put this in the Render script.

widget.hideColumn("ID");
VocalDay Solutions - Agility - Predictability - Quality

We specialize in enabling business through the innovative use of technology.

AwareIM app with beautiful UI/UX - https://screencast-o-matic.com/watch/crfUrrVeB3t
Post Reply