Eager loading a belongsToMany relationship fails on Postgres

[sethstone]

I came across several issues while working through the Up and Running with Quick workshop and have documented those issues here. Most of them have a workaround that can be applied in your project, but I did run into an issue that doesn't seem solvable outside of a framework change.

The issue occurs whenever you try to eager load a belongsToMany relationship. This is part of Step 9 of the workshop where we add tags to the with array when initially loading posts.

From /handlers/Posts.cfc

prc.posts = getInstance( "Post" )
	.with( [ "author", "tags" ] )
	.latest()
	.get();

The error generated is:

ERROR: operator does not exist: integer = character varying Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 184

💡 Note: In my experience this error came up very frequently when working with Postgres, but required a variety of solutions. See the issue I referenced above for details. The issue I'm reporting here is for only one specific cause of this error.

In this case the problem seems to occur when calling getKeys() from BelongsToMany.addEagerConstraints(). The addEagerConstraints method is what updates the WHERE clause with the needed constraints to load all tags related to all loaded posts.

Here you can see a dump of the SQL before and after the function processes the getKeys() result: You can see that all the bindings for postId have cfsqltype set to "CF_SQL_VARCHAR". This will trigger qb to quote the values which Postgres doesn't like (hence the error above).

I think this issue is possibly being self-inflicted in BaseRelationship.getKeys() when attempting to generate a unique array of primary key values arrays:

Fortunately, I think this can be fixed simply be removing the toList() and corresponding map( listToArray() ). I confirmed that the unique() method would handle de-duplication of an array of arrays as well as it does an array of strings. I will submit a PR for these changes next along with corresponding test case.

I'm still curious as to when and where this behavior was introduced in Postgres that has made it so much more pickier than MySQL. FWIW, I've tested this in PostgreSQL versions 13.4, 12.9 and 11.0 and had the same behavior in all cases. I'm not very familiar with Postgres at all, so If there's a flag or configuration that adjusts this strictness behavior I'm not aware of it.