QB statement

Classic Servoy
1

Having for example the following QB statement, I am wondering why

  • the first table does not need a /** @type {QBJoindb:/hades/profile_positions} */ statement (the other do)
  • if there is a way to use the relation (or table) alias instead of defining a prefix for the query.columns (in this example the same as for the table) ?
  • I noticed when using a relation for a join it’s always done as a LEFT OUTER JOIN, correct?
var query = datasources.db.hades.profile_positions.createSelect();
	var pp = query.columns;
	query.result
		.addPk();
	/** @type {QBJoin<db:/hades/persons>} */
	var join1 = query.joins.add('profile_positions_part_of_an_educational_description_for_persons', 'p');
	var p = join1.columns;
	/** @type {QBJoin<db:/hades/class_members>} */
	var join2 = query.joins.add('_profile_positions_a_party_to_class_members', 'cm');
	var cm = join2.columns;
	query.where
		.add(pp.period_school_year.eq(scopes.globals.plcSelectedPeriod.split(' ')[1]))
		.add(pp.period_fraction_name.eq(scopes.globals.plcSelectedPeriod.split(' ')[0]))
		.add(pp.profile_definition_level_number.eq(scopes.globals.plcSelectedClass.split(' ')[0]))
		.add(cm.class_sign.eq(scopes.globals.plcSelectedClass.split(' ')[3]))
		.add(pp.profile_definition_subject_code.eq(scopes.globals.plcSelectedSubjectCode))
		.add(pp.profile_definition_subject_type_code.eq(scopes.globals.plcSelectedSubjectTypeCode));
	query.sort
		.add(p.name.asc)
		.add(p.first_name.asc);
	
	var dataset = databaseManager.getDataSetByQuery(query, -1);	
	foundset.loadRecords(dataset);

Thanks for clarifying.

Regards,

2

Hi,

  1. The first table doesn’t need a type definition because its created by datasources.db.hades…createSelect() in this case servoy knows the datasource.
    For the datasource/relations the way they are added in the sample is that its based on a string (datasource/relation). If you like to add them this way it is needed to use the @type.

  2. You can also directly use servoy relation, if you do its not needed to use @type.

	var query = datasources.db.svy_sample.contacts.createSelect();
	var join1 = query.joins.contacts_to_addresses;
	
	query.result.addPk();
	query.result.add(query.columns.name_last);
	query.result.add(join1.columns.zipcode);
	
	query.sort.add(join1.columns.zipcode.asc);
	
	var result = databaseManager.getDataSetByQuery(query,-1);
  1. You can use other join types.
    You can do that by setting it as a second argument the alias will then be the third argument.
query.joins.add('profile_positions_part_of_an_educational_description_for_persons', JSRelation.INNER_JOIN, 'p');

Currently this types are available:

	JSRelation.FULL_JOIN
	JSRelation.INNER_JOIN
	JSRelation.LEFT_OUTER_JOIN
	JSRelation.RIGHT_OUTER_JOIN
3

Hi rvanveen

Thanks for your info. I have also hoped on a solution to use the table alias as column alias (as with standard SQL statements), to avoid the query or join variables. Yes I know if I do write them explizit with every column I don’t need the variable, but that is a bit confusing. The table alias seems only to be used internally (can be seen when outputting the QB Select or in debugger).

Regards,

4

You can’t expect the compiler to offer code completion on some string you set somewhere (like ‘myAlias’). But how is the effort of setting an alias different from pulling the join into a variable? If I understand you right, you want

query.joins.add('profile_positions_part_of_an_educational_description_for_persons', JSRelation.INNER_JOIN, 'p');

and then for example do

query.sort.add(p.columns.zipcode.asc);

??

That won’t work because the compiler can’t interpret that ‘p’…

But how is that much different from

var p = query.joins.add('profile_positions_part_of_an_educational_description_for_persons', JSRelation.INNER_JOIN)

and then

query.sort.add(p.columns.zipcode.asc);

And:

The table alias seems only to be used internally (can be seen when outputting the QB Select or in debugger)

A bit more important, the alias is used in the actual query!

5

Hi Patrick

patrick:
You can’t expect the compiler to offer code completion on some string you set somewhere (like ‘myAlias’). But how is the effort of setting an alias different from pulling the join into a variable? If I understand you right, you want

query.joins.add('profile_positions_part_of_an_educational_description_for_persons', JSRelation.INNER_JOIN, 'p');

and then for example do

query.sort.add(p.columns.zipcode.asc);

??

That won’t work because the compiler can’t interpret that ‘p’…

But how is that much different from

var p = query.joins.add('profile_positions_part_of_an_educational_description_for_persons', JSRelation.INNER_JOIN)

and then

query.sort.add(p.columns.zipcode.asc);

As you say it’s not that much different, I can well live with it. Just wanted to make sure I don’t miss an even easier solution :-)

patrick:
And:

The table alias seems only to be used internally (can be seen when outputting the QB Select or in debugger)

A bit more important, the alias is used in the actual query!

Yeah, that’s what I meant of course, it’s the relevant part.
Thanks for your reply.

Regards,