QB statement with an OR in the JOIN condition

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

QB statement with an OR in the JOIN condition

Postby huber » Tue Apr 18, 2017 3:08 pm

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.

Code: Select all
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
Code: Select all
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,
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: QB statement with an OR in the JOIN condition

Postby patrick » Tue Apr 18, 2017 6:33 pm

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.
Patrick Ruhsert
Servoy DACH
patrick
 
Posts: 3703
Joined: Wed Jun 11, 2003 10:33 am
Location: Munich, Germany

Re: QB statement with an OR in the JOIN condition

Postby huber » Wed Apr 19, 2017 11:34 am

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

patrick wrote: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,
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: Google [Bot] and 15 guests