Back

DB Relationships with where condition


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

Firtzberg replied 2 weeks ago

Hi Sathya,

I guess products should be hasMany instead of belongTo. Dump the generated sql query to see where the difference.

Firtzberg

Sathya replied 2 weeks ago

Join Query Dump

Collection {#388 ▼
  #items: array:1 [▼
    0 => PriceListItem {#389 ▼
      #table: "price_list_item"
      #dates: array:1 [▶]
      #connection: "mysql"
      #primaryKey: "id"
      #keyType: "int"
      +incrementing: true
      #with: []
      #withCount: []
      #perPage: 15
      +exists: true
      +wasRecentlyCreated: false
      #attributes: array:12 [▼
        "id" => 6
        "name" => "product1"
        "sfl_code" => "werwer"
        "micro_code" => "wer"
        "descrption" => "werwer"
        "unit_of_measure" => 1
        "deleted_at" => null
        "created_at" => "2017-11-24 12:01:18"
        "updated_at" => "2017-11-24 12:01:18"
        "unitname" => "KILO GRAMS"
        "unitid" => 1
        "price" => "6.00"
      ]
      #original: array:12 [▼
        "id" => 6
        "name" => "product1"
        "sfl_code" => "werwer"
        "micro_code" => "wer"
        "descrption" => "werwer"
        "unit_of_measure" => 1
        "deleted_at" => null
        "created_at" => "2017-11-24 12:01:18"
        "updated_at" => "2017-11-24 12:01:18"
        "unitname" => "KILO GRAMS"
        "unitid" => 1
        "price" => "6.00"
      ]
      #casts: []
      #dateFormat: null
      #appends: []
      #events: []
      #observables: []
      #relations: []
      #touches: []
      +timestamps: true
      #hidden: []
      #visible: []
      #fillable: []
      #guarded: array:1 [▶]
      #forceDeleting: false
    }
  ]
}

Relation Dump
Has generated two array as it has two records . But only one row is with active price list.

 Collection {#407 ▼
  #items: array:2 [▼
    0 => PriceListItem {#391 ▼
      #table: "price_list_item"
      #dates: array:1 [▶]
      #connection: "mysql"
      #primaryKey: "id"
      #keyType: "int"
      +incrementing: true
      #with: []
      #withCount: []
      #perPage: 15
      +exists: true
      +wasRecentlyCreated: false
      #attributes: array:8 [▶]
      #original: array:8 [▶]
      #casts: []
      #dateFormat: null
      #appends: []
      #events: []
      #observables: []
      #relations: array:3 [▼
        "products" => Products {#409 ▼
          #table: "products"
          #dates: array:1 [▶]
          #connection: "mysql"
          #primaryKey: "id"
          #keyType: "int"
          +incrementing: true
          #with: []
          #withCount: []
          #perPage: 15
          +exists: true
          +wasRecentlyCreated: false
          #attributes: array:6 [▶]
          #original: array:6 [▼
            "id" => 6
            "name" => "product1"
            "sfl_code" => "werwer"
            "micro_code" => "wer"
            "descrption" => "werwer"
            "unit_of_measure" => 1
          ]
          #casts: []
          #dateFormat: null
          #appends: []
          #events: []
          #observables: []
          #relations: []
          #touches: []
          +timestamps: true
          #hidden: []
          #visible: []
          #fillable: []
          #guarded: array:1 [▶]
          #forceDeleting: false
        }
        "activeprice" => PriceList {#412 ▼
          #table: "price_list"
          #dates: array:1 [▶]
          #connection: "mysql"
          #primaryKey: "id"
          #keyType: "int"
          +incrementing: true
          #with: []
          #withCount: []
          #perPage: 15
          +exists: true
          +wasRecentlyCreated: false
          #attributes: array:2 [▼
            "id" => 41
            "title" => "pricelist"
          ]
          #original: array:2 [▶]
          #casts: []
          #dateFormat: null
          #appends: []
          #events: []
          #observables: []
          #relations: []
          #touches: []
          +timestamps: true
          #hidden: []
          #visible: []
          #fillable: []
          #guarded: array:1 [▶]
          #forceDeleting: false
        }
        "unit" => UnitOfMeasure {#418 ▶}
      ]
      #touches: []
      +timestamps: true
      #hidden: []
      #visible: []
      #fillable: []
      #guarded: array:1 [▶]
      #forceDeleting: false
    }
    1 => PriceListItem {#392 ▼
      #table: "price_list_item"
      #dates: array:1 [▶]
      #connection: "mysql"
      #primaryKey: "id"
      #keyType: "int"
      +incrementing: true
      #with: []
      #withCount: []
      #perPage: 15
      +exists: true
      +wasRecentlyCreated: false
      #attributes: array:8 [▶]
      #original: array:8 [▶]
      #casts: []
      #dateFormat: null
      #appends: []
      #events: []
      #observables: []
      #relations: array:3 [▼
        "products" => Products {#408 ▼
          #table: "products"
          #dates: array:1 [▶]
          #connection: "mysql"
          #primaryKey: "id"
          #keyType: "int"
          +incrementing: true
          #with: []
          #withCount: []
          #perPage: 15
          +exists: true
          +wasRecentlyCreated: false
          #attributes: array:6 [▼
            "id" => 4
            "name" => "product2"
            "sfl_code" => "ertet"
            "micro_code" => "erter"
            "descrption" => "terter"
            "unit_of_measure" => 2
          ]
          #original: array:6 [▶]
          #casts: []
          #dateFormat: null
          #appends: []
          #events: []
          #observables: []
          #relations: []
          #touches: []
          +timestamps: true
          #hidden: []
          #visible: []
          #fillable: []
          #guarded: array:1 [▶]
          #forceDeleting: false
        }
        "activeprice" => null
        "unit" => UnitOfMeasure {#419 ▶}
      ]
      #touches: []
      +timestamps: true
      #hidden: []
      #visible: []
      #fillable: []
      #guarded: array:1 [▶]
      #forceDeleting: false
    }
  ]
}

Sathya replied 2 weeks ago

Got help through laracasts discussion

https://laracasts.com/discuss/channels/eloquent/db-relationships-with-where-condition

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

Sign in to participate in this thread!



We'd like to thank these amazing companies for supporting us