How to ORDER BY RANDOM() LIMIT 10

[Hans_de_Bue]

How would one setup a query that orders the result randomly and limits it ? 

Like SELECT * FROM table ORDER BY RANDOM() LIMIT 10

[wlandgraf (Wagner Landgraf)]

Use SQL projection:

Find<TTable>

  .Take(10)

  .OrderBy(TProjections.Sql<Integer>('RANDOM()'))

  .List;

[Hans_de_Bue]

Thanx!

[Araujo_Adan]

How use with TMS Webcore (XData Endpoint) for random order.

[wlandgraf (Wagner Landgraf)]

It's not possible to use the automatic CRUD endpoint, in this case you have to create a service operation for that.

[Araujo_Adan]

I try but I have a troble

This is my Service implementation

function TProductService.Product: TProduct;
var
//Declaro los objetos
Manager: TObjectManager;
oProduct: TProduct;
begin
// How to ORDER BY RANDOM() LIMIT 10
Manager := ObjectDataLab.GetManager;

try
// For test recovery record Id =1
Result := Manager.Find(1);

finally
Manager.Free;
end;

end;

Do you have some tips

[Araujo_Adan]

Sorry is last line,

Manager.Free;

if I comment this line works fine.

[wlandgraf (Wagner Landgraf)]

Looks like you are destroying the object twice.
Cannot guess as I don't know what ObjectDataLab.GetManager does.

[Araujo_Adan]

Sorry,
GetManager, Create Manager,

function TObjectDataLab.GetManager: TObjectManager;
var
ConnectionDB: IDBConnection;
begin
// Creo el objeto de las bases de datos
ConnectionDB := uConnectionModule.DatabaseConnection.AureliusConnection.CreateConnection;
Result := TObjectManager.Create(ConnectionDB);
end;

Do you mean that the Manager is destroyed when the method execution ends? and it is not necessary to destroy it?

[wlandgraf (Wagner Landgraf)]

@Araujo_Adan

Do you mean that the Manager is destroyed when the method execution ends?

Depend on how you retrieved the manager. If you got the manager from XData context, then XData would destroy it. That's not the case in your code.

How is your TProduct class declared?
And what is the call stack at the moment of the error?

[Araujo_Adan]

Dear Wagner, this is my TProduct Entity

uProduct.pas (12.2 KB)

[wlandgraf (Wagner Landgraf)]

I don't see any problem in your TProduct class at first sight.
I still didn't receive your call stack, though. Can you please send it?

[Araujo_Adan]

I prepare a test case for you.

Use Sqlite for this test and the mainform have two button (for create database and create sample records).
njewtestcase.zip (67.1 KB)

[wlandgraf (Wagner Landgraf)]

@Araujo_Adan

I prepare a test case for you.

Thanks.

Since you are returning objects that are inside the manager, you can create it, otherwise such objects will be destroyed.

Ask XData to destroy the manager when the method is finished:

  Manager := ObjectDataLab.GetManager;
  TXDataOperationContext.Current.Handler.AddManager(Manager);

And then don't destroy the manager (remove Manager.Free). Final code:

function TProductService.FeatureProduct: TList<TProduct>;
var
  Manager: TObjectManager;
begin
  // https://support.tmssoftware.com/t/how-to-order-by-random-limit-10/7653
  // Creo la conexion
  Manager := ObjectDataLab.GetManager;
  TXDataOperationContext.Current.Handler.AddManager(Manager);

  try
    TXDataOperationContext.Current.Request.Headers.SetValue('xdata-expand-level', '2');

    // SQL Lite test
    Result := Manager.Find<TProduct>.Take(3).List;

  finally
    // Destruyo el la conexio
//    Manager.Free;
  end;

end;

[Araujo_Adan]

Thank you.