Add a sublist of OR conditions with Query Builder

[juan.cristobo]

Hi all, I have this (pseudo) code where I build a SQL query string:

Code: Select all

var values = ['value1', 'value2', 'value3'];
var query = "SELECT columnA FROM myTable WHERE columnB = 'someValue' AND (";

values.forEach(
   function(val) {
      query += " columnC = '"+val+"' OR ";
   }
);

query += " 1=1)"; //avoiding syntax error with a trailing 'OR'

I'd like to use Query Builder instead of using a query string, but I don't know how to add the list of OR conditions. Any ideas?

[jdbruijn]

Not tested, but I think it is something like this:

Code: Select all

   /**@type {QBSelect<db:/example_data/person>}*/
   var q = databaseManager.createSelect('db:/example_data/person');
   q.where.add(q.or
                   .add(q.columns.firstname.like('%jos%'))
                   .add(q.columns.firstname.like('%juan%')));


[juan.cristobo]

Thanks Jos... It's ok if you know how many OR conditions you have, but if (in my example code) "values" array is a method parameter with unknown elements, you code doesn't work, I think.

[sovanm]

Hi Juan,

I hope this will help in passing an unknown number of conditions parameters into OR logical condition.

Code: Select all

// This can be obteined from any function
   /**@type {Array<String>}*/
   var projectNames = ['Done ','Locking System', 'Hello'];
   
   /**@type {QBSelect<db:/db/projects>}*/
   var query = databaseManager.createSelect('db:/rfs/projects')
   
   var condition = query.or;
   
   projectNames.forEach(function(val){
      condition.add(query.columns.project_name.like('%' +val+ '%'));
   });
   
   query.result.add(query.columns.project_id)
           .add(query.columns.project_name)
   query.where.add(condition);
   
   var ds = databaseManager.getDataSetByQuery(query,-1)
   application.output(ds.getAsHTML());


I hope this will help.

Thanks

[juan.cristobo]

Thanks, Sova, I'll try it!

[juan.cristobo]

It works, thank!

[rgansevles]

Juan,

In your original question, you did not ask for like-expressions, just fixed values.
In that case it is more simple to use isin:

Code: Select all

   /**@type {Array<String>}*/
   var values = ['A', 'B', 'C'];
   
   var query = datasources.db.example_data.book_nodes.createSelect();
   
   query.result.addPk();
   query.where.add(query.columns.node_id.eq(42))
              .add(query.columns.label_text.isin(values))
   
   var dataset = databaseManager.getDataSetByQuery(query,100)


Rob