QB statement: Combining 2 relations

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: Combining 2 relations

Postby huber » Thu Apr 20, 2017 4:41 pm

I have 3 tables with 2 relations:
persons - persons_access_right_groups - access_right_groups. The cardinality between them is 1:m and m:1.

Is it possible to travel via the 2 relations from table persons to table access_right_groups in a QB statement?

What definition needs {QBJoin <...}?

My query looks (simplified) like
Code: Select all
var query = datasources.db.hades.persons.createSelect();
var p = query.columns;
/** @type {QBJoin<db:/hades/persons_access_right_groups>} */
var join = query.joins.add('persons_to_persons_access_right_groups.persons_access_right_groups_to_access_right_groups', 'arg');
var arg = join.columns;
query.result
   .add(p.id)
   .add(arg.id);
query.where
   .add(p.usr.eq(username))
   .add(p.pwd.eq(password));


The error is:
Code: Select all
Cannot find relation 'persons_to_persons_access_right_groups.persons_access_right_groups_to_access_right_groups'

Regards,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 248
Joined: Mon May 14, 2012 11:31 pm

Re: QB statement: Combining 2 relations

Postby sean » Fri Apr 21, 2017 4:17 pm

Hi Robert,

The nice thing about the query builder is that you don't have to create the joins if you have the relations.
Just reference them directly. You should get code complete :-)

Also you can chain relations together, i.e: q.joins.relation_a.joins.relation_b.columns.some_column

Code: Select all
var query = datasources.db.hades.persons.createSelect();
var groups = query.joins.persons_to_persons_access_right_groups.joins.persons_access_right_groups_to_access_right_groups;

query.result
   .add(query.columns.id)
   .add(groups.columns.id)

query.where
   .add(query.columns.usr.eq(username))
   .add(query.columns.pwd.eq(password));
Software Engineer
Servoy USA
sean
 
Posts: 85
Joined: Mon May 21, 2007 6:26 pm
Location: USA

Re: QB statement: Combining 2 relations

Postby huber » Tue Apr 25, 2017 9:28 am

Hi Sean

Thanks for your tip. I did not notice your proposed notation for (chained) relations in the documentation. I found and used (for one relation) the following notation (as example) and tried to append another relation just by a dot (.) as is possible outside the QB. But that didn't work. Your notation does.
Code: Select all
var join = queryMenuAccessRights.joins.add('menu_access_rights_classifying_menus', 'm');

Thanks and regards,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 248
Joined: Mon May 14, 2012 11:31 pm

Re: QB statement: Combining 2 relations

Postby huber » Tue Apr 25, 2017 3:04 pm

Hi Sean

Just wondering: From looking at your proposal, it looks to me as if the notation
Code: Select all
var m = queryMenuAccessRights.joins.add('menu_access_rights_classifying_menus', 'm');

is the same as
Code: Select all
var m = queryMenuAccessRights.joins.menu_access_rights_classifying_menus';

Regards,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 248
Joined: Mon May 14, 2012 11:31 pm

Re: QB statement: Combining 2 relations

Postby sean » Tue Apr 25, 2017 5:04 pm

Hi Robert,

Yes that's totally correct :-)
Software Engineer
Servoy USA
sean
 
Posts: 85
Joined: Mon May 21, 2007 6:26 pm
Location: USA

Re: QB statement: Combining 2 relations

Postby huber » Thu Apr 27, 2017 10:35 am

Hi Sean

Thank your for the confirmation.

Regards,
sean wrote:Hi Robert,

Yes that's totally correct :-)
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 248
Joined: Mon May 14, 2012 11:31 pm


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 4 guests