mORMot Open Source

hanvas

Member

Registered: 2019-09-22

Posts: 1

how do mormot groups query criterias

Hi all,

i have searched for a while and digged through the docs but i did not find an answer or even a hint.

In SQL i can use parenthesis to group query criterias:

Examples :

1. WHERE   ((a.id = 10) or (a.id=20)) AND
                   ((a.name LIKE '%what') or (a.name LIKE '%whine'))
2. WHERE ((a.id < 10) AND (a.name LIKE '%what')) OR
                 ((a.id >100) AND (a.name LIKE '%whine'))

is this kind of grouping query critieras possible with mormot.

Thanks in advance

hajoe

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,548

Website

Re: how do mormot groups query criterias

The WHERE clause is sent to the SQL backend untouched, so you can do whatever the backend knows.
So both your clauses would be possible with mORMot with a SQL backend (like SQlite3 or any SynDB external variant).

Some restrictions apply to supported NoSQL engines, thought:
- the internal in-memory noSQL engine (e.g. TSQLRestServerFullMemory) has a limited WHERE clause ability, with only a single conditional.
- MongoDB SQL to pipeline conversion is more complete: you can use several AND/OR clauses with no problem, but parenthesis support is not done.

wxinix

Member

Registered: 2020-09-07

Posts: 121

Re: how do mormot groups query criterias

The WHERE clause is sent to the SQL backend untouched, so you can do whatever the backend knows.
So both your clauses would be possible with mORMot with a SQL backend (like SQlite3 or any SynDB external variant).

Some restrictions apply to supported NoSQL engines, thought:
- the internal in-memory noSQL engine (e.g. TSQLRestServerFullMemory) has a limited WHERE clause ability, with only a single conditional.
- MongoDB SQL to pipeline conversion is more complete: you can use several AND/OR clauses with no problem, but parenthesis support is not done.

This is great to know.

Then how can I perform something like SELECT AVG(Speed) ...WHERE.... GROUP BY xxx? Is it doable with mORMot?

wxinix

Member

Registered: 2020-09-07

Posts: 121

Re: how do mormot groups query criterias

OK. I found the answer from this post:
https://synopse.info/forum/viewtopic.php?id=4986

OneFieldValue, and use 'SUM(xx)' as the field name