mORMot Open Source

migajek

Member

Registered: 2010-10-01

Posts: 89

Selecting multiple records?

Hi,
once again very lame question but couldn't find it, it seems no sample apps shows this, and MainDemo doesn't compile for me since I don't have TMS controls pack.

How do I select multiple records? Let's say I want to list all the records in table using WHERE,
do I have to use Database.OneFieldValues, than use the array of ID's to create model with given ID? In that case, how do I find column name of unique ID field generated for my model (/record)? I've seen you were using 'ID' as a column name somewhere in this forums, but in sample DB it seems to be 'RowID' (unfortunately, Database.OneFieldValues(TSQLSampleRecord, 'RowID', '', arr) gives an empty array, yet there are some records in that DB).

If that is the only way, isn't it killing the performance? Don't know how about SQLite, but from my experience with MySQL + PHP, it's much much faster to select a thousand records using " SELECT * ... LIMIT 1000" than to do a loop and select one by one.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Selecting multiple records?

ID or RowID are synonymous, for regular SQlite3 records (only FTS3 records need DocID or RowID).

I use Database.OneFieldValues() in several apps, without any problem, to retrieve the IDs.

Of course, it's always better to select all IDs at once. You're right.

To select multiple records, you can use TSQLRest.MultiFieldValues with the needed fields.

migajek

Member

Registered: 2010-10-01

Posts: 89

Re: Selecting multiple records?

Actually, I was looking for a way to get an array / list of TSQLRecord descendent ...
The MultiFieldValues returns a TSQLTableJSON which has all the data, but does it "wrap" the data in TSQLRecord descendents?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Selecting multiple records?

Yes you can "wrap" the data into TSQLRecord descendant(s) properties.

Use TSQLRecord.FillPrepare() and FillRow()/FillOne() methods.
It's very optimized for speed.