QB statement: Combining 2 relations

Classic Servoy
1

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

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:```
Cannot find relation ‘persons_to_persons_access_right_groups.persons_access_right_groups_to_access_right_groups’

Regards,
2

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

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));
3

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.

var join = queryMenuAccessRights.joins.add('menu_access_rights_classifying_menus', 'm');

Thanks and regards,

4

Hi Sean

Just wondering: From looking at your proposal, it looks to me as if the notation

var m = queryMenuAccessRights.joins.add('menu_access_rights_classifying_menus', 'm');

is the same as

var m = queryMenuAccessRights.joins.menu_access_rights_classifying_menus';

Regards,

5

Hi Robert,

Yes that’s totally correct :-)

6

Hi Sean

Thank your for the confirmation.

Regards,

sean:
Hi Robert,

Yes that’s totally correct :-)