Hi guys.
I'm new to complex queries with records and I've got a question about how to execute a query based on records when there is no key based relationship between two tables.
Imagine a situation where there are two tables
1. Executed jobs table
2. Hourly rates of employees
In result I need to list executed jobs with their cost. But the join between these tables is based on employee name and timestamp of job fitting into rate of employee at job execution time. Using SQL I could perform a join on multiple fields, something like
SELECT jobs.id, jobs.summary, jobs.employee, jobs.timestamp, (jobs.hours * rates.HourlyRate) as jobCostFROM jobsLEFT JOIN rates ON (jobs.employee = rates.employee AND jobs.timestamp BETWEEN rates.from and rates.to)
How to do that in the queryRecordType world?
I could do a query, and then a loop and for each job record execute a calculation with retrieving rate information. But would result in multiple SQL statements, and I'm guessing there are better ways for it, right?
And just for the sake of it, it's not that I have a small table of 4 jobs and 5 rates for 2 employees This is just an example representation of an obviously larger set of data.
Help will be much appreciated
Discussion posts and replies are publicly visible
If it just for display, one possible solution is to use your SQL query to create a view, and make new record type and make the CDT of your view as source of data. or maybe just use queryEntity if it's possible for your use case.
Hi Eunnel.Thanks for the reply. All your points are valid and would work, but my question is just touching the surface of the problem, and with the full problem behind it, it's impossible to achieve.
In fact what we have is three tables:A - table listing jobsB - table listing job actions, each job action can have a different executor, all with have some execution timestamp with itC - table listing executor wages with a wage value and time where given wage started to be effective
Originally, we are using queryEntity. But this is somehow limitting for us - we want to display a requested number of jobs with all their job actions and cost of them. And you can't put a limit on the jobs and at the same time retrieve a various number of job actions with them. ...or at least that's how I see this. If you create an entity of joined A + B + C, you are putting a row count limit on result of such cartesian multiplication. Hence limiting to ten, you may get two jobs (one with 4 actions, the other with 6 actions) and that's it.
Hence queryRecordType gives a huge benefit of being able to limit on the jobs, and then add related record type data from job actions. So in the end you can say 'give me 10 jobs, with their job actions, regardless how many job actions they have'.
local!testData: a!queryRecordType( recordType: 'recordType!A', fields: { recordType!A.fields.someField, recordType!B.fields.someOtherField, }, relatedRecordData: { a!relatedRecordData( relationship: 'recordType!A.relationships.B', sort: { a!sortInfo( field: 'recordType!B.someOtherField', ascending: true ) } ) }, pagingInfo: a!pagingInfo( startIndex: 1, batchSize: 10, sort: { a!sortInfo( field: 'recordType!A.fields.someTimestampField', ascending: true ) } ) ).data,
And here, correct me if I'm wrong, but records from a CDT, so records without sync enabled, will not allow you do add relationships, right? hence no possibility to add related record data (relatedRecordData inside queryRecordType).
...or at least that's how I'm seeing it. If I'm wrong at any point, feel free to point it out to me.RegardsMichael