You are not logged in.
Pages: 1
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
Offline
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
Offline
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).
Offline
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
Offline
Pages: 1