CODEWITHSUNDEEP
laravellaravel-5
Ariando
Ariando

Reputation: 1421

Get data from 2 tables connected using a pivot table (Laravel 5.3)

How to get data from 2 tables connected using a pivot table ? for example, in my case I have users table connected to journal table using a pivot table (penulis table). Now I want to get journals data that belonged to specific user. I tried this :

$journal_list = DB::table('journal')->where('id_user', '=','id_journal')->orderBy('id', 'desc')->paginate(20);

That code above doesn't work. Below are my migrations :

Users table :

public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('username')->unique();
            $table->string('userslug');
            $table->string('nameslug');
            $table->string('email')->unique();
            $table->string('phone')->nullable();
            $table->string('address')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->enum('level', ['admin', 'author']);
            $table->timestamps();
        });
    }

Journal table : 

public function up() {
        Schema::create('journal', function (Blueprint $table) {
            $table->increments('id');
            $table->string('title', 255);
            $table->text('abstract');
            $table->text('file');
            $table->integer('id_edition')->unsigned();
            $table->string('journalslug');
            $table->timestamps();
        });
    }

Penulis table (the pivot table)

public function up()
    {
        Schema::create('penulis', function (Blueprint $table) {
            // Create tabel penulis
            $table->integer('id_user')->unsigned()->index();
            $table->integer('id_journal')->unsigned()->index();
            $table->timestamps();

            // Set PK
            $table->primary(['id_user', 'id_journal']);

            // Set FK penulis --- user
            $table->foreign('id_user')
                  ->references('id')
                  ->on('users')
                  ->onDelete('cascade')
                  ->onUpdate('cascade');

            // Set FK penulis --- journal
            $table->foreign('id_journal')
                  ->references('id')
                  ->on('journal')
                  ->onDelete('cascade')
                  ->onUpdate('cascade');
        });
    }

View composer :

public function boot()
    {
        View::composer('user/show', function ($view) {
            $journal_list = User::where('id', $user_id)->with('journal')->first();
            $view->with('journal_list', $journal_list);
        });
    }

Upvotes: 0

Views: 394

Answers (2)

Lalit Thapa
Lalit Thapa

Reputation: 311

Why not make use of the Eloquent if you are using Laravel

First define the relations in your models

class User extends Model {

    public function journal(){
        return $this->belongsToMany('App\Journal', 'penulis', 'id_user', 'id_journal');
    }

}


class Journal extends Model {

    public function user(){
        return $this->belongsToMany('App\User', 'penulis', 'id_journal', 'id_user');
    }

}

Of course you need to define the table and fillables in your model with table name and table columns respectively.

And to get the specific data related to users you can use eloquent,

User::with('journal')->paginate(20);

This will load 20 users(paginated) with related journal data.

To know further about the eloquent relationships, take a look at this link.

Hope it solves your problem.

Upvotes: 0

Alexey Mezenin
Alexey Mezenin

Reputation: 163748

If you want to use Eloquent, you should setup belongsToMany() relation first:

class User extends Authenticatable
{

    public function journals()
    {
        return $this->belongsToMany('App\Journal');
    }

Then use eager loading to load the data:

User::where('id', $userId)->with('journals')->first();

If you don't want to use Eloquent and just need journals:

$journal_ids = DB::table('penulis')->where('id_user', $userId)->get(['id_journal'])->toArray();
$journal_list = DB::table('journal')->whereIn('id', $journal_ids)->paginate(20);

Or do the same by using join().

Upvotes: 1

Related Questions