To perform a query and retrieve the content of several documents, you can
use regular CreateAndFillPrepare
or FillPrepare
methods:
R := TSQLORM.CreateAndFillPrepare(Client,WHERE_CLAUSE,[WHERE_PARAMETERS]); try n := 0; while R.FillOne do begin // here R instance contains all values of one document, excluding BLOBs inc(n); end; assert(n=COLL_COUNT); finally R.Free; end;
A WHERE clause can also be defined for CreateAndFillPrepare
or
FillPrepare
methods.
This WHERE clause could contain several expressions, joined with
AND
/ OR
.
Each of those expressions could use:
- The simple comparators
= < <= <> > >=
, - An
IN (....)
clause, IS NULL
/IS NOT NULL
tests,- A
LIKE
operation, - Or even any
...DynArrayContains()
specific function.
The mORMot ODM will convert this SQL-like statement into the
optimized MongoDB
query expression, using e.g. a regular
expression for the LIKE
operator.
The LIMIT
, OFFSET
and ORDER BY
clauses will also be handled as expected.
A special care would be taken for an ORDER BY
on textual values:
by design, MongoDB will always sort text with case-sensitivity, which
is not what we expect: so our ODM will sort such content on client side, after
having been retrieved from the MongoDB server. For numerical fields,
MongoDB sorting features would be processed on the server side.
The COUNT(*)
function would also be converted into the proper
MongoDB API call, so that such operations would be as costless as
possible.
DISTINCT() MAX() MIN() AVG()
functions and the GROUP
BY
clause would also be converted into optimized MongoDB
aggregation pipelines, on the fly.
You could even set aliases for the columns (e.g. max(RowID) as
first
) and perform simple addition/substraction of an integer value.
Here are some typical WHERE clauses, and the corresponding MongoDB query document as generated by the ODM:
WHERE clause | MongoDB Query |
'Name=?',['Name 43'] |
{Name:"Name 43"} |
'Age<?',[51] |
{Age:{$lt:51}} |
'Age in (1,10,20)' |
{Age:{$in:[1,10,20]}} |
'Age in (1,10,20) and ID=?',[10] |
{Age:{$in:[1,10,20]},_id:10} |
'Age in (10,20) or ID=?',[30] |
{$or:[{Age:{$in:[10,20]}},{_id:30}]} |
'Name like ?',['name 1%'] |
{Name:/^name 1/i} |
'Name like ?',['name 1'] |
{Name:/^name 1$/i} |
'Name like ?',['%ame 1%'] |
{Name:/ame 1/i} |
'Data is null' |
{Data:null} |
'Data is not null' |
{Data:{$ne:null}} |
'Age<? limit 10',[51] |
{Age:{$lt:51}} + limit 10 |
'Age in (10,20) or ID=? order by ID desc',[30] |
{$query:{$or:[{Age:{$in:[10,20]}},{_id:30}]},$orderby:{_id:-1}} |
'order by Name' |
{} + client side text sort by Name |
'Age in (1,10,20) and
IntegerDynArrayContains(Ints,?)',[10]) |
{Age:{$in:[1,10,20]},Ints:{$in:[10]}} |
Distinct(Age),max(RowID) as first,count(Age) as count group by
age |
{$group:{_id:"$Age",f1:{$max:"$_id"},f2:{$sum:1}}},{$project:{_id:0,"Age":"$_id","first":"$f1","count":"$f2"}} |
min(RowID),max(RowID),Count(RowID) |
{$group:{_id:null,f0:{$min:"$_id"},f1:{$max:"$_id"},f2:{$sum:1}}},{$project:{_id:0,"min(RowID)":"$f0","max(RowID)":"$f1","Count(RowID)":"$f2"}} |
min(RowID) as a,max(RowID)+1 as b,Count(RowID) as c |
{$group:{_id:null,f0:{$min:"$_id"},f1:{$max:"$_id"},f2:{$sum:1}}},{$project:{_id:0,"a":"$f0","b":{$add:["$f1",1]},"c":"$f2"}} |
Note that parenthesis and mixed AND OR
expressions are not
handled yet.
You could always execute any complex query (e.g. aggregations or
Map/Reduce) by using directly the TMongoCollection
methods.
But for most cases, mORMot allows to share the same exact code
between your regular SQL databases or NoSQL engines.
You do not need to learn the MongoDB query syntax: the ODM would
compute the right expression for you, depending on the database engine it runs
on.
Ensure you
took a look at the updated documentation to better understand NoSQL/MongoDB
integration with mORMot.
And feel free to use our forum for
feedback, as usual!
Enjoy the NoSQL world!