QB statement with an OR in the JOIN condition

Classic Servoy
1

I have a SELECT with an INNER JOIN using an OR in the JOIN condition. I can’t find a way to specify that as QB builder statement. But I’m sure there is a way to do so.

var query = "SELECT\
			aram.access_right_group_id,\
			aram.action_menu_name,\
			aram.form_name\
		FROM\
			action_menu_access_rights aram\
			INNER JOIN menus m ON m.selection_form = aram.form_name\
				OR m.detail_form = aram.form_name\
		WHERE\
			aram.access_right_group_id = " + scopes.globals.acrAccessRightGroupId + "\
			AND m.id = " + scopes.globals.acrAccessRightMenuId;

As with .add(query.or … I tried the same for a join

var query = datasources.db.hades.action_menu_access_rights.createSelect();
		var amar = query.columns;
		query.result
			.addPk();
		/** @type {QBJoin<db:/hades/menus>} */
		var join = query.joins.add('db:/hades/menus', JSRelation.INNER_JOIN);
		var m = join.columns;
		join.on
			.add(join.or
				.add(amar.form_name.eq(m.selection_form))
				.add(amar.form_name.eq(m.detail_form)));
		query.where
			.add(amar.access_right_group_id.eq(scopes.globals.acrAccessRightGroupId))
			.add(m.id.eq(scopes.globals.acrAccessRightMenuId));

but it’s not correct. Anyone can help with the correct QB form?

Regards,

2

I don’t think QueryBuilder supports that. It’s also a bit unusual and on some databases will lead to a table scan, because it cannot be optimized. I think you need to rewrite the query to for example two left joins.

3

Thanks, Patrick. I thought that the Query Builder might not support that, so rewriting the statement will be the solution, as you mention.

patrick:
I don’t think QueryBuilder supports that. It’s also a bit unusual and on some databases will lead to a table scan, because it cannot be optimized. I think you need to rewrite the query to for example two left joins.

Regards,