SQL Query and TObjectList Result

BIZ TMS Aurelius
1

Is it possible to use Aurelius with RAW SQL statements and for it to return either a TList or TObjectList?

Approach

Create a generic function to which I can pass a select query and it will in turn return either a TObjectList or a TList which can be used on the UI side by a TAureliusDataset.

Problem

The usage of the TCriteria and Projections are not clear to me, e.g.
If I want a result set with only  a number of the columns e.g. Just User ID and User Name, how do I go about it?




2

You can't create a custom SQL and map it to an object, unfortunately. But you have many options to make your query very flexible, including SQL condition and SQL projections where you provide custom SQL to build the query (but those are parts of the query (where and select clauses) not the entire sql).

The projections are a way to retrieve scalar values (including sum, max, etc.), so you have flexibility to retrieve any value instead of objects. So you can perform a query that retrieves just User Id and User Name, but that query result will come in a special TCriteriaResult object, not a specific class.
3
Hi Wagner,
Can you use the 'IN' operator when selecting records? In SQL I can do this-
SELECT * from Departments
where Departments.LocationCode in ('A','E','F','M','R')

Something like  .Add(TExpression.IN('LocationCode', ('A','E','F','M','R'))
Thanks
4

Hello Wagner,

Please assist me with a sample query on how one can get a result set with just the User ID and User Name from a class like one below;

//User Class
TUser = Class
  UserID : string;
  UserName : string;
  Email : string;
  MPassword : string;
end;

5

Greg, you must also use SQL condition. There is no "In" function in TExpression for now.

Eli, you can use this:
Results := Manager.Find<TUser>.Select(TProjections.ProjectionLilst
  .Add(TProjections.Prop('UserId')
  .Add(TProjections.Prop('UserName')
  .ListValues;

then you can grab the values this way:
UserName := Results[0].Values['UserName'];
  
6

>>>Greg, you must also use SQL condition. There is no "In" function in TExpression for now.


Thanks Wagner, I got that working.
Can you please add the 'In' function to the wish list.

Cheers
Greg
7

Greg, you could add it as a request in our feature request system so it can get registered and can be voted.

8

Hi,
 Lets take the example above

function GetUser(): TJSONValue;
var FJsonSerializer: TDBJsonSerializer;
begin
  Results := Manager.Find<TUser>.Select(TProjections.ProjectionLilst
  .Add(TProjections.Prop('UserId')
  .Add(TProjections.Prop('UserName')
  .ListValues;

 Result := FJsonSerializer.ToJson(Results);//How to do that?
end;

what is the best approach, to pass a JSON to de Client with DataSnap Rest Server(XE7)?

9

You can just do as you wrote.

the serializer/deserializer support objects of type TList<TUser>.