mORMot Open Source

ManUn

Member

Registered: 2013-02-04

Posts: 22

Select DISTINCT

Hello, How can I do a select with a DISTINCT clause using the TSQLRecord.CreateAndFillPrepare?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,242

Website

Re: Select DISTINCT

I think you just can't with the current implementation.
More generally, aggregate functions are not available - they are not REST-ready, I suspect.
Perhaps we may follow the pattern explained at http://developer.marklogic.com/learn/re … n-function

Could you add a ticket in http://synopse.info/fossil/reportlist (after anonymous login to fossil source code repository)?

Do you have an implementation preference for the call (additional parameter to CreateAndFillPrepare? or some refactoring?).
What about the REST corresponding URL?

ManUn

Member

Registered: 2013-02-04

Posts: 22

Re: Select DISTINCT

I think that the CreateAndFillPrepare should have a implementation with the possibility of we write our own SQL command.

Someting like :

Rec := TSQLRecord.CreateAndFillPrepare(DB, 'SELECT HERE');

Roberto Schneiders

Member

From: Santa Catarina, Brazil

Registered: 2012-09-19

Posts: 127

Website

Re: Select DISTINCT

That's not make sense for me ManUn.

We have to do that in ORM approach.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,242

Website

Re: Select DISTINCT

You can use any SELECT statement via the following method:

function TSQLRestClientURI.List(const Tables: array of TSQLRecordClass;
  const SQLSelect, SQLWhere: RawUTF8): TSQLTableJSON;

Just put 'DISTINCT ....' in SQLSelect parameter.
It is always better to use inline parameters, via the FormatUTF8() function with bound parameters.
See the "inline parameter" information in the SAD pdf.

ManUn

Member

Registered: 2013-02-04

Posts: 22

Re: Select DISTINCT

How about commands like: COUNT, AVG, SUM...

Is there a way to use it with TSQLRecord kind of approach?

Last edited by ManUn (2013-02-12 13:01:44)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,242

Website

Re: Select DISTINCT

You can use whatever SQL statement you want with TSQLRestClientURI.List().

Then use TSQLRecorD.FillPrepare() to fill the corresponding TSQLRecord fields from the returned value.
You just need to override the SQL column names to match the TSQLRecord field names.

Or use the returned TSQLTableJSON content directly.

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: Select DISTINCT

any news about distinct?

need to write repetitive code, sample

procedure Fill_combobox_data(r: TSQLRecord; s: TStrings; field: string);
begin
  s.Clear;
  r.CreateAndFillPrepare(Database, '');
  try
    while r.FillOne do s.Add(r.RecordProps.Fields.ByRawUTF8Name(field).GetValue(r, False));
  finally
    r.Free;
  end;
end;

if me need add distinct need write additional procedure

procedure Fill_distinct_combobox_data(r: TSQLRecord; s: TStrings; field: string);
var
 t: TSQLTable;
begin
  s.Clear;
  t := Database.List([r.RecordClass], 'distinct trim(' + field + ') as ' + field, '');
  if t = nil then Exit;
  t.OwnerMustFree := False;
  r.FillPrepare(t);
  try
    while r.FillOne do s.Add(r.RecordProps.Fields.ByRawUTF8Name(field).GetValue(r, False));
  finally
    r.Free;
    t.Free;
  end;
end;

what is currently the best option for the use of a distinct in framework?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,242

Website

Re: Select DISTINCT

AFAIR there is no direct support of DISTINCT at ORM level yet.

You will have to rely on a tuned SQL query in this case, I suppose.