Servoy Forum

Hey all,

I'm attempting to get an address from a UUID in another table.
It was part of a pretty complex query in Query Builder, and I use this for migration to other databases without much ado.

I do get a foundset but I do not really get the join, but I should at least have at least the job_number and the city from the matching record in the addresses table.

EDIT: Is this because foundsets are ONLY single tables with relations pulling the other data from tables as needed? Whereas SQL queries and Datasets are not strictly adhering to a single table?

**works SQL Editor Servoy 7.4.8**
select * from jobs
join addresses
on jobs.ship_to=addresses.address_id
where jobs.job_id = 'B888886D-ED38-4A5E-964A-350314586990'

**doesn't work**

Code: Select all

/** @type {QBSelect<db:/stsservoy/jobs>} */
var q = databaseManager.createSelect('db:/stsservoy/jobs');   
q.where.add(q.columns.job_id.eq(jobId)); // jobId is a UUID which exists

/** @type {QBJoin<db:/stsservoy/addresses>} */
var r = q.joins.add('db:/stsservoy/addresses');
r.on.add(r.columns.address_id.eq(q.columns.ship_to));

q.result.add(q.columns.job_number);//even if these are left out still get a foundset, but no join with corresponding entry
q.result.add(r.columns.city);

var Q = databaseManager.getFoundSet(q);
application.output('size '+Q.getSize());
var rc = Q.getRecord(1);
   
application.output('city '+rc.city+' '+rc);



Thanks!
--Joe.

joe26 wrote:Is this because foundsets are ONLY single tables with relations pulling the other data from tables as needed? Whereas SQL queries and Datasets are not strictly adhering to a single table?

Correct.
When using a query in order to retrieve particular records in a foundset, you also only need to add the pk column in the result.

So in case you need both, either retrieve the result via databaseManager.getDatasetByQuery, or get the foundset of jobs and retrieve the city via a relationship.
The first one will return data a lot faster, since it doesn't return all columns and doesn't have to do the additional query as a result of using a relation.

Hope that helps.

Thank you, Marc. It does. I have been here before but something just didn't want to give up on joined tables in a foundset. i was *that* close...