mORMot Open Source

tbo

Member

Registered: 2015-04-20

Posts: 339

Extending function Int64DynArrayToCsv()

Arnaud, can you please extend the function Int64DynArrayToCsv() as follows:

function Int64DynArrayToCsv(Values: PInt64Array; ValuesCount: integer;
  const Prefix: RawUtf8 = ''; const Suffix: RawUtf8 = ''; const PrefixItem: RawUtf8 = ''; const SuffixItem: RawUtf8 = '';
  InlinedValue: boolean = false): RawUtf8; overload;

The reason is, I want to get the result set back in the same order as the IDs in the IN-list.

select
  *
from
  Article
where
  ID in (13,3,2)
order by
  ID=13 desc,ID=3 desc,ID=2 desc;

This allows me to write the IN and ORDER part as follows:

Int64DynArrayToCsv(Pointer(rankingArticleIDs), Length(rankingArticleIDs),  'ID in (', ')');
Int64DynArrayToCsv(Pointer(rankingArticleIDs), Length(rankingArticleIDs),  'order by ', ';', 'ID=', ' desc');

With best regards
Thomas

tbo

Member

Registered: 2015-04-20

Posts: 339

Re: Extending function Int64DynArrayToCsv()

Maybe it is useful for someone. To create the SQL query described above, I use the following source code:

var
  rankingArticleIDs: TIDDynArray;
  rankingSqlWhere: RawUtf8;
begin
  ...
  rankingSqlWhere := TIDDynArrayToCsv(rankingArticleIDs, 'RowID in (', ') ');
  rankingSqlWhere := rankingSqlWhere + TIDDynArrayToCsv(rankingArticleIDs, 'order by ', ';', 'ID=', ' desc');
  pmoSalesRanking := restOrm.RetrieveDocVariantArray(TOrmArticle, '', rankingSqlWhere, [], _RETURN_FIELDS);
end; 

Additionally the following helper function:

function TIDDynArrayToCsv(const pmcArray: TIDDynArray; const pmcPrefix: RawUtf8 = ''; const pmcSuffix: RawUtf8 = '';
  const pmcItemPrefix: RawUtf8 = ''; const pmcItemSuffix: RawUtf8 = ''): RawUtf8;
var
  i, highArrIdx: Integer;
  w: TBaseWriter;
  buffer: TTextWriterStackBuffer;
begin
  Result := '';
  if Length(pmcArray) = 0 then Exit; //=>

  w := TBaseWriter.CreateOwnedStream(buffer);
  try
    w.AddString(pmcPrefix);
    highArrIdx := Length(pmcArray) - 1;
    for i := 0 to highArrIdx do
    begin
      w.AddString(pmcItemPrefix);
      w.Add(pmcArray[Int64(i)]);
      w.AddString(pmcItemSuffix);
      if i < highArrIdx then
        w.AddComma;
    end;

    w.AddString(pmcSuffix);
    w.SetText(Result);
  finally
    w.Free;
  end;
end; 

With best regards
Thomas

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,563

Website

Re: Extending function Int64DynArrayToCsv()

Such SQL statements works for SQLIte3 only. For other DB back-ends either SQL statement size will be a limitation (for long ID's array), or ORDER BY ID=13... syntax is not supported. IMHO it is easier (and for sure faster) to reorder result on the client (if needed).

tbo

Member

Registered: 2015-04-20

Posts: 339

Re: Extending function Int64DynArrayToCsv()

Such SQL statements works for SQLIte3 only.

You are absolutely right. The procedure is intended only for SQLite. The tip with the order is from the SQLite forum. The client is an HTML WebApp. Your hint is correct, it is only a small amount of data that needs to be sorted here. But this is rather common for this kind of software. For these types of applications, I have switched completely to SQLite. My software is multi-tenant. Each client has its own database. This keeps the amount of data in a manageable size. I lost the note in the header that the tip is only for SQLite when copying parts of the text over to the forum editor. Thanks for the hint.

With best regards
Thomas