WhereHas Constraints Not Working

[MannikJ]

I have the following setup in my app:

<?php

namespace App;

use Awobaz\Compoships\Compoships;
use Illuminate\Database\Eloquent\SoftDeletes;

class Job extends Model
{
    use SoftDeletes,
        Compoships;
    /**
     * Do not use this relation in whereHas queries!
     */
    public function duplicates()
    {
        return $this->hasMany(
            self::class,
            ['name', 'customer_id'],
            ['name', 'customer_id']
        );
    }
}

When I try use the relation inside whereHas, I get an exception:

\App\Job::whereHas('duplicates')->get();

Illuminate\Database\QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'where clause' (SQL: select * from jobs where exists (select * from jobs as laravel_reserved_0 where (0 = jobs.name and 1 = jobs.customer_id) and laravel_reserved_0.deleted_at is null) and jobs.deleted_at is null limit 1)'

The problem here is that the generated SQL is wrong, because obviously there are no columns 0 and 1. But I have no clue where it comes from. And I am quite sure that a query like this has once worked.

If I query the relations via a job instance like below, there is no exception because the resulting SQL is correct:

$job->duplicates()->get(); //  select * from "jobs" where "jobs"."name" = ? and "jobs"."customer_id" = ? and "jobs"."deleted_at" is null

[topclaudy]

@MannikJ Which version of the package are you using?

[MannikJ]

Sorry, v2.1 it was, I guess

Update: Just updated to the latest v2.1.1 but the issue is still there..
Using it in Laravel v8.38.0 with PHP v7.4.16 and MySQL v8.0.20 by the way

@topclaudy Do you have an idea?

[topclaudy]

Sorry, v2.1 it was, I guess

Update: Just updated to the latest v2.1.1 but the issue is still there..
Using it in Laravel v8.38.0 with PHP v7.4.16 and MySQL v8.0.20 by the way

@topclaudy Do you have an idea?

Sorry for the delay. Can you try 2.0.x?

[TheFehr]

I am facing the same problem.
And downgrading to 2.0.x does not work as that version was not yet compatible with laravel/framework 9.x

[TheFehr]

And as was also in the original authors code snippet. I too am trying to have a relationship onto the same class.
With another class it works.

So:

class A extends Model
{
    use compoships;

    public function linkedA()
    {
        return $this->belongsTo(
            A::class,
            ['linked_a_id', 'other_id'],
            ['id', 'other_id']
        );
    }
}

A::whereHas('linkedA');

does not work.

If we add

class B extends Model
{
    use compoships;

    public function a()
    {
        return $this->belongsTo(
            B::class,
            ['a_id', 'other_id'],
            ['id', 'other_id']
        );
    }
}

B::whereHas('a');

it works.

[iveoles]

I can confirm I have the same setup as TheFehr and the issue only presents when the relation is on the same model e.g. a parent/child relationship.

[TheFehr]

As this was a big problem for me I removed this package and added calculated id columns for the pair of id's I needed to match.
It leads to pretty big id's but I now can correctly use laravel relations again.

For anyone curious I used the cantor pairing function.

If you use this approach don't forget to always use the same order for your two id's otherwise it won't work. And don't forget to add indexes for the new calculated id column.