CODEWITHSUNDEEP
mysqldatabaselaravellaravel-5eloquent
nick carraway
nick carraway

Reputation: 280

Getting row identified by foreign key reference in laravel

I have a music streaming app. There are multiple songs in the app. Each user may "save" a song to their library.

So, the base model "song" has things like song_id,title,songduration,artist,album.

Then, there's a "usersong" model that has many rows. Each row is just a user_id and a song_id. When a user logs in, I get a list of songs they've saved by querying their user_id to that table. But, I'd like to get the full "song" row referenced by song_id, without having to store information in both songs and usersongs, and without having to make a 1000 individual queries.

Here is the "song" migration (shortened for simplicity):

  Schema::create('songs', function (Blueprint $table) {
        $table->string('id', 32);
        $table->string('title');
        $table->float('length');
})

Here is the "usersong" migration:

 Schema::create('usersongs', function (Blueprint $table) {
        $table->integer('user_id')->unsigned(); // a koel reference
        $table->string('song_id', 32);
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
        $table->foreign('song_id')->references('id')->on('songs')->onDelete('cascade'); 
        });

Here is the UserSong model, for the above schema. What's the best way to make the "getByUserId" function to return all "songs" belonging to the user with user_id, in a single query?

class UserSong extends Model
{

    protected $guarded = [];


    protected $table="usersongs";

    public function getByUserId($user_id){
        $this->where("user_id",$user_id)->get();
        /// how do I get the song rows automatically in this function?
    }
    protected $fillable=["user_id","song_id"];
}

Ideally I would call it in another script like:

$songs=UserSong::getByUserId($someuserid);

Upvotes: 1

Views: 823

Answers (3)

Dhruv Raval
Dhruv Raval

Reputation: 1583

You can use scope and relationship:

Example

//scope
public function scopeGetByUserId($query, $someuserid)
{
    return $query->where('user_id', $someuserid);
}

//relationship
public function userSongs()
{
    return $this->hasMany('App\Song', 'song_id');
}

Use:

$songs = UserSong::getByUserId($someuserid)->with('userSongs')->get();

Source

Upvotes: 2

pellul
pellul

Reputation: 1031

What about using the Relationship Existence Query?

In your Song model, you should define your relationship with User.

class Song extends Model
{
    public function users()
    {
        return $this->belongsToMany(User::class)->using(UserSong::class);
    }
}

Then, in your controller, you can write:

MyAwesomeController

Song::whereHas('users', function ($query) use ($yourUserId) {
    $query->where('id', $yourUserId);
})->get();

There you'll get the Collection of the songs linked to your User.

By the way, your UserSong should maybe extends Illuminate\Database\Eloquent\Relations\Pivot instead of Model.

For further explanations, you can take a look at Laravel's Many to Many Documentation

Upvotes: 1

Agnikim Pro
Agnikim Pro

Reputation: 7

you can use this to to do joining in a easy and fast way

https://github.com/themsaid/laravel-model-transformer

Upvotes: -1

Related Questions