WHERE clause translation to Query Builder

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

WHERE clause translation to Query Builder

Postby huber » Fri Dec 08, 2017 12:08 pm

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.

Code: Select all
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,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm

Re: WHERE clause translation to Query Builder

Postby mnaeimi » Fri Dec 08, 2017 5:01 pm

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']))
)
);
Mina @ Servoy
mnaeimi
 
Posts: 26
Joined: Fri Jun 08, 2012 2:48 pm

Re: WHERE clause translation to Query Builder

Postby sean » Fri Dec 08, 2017 11:44 pm

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:
Code: Select all
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)
Software Engineer
Servoy USA
sean
 
Posts: 370
Joined: Mon May 21, 2007 6:26 pm
Location: USA

Re: WHERE clause translation to Query Builder

Postby huber » Sat Dec 09, 2017 12:04 pm

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 wrote: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,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm

Re: WHERE clause translation to Query Builder

Postby huber » Sat Dec 09, 2017 12:12 pm

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 wrote: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:
Code: Select all
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,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 13 guests