mORMot Open Source

sachli

Member

Registered: 2011-11-21

Posts: 2

How to search in collection

Hi,

I have read the SAD forward and backward but I couldn't find the answer.

I have class like this:

  TLicense = class( TCollectionItem)
  private
    FUserName : TTextProperty;
    FPassword : TTextProperty;
    FActive   : Boolean;
  published
    property UserName : TTextProperty read FUserName write FUserName;
    property Password : TTextProperty read FPassword write FPassword;
    property Active   : Boolean       read FActive   write FActive;
  end;

  TLicenses = class(TCollection)
  public
    property Detail[index:Integer]:TLicense read GetDetail; default;
  end;

  

  TSQLCustomer = class(TSQLStandardRecord)
  private
    ...
    FLicenses   : TLicenses;
  public
    constructor Create; override;
    destructor Destroy; override;
  published
    property Name:TTextProperty read FName write FName;
     ...
    property Licenses   : TLicenses       read FLicenses      write FLicenses;
  end;

I have already been able to append a record. Now I would like to search all the Customer that have licenses.UserName = 'AA'.

My code is:

  Client.OneFieldValues( TSQLCustomer, 'ID', 'RawUTF8DynArrayContainsCase(UserName,:("AA"):)', Data);
  CheckEquals( 1, Length(Data));

Unfortunately, the Check fails because the query returns nothing.

Is there anything I have done wrong?
What is the right way to search in Collection?

Thanks in advance,

Sachli

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: How to search in collection

RawUTF8DynArrayContainsCase() function will work only for dynamic arrays stored as BLOB.
TCollection are stored as JSON arrays.
So you can not use the RawUTF8DynArrayContainsCase() function for your search.

There is no dedicated SQL function yet to search inside a collection.
We'll certainly have to find a way to add some customize SQL functions ready to parse JSON arrays and search its content.

May be something like:

Client.OneFieldValues( TSQLCustomer, 'ID', 'JSONContainsCase(''Licenses'',UserName,:("AA"):)', Data);

Some new SQL functions named JSONContainsNoCase() and JSONContainsCase()=JSONContains() could make sense.
I'll need to write some low-level new functions able to parse the JSON content and search within it, with best speed.
This will be much faster than retrieving the content on the client side as objects, then search for the data.

If you have some ideas of such function definition, feel free to post here!

sachli

Member

Registered: 2011-11-21

Posts: 2

Re: How to search in collection

OK, my idea is like this:
We could search directly using sql query such as:

select a.ID from Customer a where a.Licenses containing 'UserName:"AA"'

I don't know how fast can we search inside the blob. But based on my experience, it will not too fast. But at least it works.
OK, in the mean while, I think I should use One to Many relations first.

Is there anything I can do to help?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: How to search in collection

TCollection are stored as TEXT, not as BLOB.
They are serialized as plain JSON object.
Search will be fast, but of course slower than using a normal SQL field with an appropriate index.

In fact, my proposal will allow queries as such:

select ID from Customer where JSONContains(Licenses,'UserName',:("AA"):);

And it should be able to work also with an integer sub-field:

select ID from Customer where JSONContains(Licenses,'BirthYear',:(1972):);