Ambiguous column name after extending pivot query

[vdlp-mw]

• Laravel Version: v6.20.19
• PHP Version: 7.4
• Database Driver & Version: mysql 5.5.5

Description:

We ran into a use case where it is necessary to extend the pivot query of a model. To do this \Illuminate\Database\Eloquent\Relations\Concerns\InteractsWithPivotTable::newPivotQuery is overridden as follows;

public function newPivotQuery()
{
   $query = parent::newPivotQuery();

   // add relation's conditions and scopes to the query
   $this->addDefinedConstraintsToQuery($query);

   $related = $this->getRelated();

   return $query
   	->join($related->getTable(), $related->getQualifiedKeyName(), '=', $this->getOtherKey())
   	->select($this->getTable().'.*');
}

The default pivot query where clauses don't use qualified column names. In some cases this may result in an ambiguous column name error. For example:

# SQLSTATE[23000]: Integrity constraint violation: 1052
# Column 'rule_id' in WHERE clause is ambiguous

SELECT `cmp_rule`.*
FROM   `cmp_rule`
       RIGHT JOIN `reg_rule`
               ON `reg_rule`.`rule_id` = `cmp_rule`.`rule_id`
WHERE  `base_id` = 1
       AND `rule_id` IN ( 5 ) 

After looking into the internals, it appears that InteractsWithPivotTable::newPivotQuery does not use a qualified column names. Other parts of the laravel internals do use qualified column names.

Applying the following changes on 6.x LTS resulted in a working query.

// Illuminate\Database\Eloquent\Relations\Concerns\InteractsWithPivotTable.php:432
- $query->whereIn($this->relatedPivotKey, (array) $ids);
+ $query->whereIn($this->getQualifiedRelatedPivotKeyName(), (array) $ids);

// Illuminate\Database\Eloquent\Relations\Concerns\InteractsWithPivotTable.php:548
- return $query->where($this->foreignPivotKey, $this->parent->{$this->parentKey});
+ return $query->where($this->getQualifiedForeignPivotKeyName(), $this->parent->{$this->parentKey});

Resulting query:

SELECT `cmp_rule`.*
FROM   `cmp_rule`
       RIGHT JOIN `reg_rule`
               ON `reg_rule`.`rule_id` = `cmp_rule`.`rule_id`
WHERE  `cmp_rule`.`base_id` = 1
       AND `cmp_rule`.`rule_id` IN ( 5 ) 

Is there a particular reason that qualified column names are not used in InteractsWithPivotTable or can we safely create a PR for this change?

A search through recent issues and PRs shows that column names are being replaced with qualified column names in other places too. https://github.com/laravel/framework/search?q=qualified+column&type=issues

[driesvints]

Since you're overriding part of the framework we can't really help you, sorry.

However, I do agree with you that it seems odd that the qualified column names aren't applied. Feel free to attempt a PR. If you do, make sure that all tests pass. Thanks

[vdlp-mw]

Thanks for your feedback @driesvints!

Yes our use case does indeed override internals. That's why the primary question was focused on why FQN weren't used here. Anyway I opened a PR and all tests are passing. I did have to change two existing tests because they were expecting the column name instead of a FQN. Not sure if this is a problem.

#36720.