[Quick 7] How Do You Update a hasManyThrough Relationship?

Communities Quick ORM
1

What is the optimal way to update an entity that has a hasManyThrough relationship?

Let’s say you have a Post entity that has the following relationship definition:

// Post Entity

/**
 * This has many Categories through PostCategories
 */
function categories() {
    return hasManyThrough( [ "PostCategories", "Category" ] );
}

/**
 * This has many PostCategories
 * note:  this relationship entity contains extra data (like `rowOrder` sorting)
 */
function postCategories() {
    return hasMany( "PostCategory" );
}

The user wants to update a Post and the rc contains a list of categoryId values like this:

// rc scope
{
    "id": "1", // post id
    "categoryId": "2,3,4"
}

If I execute the following code in my handler, I get an exception, “Method does not exist on QueryBuilder [applySetter]”

prc.categories = getInstance( "Category" ).whereIn( "id", rc.categoryId ).get();
prc.post.setCategories( prc.categories );

I also tried saveMany(), but I got “Method does not exist on QueryBuilder [saveMany]”

prc.categories = getInstance( "Category" ).whereIn( "id", rc.categoryId ).get();
prc.post.categories().saveMany( prc.categories );

This leads me to believe that you can not persist relational data via the hasManyThrough relationship.

The only alternative I can think of is to manually loop through the rc.categoryId list, look for an existing PostCategory entity, and either create or update it. Additionally, I would then need to look for any PostCategory entities that aren’t in the list of rc.categoryId and delete them.

This workaround feels like a lot of manual work that an ORM should take care of automatically. Surely I must be tackling this problem all wrong. Is there a better and more “Quick” way to handle updating relationships like this?

Note: I don’t want to overwrite all PostCategory entities with every save, because there are columns in the post_category table, like rowOrder that needs to be preserved.

2

I made some progress writing a private handler method to process relationships. The code was inspired by generic relationship data mapping code I wrote back in the day before I started using Quick.

The process is pretty straightforward:

  1. Get a collection of current relationship entities. (i.e. PostCategory)
  2. Get a collection of desired relationship target entities. (i.e. Category)
  3. Loop through the desired relationship targets and create relationship entities as needed.
  4. Once complete, filter the collection of original relationship entities and remove anything that doesn’t exist in the targeted entity list.
private function saveRelationships( event, rc, prc ) {

    // PostCategories
    // Get the current relationships
    prc.oldRelationships = prc.post.postCategories().get();

    // Get Our desired target categories (this is a good place to force parents)
    prc.categories = getInstance( "Category" )
        .whereIn( "id", rc.categoryId )
        .forceParents() // scope to recursively add parent categories
        .get();

    // loop through each catgeory
    prc.categories.each( function( category ) {

        // look for an existing relationship
        // If we find a match, this is where we could update the relationship
        // for this case, we just return
        if ( prc.oldRelationships.find( function( item ) {
            return item.getCategoryId() == category.getId();
        } ) ) {
            // no need to re-save it. the relationship already exists
            return;
        // else: we need to add the relationship
        } else {
            prc.post.postCategories().create( {
                "categoryId": category.getId()
            } );
        }

    } );

    // assert: we've added all of the new relationships. Now we need to remove any that were removed
    prc.oldRelationships.filter( function( item ) {
        return !prc.categories.find( function( category ) {
            return category.getId() == item.getCategoryId();
        } );
    } ).each( function( item ) {
        item.delete();
    } );

}

Assuming there isn’t already a way to do this in Quick, my next step will be to figure out if there’s a way to add some of this logic to a BaseQuickEntity class for dynamic many-to-many relationship processing.

3

Personally, I would probably elect to have a custom setter for setCategories in Post that handles the relationship assurance. Something like:

function setCategories( array categories ){

   if( isNull( arguments.categories ) ){
		getInstance( "Category" )
			.where( "FK_post", getId() )
			.deleteAll();
		return this;
   }

   var categoryIds = arguments.categories.map( ( category ) => isSimpleValue( category ) ? category : category.getId() );

   getInstance( "Category" )
		.where( "FK_post", getId() )
        .whereNotIn( "id", categoryIds )
		.deleteAll();

	getInstance( "Category" )
			.whereIn( "id", categoryIds )
			.whereNotIn(
				"id",
				this.postCategories().get().map( ( pc ) => pc.getCategoryId() )
			)
			.get()
			.each(
				( category ) => this.postCategories()
							.create( {
								"categoryId": category.getId()
							} )
			);

	return this;

}
1 Like
4

@jclausen I like the idea of having the custom setter in the Post entity, like in your example.

Did you mean to deleteAll() instances of Category? Or did you mean PostCategory, since that is the joining entity between Post and Category.

5

I think you want PostCategory, but I was looking at your example above and it seemed like it was deleting actual categories.

1 Like