[Quick 4.2.4] How to Deal With Extra Data in Relationship Tables?

Communities Quick ORM
1

Is there a way to handle additional data in many-to-many relationship tables in Quick? I have a belongsToMany() relationship and the joining table post_category has some additional fields in there like createdBy, rowOrder, etc... that I’ll need to be able to populate when attaching entities to one another.

I was thinking maybe there was some type of interceptor or implicit method that would be called similar to the preSave() postSave() ones that already exist. If not, maybe having something like a preAttach() or postAttach() might allow for some extra fancy relationship mapping?

Or perhaps I’m going about this all wrong and there’s a better way to deal with situations like this?

Relationship code:

function categories() {
    return belongsToMany( 
       "Category@cms",
       "post_category",
       "postId", // fk 1
       "categoryId", // fk 2
       "id", // post.id
       "id" // category.id
    );
}
2

If you have more data, then you might want to setup a one to many to that pivot table, and then many to one from the other side.

If you need to access the information, use that relationship, if you need to skip it, use the many to many relationship.

Quick is great because you have the option of having multiple relationships formed… I like having relationships that are one to many users, but i can have a relationship which is one to many managers ( users with this role ) or one to many customers (users with a different role) for example.

Hope that helps

2 Likes
3

Very interesting! I hadn’t considered that before. If I created a one-to-many relationship to the pivot table, then the pivot table itself becomes a new entity (e.g. PostCategory). Did I understand correctly?

4

Yes.
It is more work, but it gives you more options.
If you dont need it… maybe it has triggers for created and modified dates, you dont need it right now, so just keep the many to many.

If you do add it, you can have both types of relationships.
It’s nice when you’re doing things like, give me all categories for this post, and you can just do

getInstance( “postCategory” ).where( “postID”, rc.postID ).getCategory().list() or something like that. You dont have to populate the post to get it though.

1 Like
5

Fantastic! Thanks, Gavin. These tips really opened up some new possibilities.

1 Like
6

Finally, you can use a hasManyThrough or belongsToMany relationship to skip over the intermediate entity when needed, giving you the best of both worlds.

2 Likes
7

I just read through the documentation on hasManyThrough and it sounds like that relationship type could also solve my need.

The documentation mentions “This allows you to store additional data”, which is exactly what I want to do. The examples, don’t show the additional data, but I believe what I would do in my case is create a PostCategory entity that would contain the extra data needed.

Then my models would be set up like this:

// Post.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    function categories() {
        return hasManyThrough( [ "postCategory", "category" ] );
    }
    
    function postCategories() {
        return hasMany( "PostCategory" );
    }

}

// category.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    function posts() {
       return hasManyThrough( [ "postCategory", "post" ] );
    }
    
    function postCategories() {
        return hasMany( "PostCategory" );
    }

}

// PostCategory.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    // custom data
    property name="rowOrder";
    property name="createdBy";

    function post() {
        return belongsTo( "Post" );
    }

    function category() {
        return belongsTo( "Category" );
    }

}

Hopefully I’m on the right track now.

1 Like