mORMot Open Source

lele9

Member

Registered: 2011-10-28

Posts: 170

tcollection or tdynarray and select

hi,
i have a tcollection/tdynarray property in a tsqlrecord object.
i would like to query(with a select statment) just the first element of tcollection/tdynarray.
It's possible? how?
thanks,
Emanuele.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: tcollection or tdynarray and select

What is the DB backend you are using?

If this is an external DB, the fastest would be to retrieve only this data field and its ID, then perform the search.
If this is an internal SQLite3 database, you can write your own SQLite3 custom function, to perform the search within the SQLite3 engine itself - see the IntegerDynArrayContains(), RawUTF8DynArrayContainsCase/NoCase() and Byte/Word/Cardinal/Int64/CurrencyDynArrayContains(BlobField,I64) SQL functions, able to fast search data within T*DynArray and TRawUTF8DynArray published properties BLOB.

In all cases, my advice and opinion (which may be wrong) is that if you need to search within a sharding field, you should better use a dedicated SQL table or field, then perform the query by hand.
To my knownledge, only NoSQL databases do support such queries within a complex field, and PostgreSQL (especially with their new JSONB format to be released in the upcoming 9.4 - see http://www.pgcon.org/2014/schedule/atta … .4json.pdf).

We may add a native SQLite3 function, able to search within some JSON content on any database.
Could be a nice improvement.

cheemeng

Member

From: Malaysia

Registered: 2011-08-09

Posts: 61

Re: tcollection or tdynarray and select

My 2 cents, Arnaud... there are really quite a few scenarios I have encountered that require this time of 1-to-many relationships.

I do hope foreign keys and 1-to-many (with optional lazy loading) can rank higher in the wish list

lele9

Member

Registered: 2011-10-28

Posts: 170

Re: tcollection or tdynarray and select

i try to explain the scenarios...
i have a TSQLPerson with a TCollection (but could be a tdynarray also) property "ContactDetails" where i store telephon numbers, emails etc etc.
Now, what i want, is retrieve a TSQLTable (ExecuteList with a personalized query) that contains (just one!) contactDetail (for example the contactDetail marked as "default").
is possible?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: tcollection or tdynarray and select

In this case, the easiest is not to use a TCollection nor a dynamic array, but a separated TContactDetail table, which will have such published fields:

type
  TContactDetail = type(TSQLRecord);
  ...
  published
    Contact: TContact read fContact write fContact; // one-to-many reverse cardinality
    State: TContactDetailState read fState write fState;
  end;

and use:

aDefaultDetail := TContactDetail.Create(aRest,'Contact=? and State=%',[ord(cdsDefault)],[aContactID]);

cdsDefault being the default in TContactDetailState enumerate.