I am experimenting with Quick’s HasManyThrough()
relationship, and I’m having trouble ensuring my queries are being executed efficiently and returning the JSON structure I want.
Here’s the database schema:
Relationship Breakdown:
I have a Task
which hasMany TaskLocation
entities. The TaskLocation
belongsTo a Task
, Location
, and Progress
.
I’d like to query an array of Task
entities that includes the nested array of TaskLocation
entities with nested structs containing the Location
and Progress
. In other words, I’d like to generate JSON output that looks like this (similar to the database schema design):
[
// Task
{
"id": 1,
"name": "Just a Normal Task",
// TaskLocation
"taskLocations": [
{
// Location
"location": {
"id": 1,
"name": "Sacramento"
},
// Progress
"progress": {
"id": 3,
"name": "Complete"
}
},
{
// Location
"location": {
"id": 2,
"name": "San Francisco"
},
// Progress
"progress": {
"id": 2,
"name": "Pending"
}
}
]
},
// etc.
]
I’ve experimented with a few different ways of getting the data, but each way I try generates unnecessary queries. For example, the following code generates 8 queries:
prc.result = getInstance( "Task" )
.with( [ "taskLocations", "taskLocations.location", "taskLocations.progress" ] )
.asMemento( "taskLocations,taskLocations.location,taskLocations.progress" )
.get();
writeDump( serializeJson( prc.result ) );abort;
If I change my code, so I utilize the hasManyThrough relationships, Quick only executes 4 queries, which is much better:
prc.result = getInstance( "Task" )
.with( [ "taskLocations", "locations", "progress" ] )
.asMemento( "taskLocations,locations,progress" )
.get();
However, my resulting memento doesn’t have the structure I want:
[
// Task
{
"id": 1,
"name": "Just a Normal Task",
// locations
"locations": [
{
"id": 1,
"name": "Sacramento"
},
{
"id": 2,
"name": "San Francisco"
}
],
// progress
"progress": [
{
"id": 3,
"name": "Complete"
},
{
"id": 1,
"name": "Pending"
}
],
// taskLocations
"taskLocations": [
{
//... foreign keys keys only
}
]
},
// etc.
]
Is it possible to generate the JSON output I want with the more efficient query execution I get from the HasManyThrough approach? Perhaps the solution lies with the asMemento()
function?
For reference, here are simplified versions of each entity:
// Task
component
extends="quick.models.BaseEntity"
accessors="true"
{
property name="id";
property name="name" sqltype="varchar";
// RELATIONSHIPS
function taskLocations() {
return hasMany( "TaskLocation", "taskId" );
}
// experment: hasManyThrough
function locations() {
return hasManyThrough( [ "taskLocations", "location" ] );
}
function progress() {
return hasManyThrough( [ "taskLocations", "progress" ] );
}
}
// TaskLocation
component
extends="quick.models.BaseEntity"
accessors="true"
{
property name="id";
property name="progressId" sqlType="integer";
property name="taskId" sqlType="integer";
property name="locationId" sqlType="integer";
// RELATIONSHIPS
function progress() {
return belongsTo( "Progress", "progressId" );
}
function task() {
return belongsTo( "Task", "taskId" );
}
function location() {
return belongsTo( "Location", "locationId" );
}
}