CODEWITHSUNDEEP
phplaraveleloquent
Chris
Chris

Reputation: 1654

One To Many Relationship in Eloquent with JSON field

I'm trying to create a connection between a JSON field in my database and a table which stores music by ID. So, I have a table called "playlists" which has a field called "songs". In this "songs" field I have a array[] of song ID's e.g. [1,2]. I tried the following code to make a relationship between these two tables:

class Playlist extends Model
{
    protected $table = 'playlists';

    public function songs()
    {
        return $this->hasMany('App\Music', 'id');
    }

}

I used the foreign_key id because of the songs table which has a id field.

The code I used to retrieve the playlist from the controller is as follows:

$playlist = Playlist::find($id)->songs;
print_r($playlist);

Which outputs: [1,2]

I most probably did something wrong, not understanding the relationships correctly. Could someone explain how this works? I looked up the documentation but did not get any wiser.

Upvotes: 4

Views: 7706

Answers (3)

Dev
Dev

Reputation: 441

Although this is a very old post but I will go ahead and drop my own opinion for my future self and fellow googlers.....

So, If I got this question correctly, you are trying to use a JSON field for a relationship query. This issue I have stumbled across a couple of times, at different occasions for different use-cases. With the most recent being for the purpose of saving a couple of Ids belonging to different tables, in a single JSON field on a given table (While I keep pondering on why the Laravel guy won't just add this functionality already! I Know Pivots, Data Normalization etc....But I'm pleading for the 1%). Until I came across this post on Laracast that worked like a charm.

Apologies for the long intro, let me get right into it....

On your Playlist model (in Laravel 8.0 and a few older versions I can't really keep track of) you can do something like so;

public function songs() 
{
    $related = $this->hasMany(Song::class);
    $related->setQuery(
        Song::whereIn('id', $this->song_ids)->getQuery()
    );

    return $related;
}

Upvotes: 3

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25926

Laravel has no native support for JSON relationships.

I created a package for this: https://github.com/staudenmeir/eloquent-json-relations

If you rename the songs column to song_ids, you can define a many-to-many relationship like this:

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

    protected $casts = [
       'song_ids' => 'json',
    ];

    public function songs()
    {
        return $this->belongsToJson('App\Music', 'song_ids');
    }
}

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

    public function playlists()
    {
       return $this->hasManyJson('App\Playlist', 'song_ids');
    }
}

Upvotes: 6

Ismoil Shifoev
Ismoil Shifoev

Reputation: 6011

I have the really good solution for keeping data in column on json format. It help me on previous project online shop

https://scotch.io/tutorials/working-with-json-in-mysql

Upvotes: 2

Related Questions