DB Relationships with where condition

Published 1 month ago by sathyas17

I'm making a webapp where need to display all the products with active price list . I’m struggling to get the relations work with a condition. The below join query gives the exact answer needed

$getDetails  = $this
           ->join('price_list','price_list.id','=','price_list_item.price_list_id')
           ->join('products','products.id','=','price_list_item.item_id')
           ->join('unit_of_measurement','unit_of_measurement.id','=','price_list_item.item_unit_id')
           ->select('products.*','unit_of_measurement.name as unitname','unit_of_measurement.id as unitid','price_list_item.price')
           ->where('price_list.active_status',0)
           ->get();

The relationship code in price_list_item model :

function products()
    {
       return $this->belongsTo('App\Models\Products','item_id')->select('id','name','sfl_code','micro_code','descrption','unit_of_measure');
    }
function unit()
    {
        return $this->belongsTo('App\Models\UnitOfMeasure','item_unit_id')->select('id','name');
    }

function activeprice()
  {
      return $this->belongsTo('App\Models\PriceList','price_list_id')->where('active_status',0)->select('id','title');
  }
$getDetails  = $this->with(array('products', ' activeprice ','unit'))->get();

Can someone help me to find where I am doing the mistake

Best Answer (As Selected By sathyas17)
patrickcarlohickman

Your query with joins is setup to be limited by the active price lists. Your query with relationships is not.

While you've defined a relationship to model your active prices, and you've eager loaded this relationship, you haven't actually limited your query by this relationship.

Your current query will just get all of your price_list_items in your database, as well as their related data. What you're looking to do is to limit this query by your activeprice relationship. You can do this using the has() method.

$getDetails  = $this
    ->with(['products', ' activeprice ','unit'])
    ->has('activeprice')
    ->get();

This will only return price_list_items that have a related activeprice record.

You can read more about querying relationships in the documentation here.

topvillas

Format your code blocks.

sathyas17

Any suggestions !!?

patrickcarlohickman

Your query with joins is setup to be limited by the active price lists. Your query with relationships is not.

While you've defined a relationship to model your active prices, and you've eager loaded this relationship, you haven't actually limited your query by this relationship.

Your current query will just get all of your price_list_items in your database, as well as their related data. What you're looking to do is to limit this query by your activeprice relationship. You can do this using the has() method.

$getDetails  = $this
    ->with(['products', ' activeprice ','unit'])
    ->has('activeprice')
    ->get();

This will only return price_list_items that have a related activeprice record.

You can read more about querying relationships in the documentation here.

sathyas17
$getDetails  = $this
    ->with(['products', ' activeprice ','unit'])
    ->has('activeprice')
    ->get();

It works thank you @patrickcarlohickman . Will go through the documentation

Please sign in or create an account to participate in this conversation.