Add Relationship result to where query

Question

Can anyone help, I'm out of ideas.

I have this code:

$item = ItemsBrandOitb::select('ItmsGrpCod')->where('Brand', '=', $brand)->first();

return ItemsOitm::where('Country', $country)
            ->where('OnHand', '>', 0)
            ->where('ItmsGrpCod','=', $item->ItmsGrpCod)
            ->with([
               'price' => function($q) use ($country){
                  return $q->where('Country', $country);
               },
               'stock'=> function($q) use ($country){
                   return $q->where('Country', $country);
               },
               'brand' => function($q) use ($brand){
                   return $q->whereHas('Brand', '=', $brand);
               }
           ]
       )->groupBy('U_GeralRef')->orderBy('ColectionDate', 'desc')->get();

Does anyone know how to put this query:

$item = ItemsBrandOitb::select('ItmsGrpCod')->where('Brand', '=', $brand)->first();

//here:

->where('ItmsGrpCod','=', $item->ItmsGrpCod)

So I can have only one database query?

Thanks

if you share the databse structure i will write the join query — ManojKiran A, Apr 02 '19 at 12:19

score 0 · Answer 1 · answered Apr 02 '19 at 12:43

Try something like this:

return ItemsOitm::where('Country', $country)
        ->where('OnHand', '>', 0)
        ->where('ItmsGrpCod', function ($query) use ($brand) {
            $query->from('TABLE_NAME')
                ->where('Brand', '=', $brand);
        })
        ->with([

                'price' => function( $q) use ($country){ 
                    return $q->where('Country', $country);
                },
                'stock'= > function( $q) use ($country){ 
                    return $q->where('Country', $country);
                },
                'brand' => function( $q) use ($brand){ 
                    return $q->whereHas('Brand', '=', $brand);
                }
            ]
        )->groupBy('U_GeralRef')->orderBy('ColectionDate', 'desc')->get();

That is almos what I need: but I need to have a select in the subquery, and when I do it, Laravel throwns an error — Ricardo Simão, Apr 02 '19 at 15:05

score 0 · Answer 2 · answered Apr 02 '19 at 16:04

Nailed it:

return ItemsOitm::where('Country', $country)
            ->where('OnHand', '>', 0)
            ->where('ItmsGrpCod', function ($query) use ($brand) {
                $query->select('ItmsGrpCod')->from('items_brand_oitbs')
                    ->where('Brand', $brand);
            })
            ->with([
                    'price' => function($q) use ($country){
                        return $q->where('Country', $country);
                    },
                    'stock'=> function($q) use ($country){
                        return $q->where('Country', $country);
                    }
                ]
            )->groupBy('U_GeralRef')->orderBy('ColectionDate', 'desc')->get();

Had to remove the last eager loading (Brand) I dont need it because I'm already geting the value in the sub query

Thanks everyone who helped. Cheers!

Add Relationship result to where query

2 Answers2