WHEREHAS failing for relations with multiple keys

I have this one-to-one relation with two keys (BELEG and POSITION), which works fine in general:

public function Auftragspositionzusatz2()
{
    return $this->hasOne('App\Auftragspositionzusatz', 'BELEG', 'BELEG')->where('POSITION', $this->POSITION);
}

However, when queurying this via WHEREHAS, it fails to provide any records:

    $belege = Auftragsposition::with('Auftragspositionzusatz2')
    ->whereHas('Auftragspositionzusatz2', function ($query) {
        $query->where('PRODUKTIONWA', '=','1' );
    })
    ->sortable()->paginate(ViewController::itemsPerPage);

In the debug bar I see that at the end it is querying for [POSITION] is null:

select count(*) as aggregate from [HB001AUFTRAGSPOSITION] where cast([DATUMNEUANLAGE] as date) >= '2022-11-01' and exists (select * from [HB001AUFTRAGSPOSITIONZUSATZ] where [HB001AUFTRAGSPOSITION].[BELEG] = [HB001AUFTRAGSPOSITIONZUSATZ].[BELEG] and [PRODUKTIONWA] = '1' and [POSITION] is null)

I do not have this problem, when the model relation is defined by only one key. Is this a known bug? How can I overcome this? Thank you for your help!

itsfg

3 years ago

Best Answer

Level 5

I can imagine that your where('POSITION', $this->POSITION) doesn't work as you expect, because when you're building a query, there's no model loaded, so $this->position is null.

I'm not sure how to solve this though. Maybe if you build a query with a join between the 2 tables, you could specify a where condition between the 2 position columns.

Or try the whereColumn() method instead of where('POSITION', $this->POSITION) in your relation ?

1 like

CarlMahnke

3 years ago

Level 1

@itsfg Thank you for your input. I finally solved the problem with a join inside the WHEREHAS function as you proposed! The additional key was specified in the WHERERAW statement.

        ->whereHas('Auftragsposition', function ($query) {
            $query->join('HB001AUFTRAGSPOSITIONZUSATZ', 'HB001AUFTRAGSPOSITION.BELEG', '=', 'HB001AUFTRAGSPOSITIONZUSATZ.BELEG')
                ->join('HB001ARTIKELZUSATZ', 'HB001AUFTRAGSPOSITION.ARTIKEL', '=', 'HB001ARTIKELZUSATZ.ARTIKEL')
                ->whereRaw('HB001AUFTRAGSPOSITION.POSITION = HB001AUFTRAGSPOSITIONZUSATZ.POSITION')
                ->where('HB001AUFTRAGSPOSITIONZUSATZ.PRODUKTIONWA',1)
                ->where('HB001ARTIKELZUSATZ.KOMMIBEREICH','!=',10);
        })

1 like

Please or to participate in this conversation.