SQL Join (Query Builder vs SQL), and what am I missing?RSLVD

Questions and answers regarding general SQL and backend databases

SQL Join (Query Builder vs SQL), and what am I missing?RSLVD

Postby joe26 » Tue Jun 26, 2018 5:43 pm

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.
Last edited by joe26 on Thu Jun 28, 2018 7:08 am, edited 1 time in total.
joe26
 
Posts: 80
Joined: Wed Jun 19, 2013 10:30 pm

Re: SQL Join (Query Builder vs SQL), and what am I missing?

Postby mboegem » Wed Jun 27, 2018 6:50 pm

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.
_____________________
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance SAN Developer

Image
User avatar
mboegem
 
Posts: 1434
Joined: Sun Oct 14, 2007 1:34 pm
Location: Hoofddorp, The Netherlands

Re: SQL Join (Query Builder vs SQL), and what am I missing?

Postby joe26 » Thu Jun 28, 2018 7:07 am

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...
joe26
 
Posts: 80
Joined: Wed Jun 19, 2013 10:30 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 1 guest

cron