Programmatic ConditionalPhrase

[yusufmk]

Hi, I need to generate ConditionalPhrase dynamically for filtering the database entries.
For example the user might use "name" field only to filter results. Also the user might want to use "date" field to filter results at the same time. Like these two cases, I have many filtering options. And user may use any combination of those. So when I generate my query with:
_db.tbl.query()->where( _myConditionalPhrase)->toList();

I want to generate "_myConditionalPhrase" object in my code dynamically by checking if individual filtering options are filled by user. Is this possible and if yes how? Can you give an example?

[HamedMasafi]

Hi, there is an example for you:

auto q = _db.tbl.query();
if (some_candidate)
   q = q->where( Table::fieldField() == value);
if (some_other_candidate)
   q = q->where( Table::field2Field() == value2);

//do somthing with q->toList();

If this does not solve your problem let me know

[yusufmk]

Thank you for the reply !
YES. I found this on my own when I was looking for a solution. I figured the difference between setWhere and where functions.
BUT "where" function always use "&&" operator while combining phrases. However, it would be better if I had a way to create more flexible programmatic queries. Here is an example:

The user might want to search for entries that match:
SELECT * FROM TBL WHERE ID=1234 OR ID=6789;

currently I can only combine with AND operator:
SELECT * FROM TBL WHERE ID = 1234 AND ID = 6789;

which doesnt make sense in this case. Because it is impossible.

Also the library should allow me to combine queries like below:

1. select * from tbl where ID=2703828 or ID=8080912 and TestDate > "2020-10-16 22:59:00";
2. select * from tblReports where (ID=2703828 or ID=8080912) and TestDate > "2020-10-16 22:59:00";

These two queries return different results because 2nd one contains parentheses.

Currently I am generating the query so that it only contains AND conditions. Then I filter results in returned QList once again. It is ok in small db. But im afraid it would be very slow on large db.

Looking to hear from you. Regards...

[HamedMasafi]

Ok, you can create ConditionalPhrase separately, eg:

auto p = Table::nameField() == "1";
p ||= Table::lastNameField() == "2";
auto list = _db.tbl.query()->where(p)->toList();

[yusufmk]

This doesn't compile on my code. I'm using Nut version you used in OrmTest repo:
Nut commit hash: Commit 6ce50e2 by Jack Lilhammers, 07/07/2020 05:50 PM
Serializer commit hash: Commit b0194da3 by Miklós Márton, 06/13/2020 12:00 AM

my code is:
ConditionalPhrase cp;
cp ||= Report::patientIdField() == id;

error is: expected primary-expression before ‘=’ token
cp ||= (Report::patientIdField() == id);
^

Maybe you didn't implemented this operator overload in this commit yet? If there is stable version with better features, can you guide me how to use it? For example, can you update OrmTest repo such that it uses updated version of Nut? OrmTest repo helped me a lot to understand how to use this lib.

[HamedMasafi]

Hi @yusufmk
I've tested and that is my mistake to write code without test in comment!
please change it to:

auto p = Table::nameField() == "1";
p = p || Table::lastNameField() == "2";
auto list = _db.tbl.query()->where(p)->toList();

None: New version of nut currently is under development in dev branch is a Qt module, please continue with it, It will be merged into master branch soon.

[yusufmk]

!!! SOLVED !!!
Thank you! This works.