[Quick 4.2.4] Having Difficulty With HasManyThrough() Relationship, Efficient Queries, and Desired JSON Output

[DaveL]

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" );
    }

}

[DaveL]

I solved the problem by redefining the relationship and eagerly loading the sub-relationships from the Task entity:

Original:

// Task entity relationships
function taskLocations() {
    return hasMany( "TaskLocation", "taskId" );
}

Modified to:

function taskLocations() {
    return hasMany( "TaskLocation", "taskId" ).with( [ "location", "progress" ] );
}

The final retrieval statement looks like this:

prc.result = getInstance( "Task" )
    .with( [ "taskLocations" ] )
    .asMemento( "taskLocations,taskLocations.location,taskLocations.progress" ) 
    .get();

I’m not sure why my original statement didn’t work, and it might be an issue with the current version of Quick. However, the workaround does what I need it to. Hopefully, the above will help others.

Update: Possible Solution 2

Another way you can accomplish the same thing is to include the with() statement where you would typically constrain relationships (doc link). This process allows you to keep the original entity definition:

// Task entity relationships
function taskLocations() {
    return hasMany( "TaskLocation", "taskId" );
}

The final retrieval statement would look like this:

prc.result = getInstance( "Task" )
    .with( [ { "taskLocations": function( q1 ) {
                q1.with( [ "location", "progress" ] );
            } } ] )
    .asMemento( "taskLocations,taskLocations.location,taskLocations.progress" ) 
    .get();