Getting collections of data Laravel Eloquent doubts

Question

I have four tables in database and one Eloquent model for each table. Tables are:

How to retrieve items from one category and get collection of itemProperties for each item, so that when item don't have all properties which category have, the rest of properties exists in collection and values for that properties are empty strings or something like that?

For example:

in table category i have entry

cats

in table category_properties i have entries

color of eyes
length of tail
shape of ears

In table items i have entry

Garfield

and in item_properties I have only one entry

green

related to Garfield in items table and color of eyes in category_properties.

How to get [ Garfield => [ 'color of eyes' => 'green', 'length of tail' => ' ', 'shape of ears' => ' ']], instead of [Garfield => [ 'color of eyes' => 'green']]

I can easily get expected results in mysql by joining tables item_properties and category_properties, but i don't know how to do the same with Eloquent and Laravel collections.

Is there something similar to sql join in eloquent?

Adis Azhar Adis Azhar · Accepted Answer · 2018-07-19T16:36:28

Your description is a bit hard to understand. Im assuming you are trying to get items with item_properties from a category? It's best that you avoid using raw joins in Laravel, and use the Model instead.

Now, each of your Model create a relationship (Eloquent Relationship).

https://laravel.com/docs/5.6/eloquent-relationships

And then you can use Eager Loading

https://laravel.com/docs/5.6/eloquent-relationships#eager-loading

I hope you've read the links I sent you.

Example:

Category Model:

public function items(){
  return $this->hasMany('App\Item', 'category_id', 'id');
}
public function categoryProperties(){
  return $this->belongsTo('App\CategoryProperty', 'category_id', 'id');
}

Item Model:

public function categories(){
  return $this->belongsTo('App\Category', 'category_id', 'id');
}

public function itemProperties(){
  return $this->hasMany('App\ItemProperty', 'item_id', 'id');
}

ItemProperty Model:

  public function items(){
      return $this->belongsTo('App\Item', 'item_id', 'id');    
}

public function categoryProperties(){
      return $this->belongsTo('App\CategoryProperty', 'property_id', 'id');
    }

CategoryProperty Model:

  public function itemProperties(){
      return $this->hasMany('App\ItemProperty', 'property_id', 'id');
    }
public function categories(){
  return $this->belongsTo('App\Category', 'category_id', 'id');
}

Controller:

$data = Item::with(['categories.categoryProperties','categories.categoryProperties.itemProperties'])->get();

Getting collections of data Laravel Eloquent doubts

1 Answers