Recommended implementation of auto completed properties

damiand · 2019-06-04 09:43:20

Let's assume to have a TSQLRecord descendant class, named for example TMyDoc which have a multi valued text property, named for example Recipients. When a user creates or edits a TMyDoc object I want to enable auto completion of the Recipients property based on the existing values stored in all similar objects in the database. For example when a user writes Be the application will query all distinct Recipients values starting as this, and present a sorted list with the results, like:

Beethoven
Berlioz

What is the recommended way to implement the Recipients property in the TMyDoc class definition? As a TStrings for example? If yes, how can I perform such select distinct queries on the Recipients property?

ab · 2019-06-04 10:28:34

I would either:

1. pre-read the values into a local in-memory TRawUTF8DynArray, then use brute-force lookup - via IdemPChar over the uppercased values, or sort the lines, and use fast binary search - this will need to reload all values if it changes

2. create an index on the TSQLRecord name field, then use a server-side REST service search over something like Name>='Be' and Name<'Bf' limit 50, with a local and server-side cache

The option 2 may be more scalable I guess.

damiand · 2019-06-05 14:04:29

Thanks. By the way, I defined the property as TStrings and mORMot created in the corresponding SQLite3 table, a field type TEXT with binary encoding. With SQLite Studio I see that in this field an array is stored with binary encoded values. When I retrieve the object using CreateAndFillPrepare, this property remains nil, where another RawUTF8 property (e.g. Name) gets its value correctly. Am I missing another step to load correctly the TStrings object from the database field into the corresponding class property?

ab · 2019-06-05 14:07:29

TStrings don't store anything: you need to have a TStringList instance to store something.
But this is not the best way to store array of strings.

Just use a variant field and a TDocVariant within, or a TRawUTF8DynArray field.

edwinsn · 2019-06-06 05:11:07

Use TStringList to store the value list, and use json_each to query. Check: https://www.sqlite.org/json1.html

Last edited by edwinsn (2019-06-06 10:36:39)

damiand · 2019-06-06 10:35:09

Thank you both. Just for the record, while in Delphi (DX) I am able to populate the directly instantiated property with Add(), in FPC I have to use a local variant variable first and assign this variable to the property in order to store correctly in the database field an array of strings. Otherwise stores an empty array.

The following lines work fine with Delphi DX, but with FPC stores in the GroupMember database field an empty array.

  group := TSQLGroups.Create;
  try
    group.GroupName := Edit1.Text;
    group.GroupMember := _Arr([]);
    for i := 0 to Memo1.Lines.Count - 1 do
        group.GroupMember.Add(Memo1.Lines[i]);
    Database.Add(group, true);
  finally
    group.Free;
  end;

With FPC to get the same results, the code has to be changed as follows:

  v := _Arr([]);
  for i := 0 to Memo1.Lines.Count - 1 do
      v.Add(Memo1.Lines[i]);
  group := TSQLGroups.Create;
  try
    group.GroupName := Edit1.Text;
    group.GroupMember := v;
    Database.Add(group, true);
  finally
    group.Free;
  end;

Last edited by damiand (2019-06-06 10:36:30)

damiand · 2019-06-06 14:39:59

I ended up with a sql query as follows:

SELECT DISTINCT json_each.value
FROM Groups, json_each(GroupMember)
WHERE json_each.value LIKE 'Be%'
ORDER BY json_each.value;

What is the mORMot way to formulate and execute such a query?

edwinsn · 2019-06-07 02:51:09

Use ExecuteList which returns TSQLTableJSON.

mORMot Open Source

#1 2019-06-04 09:43:20

Recommended implementation of auto completed properties

#2 2019-06-04 10:28:34

Re: Recommended implementation of auto completed properties

#3 2019-06-05 14:04:29

Re: Recommended implementation of auto completed properties

#4 2019-06-05 14:07:29

Re: Recommended implementation of auto completed properties

#5 2019-06-06 05:11:07

Re: Recommended implementation of auto completed properties

#6 2019-06-06 10:35:09

Re: Recommended implementation of auto completed properties

#7 2019-06-06 14:39:59

Re: Recommended implementation of auto completed properties

#8 2019-06-07 02:51:09

Re: Recommended implementation of auto completed properties

Board footer