Add a sublist of OR conditions with Query Builder

Classic Servoy
1

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

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?

2

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

	/**@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%')));
3

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.

4

Hi Juan,

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

// 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

5

Thanks, Sova, I’ll try it!

6

It works, thank!

7

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:

	/**@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