WHERE clause translation to Query Builder

Classic Servoy
1

Hi

I have a WHERE clause in a SQL statement looking like the following and have difficulties to translate it to Query Builder as I don’t get twice two ANDs combined with the OR. Any hints are welcome.

WHERE
	((cm.class_level_number = 4
	AND cm.class_sign IN ('ma','mb','mc','na','nb'))
		OR (cm.class_level_number = 3
		AND cm.class_sign IN ('fa', 'fb')))

Regards,

2

You can try something like following:

var query = databaseManager.createSelect(datasources);
query.result.addPk();
query.where.add(
query.or
.add(
query.and
.add(query.columns.class_level_number.eq(4))
.add(query.columns.class_sign.isin([‘ma’,‘mb’,‘mc’,‘na’,‘nb’]))
)
.add(
query.and
.add(query.columns.class_level_number.eq(3))
.add(query.column.class_sign.eq([‘fa’, ‘fb’]))
)
);

3

Hi Robert,

I recommend you catch-up on the query builder webinar

That’s a pro tip from Mina.
But sometimes it’s the code-chaining / nesting that can make it tricky until you get used to looking at it.

Remember that you can always break it up to make it more clear.
And remember that query.or and query.and do not affect the where clause; they only return a new condition each call
Nothing is added until you can query.where.add(condition)

Try this if you are having trouble to read it:

var and1 = query.and
    .add(query.columns.class_level_number.eq(4))
    .add(query.columns.class_sign.isin(['ma','mb','mc','na','nb']))
var and2 = query.and
    .add(query.columns.class_level_number.eq(3))
    .add(query.column.class_sign.eq(['fa', 'fb']))
var or = query.or
    .add(and1)
    .add(and2)
query.where.add(or)
4

Hi Mina

Thanks a lot for the solution, exactly what I was looking for. Now I see the after the first .add the query.or is needed and I didn’t realise that.
Just if someone looks (later on) at the code, there are two little things to adjust. The query.column.class_sign.eq([‘fa’, ‘fb’]) should of course read query.columns.class_sign.isin([‘fa’, ‘fb’]). And as a typo the s at columns is missing in the code.

mnaeimi:
You can try something like following:

var query = databaseManager.createSelect(datasources);
query.result.addPk();
query.where.add(
query.or
.add(
query.and
.add(query.columns.class_level_number.eq(4))
.add(query.columns.class_sign.isin([‘ma’,‘mb’,‘mc’,‘na’,‘nb’]))
)
.add(
query.and
.add(query.columns.class_level_number.eq(3))
.add(query.column.class_sign.eq([‘fa’, ‘fb’]))
)
);

Best regards,

5

Hi Sean

Thanks for pointing me to the video. I was looking for it after the webinar but was obviously too early. I will look at it again as a learning aid. By the way I enjoyed it when you presented it and it gave me also some certainty that query builder is not a thing of the past. As we are in the process of translating our SQL statements to QB this is most important, as it’s quite a lot of work to translate statement for statement and test it.

Thanks for the very nice solution you present by breaking up the AND and OR parts. This is even more elegant as my WHERE clause contains actually more nested conditions as shown.

sean:
Hi Robert,

I recommend you catch-up on the query builder webinar

That’s a pro tip from Mina.
But sometimes it’s the code-chaining / nesting that can make it tricky until you get used to looking at it.

Remember that you can always break it up to make it more clear.
And remember that query.or and query.and do not affect the where clause; they only return a new condition each call
Nothing is added until you can query.where.add(condition)

Try this if you are having trouble to read it:

var and1 = query.and

.add(query.columns.class_level_number.eq(4))
.add(query.columns.class_sign.isin(['ma','mb','mc','na','nb']))

var and2 = query.and
.add(query.columns.class_level_number.eq(3))
.add(query.column.class_sign.eq([‘fa’, ‘fb’]))
var or = query.or
.add(and1)
.add(and2)
query.where.add(or)

Best regards,