Laravel hasMany on json field

Admin 1
Viewed 13716

I would like to get all products related to a certian color using Eloquent 'hasMany' relationship. However the colors related to products are stored in json value in the products table.

Color Table:

-------------
id | color   
-------------
 1  Black     
 2  Brown
 3  Gray

Product Table:

------------------------------
id | name      | color
------------------------------
 1   Shoe Name   ["1","2"]
 2   Shoe Name   ["2","3"]

Color Model

public function products()
{
  return $this->hasMany(Product::class, 'color');
}

But unfortunately nothing returns when i do dd(Color::products());

I know the issue is trying to do a hasMany on a json field, any help or assistance would be great. Unfortunately i won't be able to change the database structure. I need to work with it as is.

Thanks in advance.

laravel-5.4 laravel-5 laravel
2 Answers

I created a package with JSON relationships: https://github.com/staudenmeir/eloquent-json-relations

You can create a many-to-many relationship like this:

class Product extends Model
{
    use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;

    protected $casts = [
       'color' => 'json'
    ];

    public function colors()
    {
        return $this->belongsToJson(Color::class, 'color');
    }
}

class Color extends Model
{
    use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;

    public function products()
    {
       return $this->hasManyJson(Product::class, 'color');
    }
}
user_48485870

Going on from my comment, ideally i would simply do the following:

  • Create a pivot table called product_colors to store product & color association. So the relation will become a many-to-many.

This i would seriously recommend. Since you have written that you aren't able to alter the database structure.

For example, to retrieve list of products for a color. Your solution might be something like the following:

Create a mutator in your Product model that converts color json field into an array:

public function setColor()
{
    return json_decode($this->color, true);
}

Create a function in your Color model that retrieves a list of all products, and then filters list to return products that contains the specific color

public function products()
{
    return Product::all()->filter(function($product) {
        return in_array($this->id, $product->color)) ? $product : null;
    });               
}
user_23955510
Related