one-of-many relationship query error.

[eusonlito]

• Laravel Version: v8.42.0
• PHP Version: 7.4.18
• Database Driver & Version: MySQL 8.0

Description:

Related to merged PR #37362

Having this tables:

Schema::create('exchange', function (Blueprint $table) {
    $table->id();

    $table->unsignedFloat('exchange', 19, 12);

    $table->timestamps();

    $table->unsignedBigInteger('platform_id');
    $table->unsignedBigInteger('product_id');
});

Schema::create('product', function (Blueprint $table) {
    $table->id();

    $table->string('code')->index();
    $table->string('name');

    $table->unsignedBigInteger('platform_id');
});

Product > Exchange relation is set as:

/**
 * @return \Illuminate\Database\Eloquent\Relations\HasOne
 */
public function exchange(): HasOne
{
    return $this->hasOne(Exchange::class, 'product_id');
}

My code is:

\App\Models\Product::with(['exchange'])->limit(1)->get();

select * from `exchange` where `exchange`.`product_id` in (1)

After adding ofMany to hasOne

/**
 * @return \Illuminate\Database\Eloquent\Relations\HasOne
 */
public function exchange(): HasOne
{
    return $this->hasOne(ExchangeModel::class, static::$foreign)->ofMany();
}

same previous code generate an SQL error:

Illuminate\Database\QueryException
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'exchange.product_id' in where clause is ambiguous (SQL: select * from `exchange` inner join (select MAX(id) as id, `exchange`.`product_id` from `exchange` group by `exchange`.`product_id`) as `exchange` on `exchange`.`id` = `exchange`.`id` and `exchange`.`product_id` = `exchange`.`product_id` where `exchange`.`product_id` in (1))

[driesvints]

Ping @cbl

[cbl]

The join alias ist the same as the table name both are exchange. Try setting a different alias:

->ofMany(relation: 'latest_exchange');

Or

->ofMany('id', 'max', 'latest_exchange');

[eusonlito]

@cbl it works perfect.

Maybe this can be a common "error". What about to rename default alias as table_name + _join|_related or something similar to avoid issues like this?

Thanks!