1.2.0 changes ManyToMany/HasOneThrough join SQL #1007
Description
Postgres 10.4
Objection 1.2.0
Node 10.6.0
Yarn 1.3.2
Objection 1.2.0 generates different SQL than Objection 1.1.10, and this seems by design:
The change, however, causes ManyToManyRelation and HasOneThrough's $relatedQuery and $loadRelated queries to have invalid syntax.
Objection 1.1.10 sample query:
SELECT "policy"."documents".*,
"policy"."policy_attachables"."attachable_type" AS "attachable_type",
"policy"."policy_attachables"."attachable_id" AS "attachable_id",
"policy"."policy_attachables"."policy_type" AS "policy_type",
"policy"."policy_attachables"."policy_id" AS "objectiontmpjoin0"
FROM "policy"."documents"
INNER JOIN "policy"."policy_attachables"
ON "policy"."documents"."id" = "policy"."policy_attachables"."attachable_id"
WHERE "policy"."policy_attachables"."policy_id" IN (?)
AND "policy_type" = ?
AND "attachable_type" = ? limit ?Objection 1.2.0 sample query:
SELECT "policy"."documents".*,
"policy"."policy_attachables"."attachable_type" AS "attachable_type",
"policy"."policy_attachables"."attachable_id" AS "attachable_id",
"policy"."policy_attachables"."policy_type" AS "policy_type",
"policy"."policy_attachables"."policy_id" AS "objectiontmpjoin0"
FROM "policy"."documents"
INNER JOIN "policy"."policy_attachables" AS "policy.policy_attachables"
ON "policy"."documents"."id" = "policy"."policy_attachables"."attachable_id"
WHERE "policy"."policy_attachables"."policy_id" IN (?)
AND "policy_type" = ?
AND "attachable_type" = ? limit ?The 1.2.0 version contains invalid syntax and causes this error:
- invalid reference TO FROM-clause entry FOR TABLE "policy_attachables"
Sample relationMapping:
return {
ManyToManyRelation,
modelClass: TermPolicy,
filter: {
attachableType: 'Document',
policyType: 'TermPolicy',
},
beforeInsert(model) {
model.attachableType = 'Document'
model.policyType = 'TermPolicy'
},
join: {
from: `documents.id`,
through: {
from: 'policy.policyAttachables.attachableId',
to: 'policy.policyAttachables.policyId',
},
to: `termPolicies.id`,
},
}
}This change may or may not only affect relations with through - unconfirmed.