I wanted to see complex queries and try to understand it. There are some situations that I really dont know what to do :(
Since Aurelius retrieve object for my program, how SUM for example, works? I mean how to get data retrieved with calculations, ordering, etc, in a SQL database this is like a newly created table (view). Is there such thing? that can also be connected to the DataSet?
The only complex query I made, I could not understand (shame) and I have changed it to load the data on memory and the process using simple procedures.
That is not good and take an important part of SQL away.
Base on that, I needed to retrieve all the sales from a specific period of time and get the daily totals of sales, generating a simple 2 columns table to be used in a chart. I ended up getting a TList of the objects and interacting thru it to get these grand totals.
I think I did not understand the documentation about this. So, is there an example of that?
Eduardo, there are no more samples than the ones available in documentation.
Queries basically can return entities, or projections. When using projections, no classes are retrieved anymore (no TCustomer, TInvoice, etc.), but instead, scalar values are returned.
Also, keep in mind you have both SQL condition and SQL projection which allows you inject custom SQL into queries, this is sometimes useful and needed for queries too complex if Aurelius criteria API is not enough (and it's not, in some situations).
Grouping in Aurelius is "automatic", I mean, if you do Sum(Total), you don't need to do "GroupBy(Field)", because it groups automatically by the fields not in aggregated functions. So in your example, you basically add a projection for "Day", and them add "Sum(Total)", it will give you a sum for each day. However, some functions are missing (this is in our todo list), like Day, Month, Year, and also some projection functions like Add, Subtract, etc.. But you can use SQL project to extract the "DayOf(Field)" and then sum by that field.