I'm having a bit of trouble wrapping my head around an SQL issue with a custom Element.

One column in my Element's table—for the case of this question, let's call it myplugin_things—has a foreign key pointing to another custom Element's table, myplugin_inventors. So, every Thing in the system has an inventorId column, and every Inventor has a dateOfBirth.

If I wanted to make it possible to sort or filter a Things query by their Inventors' birthdays, I figured I could do the following in the ThingQuery::beforePrepare(…) method:

$this->innerJoin('{{%myplugin_inventors}} inventors', '[[inventors.id]] = [[inventorId]]');

However, this produces the SQL error Duplicate alias: 7 ERROR: table name "inventors" specified more than once.

There's a method called via ElementQuery::prepare(), _applyJoinParams() which seems to expand all the joins set on the Query to the main query and subQuery, and I see evidence of that in both the query and subquery of the raw SQL statement:

INNER JOIN "myplugin_inventors" "inventors" ON "inventors"."id" = "inventorId"
INNER JOIN "myplugin_inventors" "inventors" ON "inventors"."id" = "inventorId"

If I instead apply the join to the query and subquery directly (presumably bypassing the ElementQuery's _applyJoinParams call), I don't get an error:

$this->query->innerJoin('{{%myplugin_inventors}} inventors', '[[inventors.id]] = [[thingId]]');
$this->subQuery->innerJoin('{{%myplugin_inventors}} inventors', '[[inventors.id]] = [[thingId]]');

So, is the regular ElementQuery::innerJoin() applying the join twice? I imagine Yii is also trying to apply the join clauses, as they remain there after Craft is done preparing the query.

Worth clarifying: I already have the $this->joinElementTable(…) call in the beforePrepare step—this join is for a different Element.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.