Add Relationship result to where query

Simply use Orwhere and joined as like below.

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

What are you trying to achieve?

In this case you have to define foreign key in your model method , where you are defining the relationship , I recommend you to visit this link : https://laravel.com/docs/5.8/eloquent-relationships

@CHRISIDAKWO - What I have in mind is to replace the first query result ($item) and try to put it in the second where of the second query.

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

Because now I have two DB queries. I'm tying to get the value of the ItmsGrpCod in the Brand relationship, which I got in the first query

You will need a join for that.

Join the second table and then have the where condition for the second table.

@FTIERSCH - Thanks. Can you elaborate an example, I'm really lost here.....

->join('items_brand_oitb', 'items_oitm.ItemsGrpCod', '=', 'items_brand_oitb.ItemsGrpCod')
->where('items_brand_oitb.brand', '=', $brand)

Something like that. But I'm pretty sure you could optimize your database to achieve that easier but I can't really understand the data and what it's supposed to be doing :) And also depending on your data two queries might actually be faster than one query with a join in it.

Almost there

I have:

return ItemsOitm::where('Country', $country)
            ->where('OnHand', '>', 0)
            ->where('ItmsGrpCod', function ($query) use ($brand) {
                $query->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);
               },
               'brand' => function($q) use ($brand){
                   return $q->whereHas('Brand', '=', $brand);
               }
           ]
       )->groupBy('U_GeralRef')->orderBy('ColectionDate', 'desc')->get();

which produces the following SQL

select * from `items_oitms` where `Country` = "DE" and `OnHand` > 0 and `ItmsGrpCod` = (select * from `items_brand_oitbs` where `Brand` = "AH") group by `U_GeralRef` order by `ColectionDate` desc

But no result is shown because I need to have:

select * from `items_oitms` where `Country` = "DE" and `OnHand` > 0 and `ItmsGrpCod` = (select ItmsGrpCod from `items_brand_oitbs` where `Brand` = "AH") group by `U_GeralRef` order by `ColectionDate` desc

I´ve tried:

 ->where('ItmsGrpCod', function ($query) use ($brand) {
                $query->select('ItmsGrpCod')->from('items_brand_oitbs')
                    ->where('Brand', $brand);
            })

But Laravel throws:

Argument 2 passed to Illuminate\Database\Eloquent\Builder::whereHas() must be an instance of Closure or null, string given, called in....

Any idea? Thanks

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 dónt need it because I'm already geting the value in the sub query

Thanks everyone who helped. Cheers!