How to combine 3 different table using join with laravel? One table used for Many-Many relationship table with additional variable that needed on view

Question

I have 3 tables in database: users, bukus, and bukus_users(for many-many relationship I might need later when working on project)

The column and type variable of tables as followed was:

users: id, name(string), email(string), username(string), password(string)

bukus: id, nama_buku(string), cover(string), author(string), barcode(string)

bukus_users: id, bukus_id(bigint), users_id(bigint),tgl_pinjam(datetime), tgl_tenggat(datetime), status(boolean)

I needed a table view that retrieved from bukus_users table data with nama_buku replace bukus_id and username/email/name replace user_id, and might result table like following example:

No | username/email/name | nama_buku |tgl_pinjam|tgl_tenggal| status

1 | user1, us1@email.com| book1 | 10/02/22 | 21/02/22 | No

2 | user1, us1@email.com| book2 | 09/05/23 | 20/02/23 | Yes

3 | user2, us2@email.com| book2 | 10/02/22 | 21/02/22 | No

4 | user2, us2@email.com| book1 | 09/05/23 | 20/02/23 | Yes

So Here's the following code I did(you can ignore many of coded comment):

Model/Buku.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use App\Models\User;
use App\Models\bukus_users;
class Buku extends Model
{
    use HasFactory;
    /*protected function users(){
        return $this->belongsToMany('App\Models\User');
    }*/
    public function users()
    {
        return $this->belongsToMany(
            User::class,
            bukus_users::class,
            'bukus_id',
            'users_id'
        )->withPivot((new bukus_users())->getColumns());
    }
        /*protected function pinjam(){
            return $this->hasMany('App\Models\bukus_user');
        }*/
        protected $fillable = [
            'cover',
            'nama_buku',
            'author',
            'terbitan',
            'barcode',
            'ketersediaan',
        ]; 
}

Model/User.php

<?php

namespace App\Models;

// use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Laravel\Sanctum\HasApiTokens;
use App\Models\Buku;
use App\Models\bukus_users;

class User extends Authenticatable
{
    use HasApiTokens, HasFactory, Notifiable;
    public function bukus()
    {
        return $this->belongsToMany(
            Buku::class,
            bukus_users::class,
            'users_id',
            'bukus_id',
        )->withPivot((new bukus_users())->getColumns());
    }
        /*protected function pinjam(){
            return $this->hasMany('App\Models\bukus_user');
        }*/
        protected $fillable = [
            'name',
            'email',
            'username',
            'password',
            'address',
            'phone',
        ]; 
}

Model/bukus_users.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use App\Models\User;
use App\Models\Buku;
use Illuminate\Database\Eloquent\Relations\HasOne;

class bukus_users extends Model
{
    use HasFactory;
    public function user()
    {
        return $this->hasOne(User::class, 'id', 'users_id');
    }
    public function buku()
    {
        return $this->hasOne(Buku::class, 'id', 'bukus_id');
    }
    protected $fillable = [
        'bukus_id',
        'users_id',
        'tgl_pinjam',
        'tgl_tenggat',
        'status_pengembalian',
    ];
}

controller.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Providers\RouteServiceProvider;
use App\Models\bukus_users;
use Carbon\Carbon;
class PinjamController extends Controller
{
protected function index()
{
    //$rows = bukus_users::orderBy('id')->paginate(5);
    $i = 0;
    $rows = bukus_users::query()->with(['buku', 'user'])->paginate(5)->toArray(); // the rows will be in data key
    //ddd('dumping this one', $rows);
    return view('admin.pinjam.index', compact('rows', 'i'));
} }

admin/pinjam/index.blade.php:

<table class="table table-bordered">
            <thead>
                <tr>
                    <th>No</th>
                    <th>Nama User</th>
                    <th>Nama Buku Pinjaman</th>
                    <th>Tanggal Pinjam</th>
                    <th>Tanggal Tenggat</th>
                    <th>Status</th>
                    <th width="280px">Action</th>
                </tr>
            </thead>
            <tbody>
                @foreach ($rows as $pinjam)
                <tr>
                    <td>{{ $i++ }}</td>
                    <td>
                    {{ $pinjam->user->name }}, {{ $pinjam->user->username }}, {{ $pinjam->user->email }}
                    </td>
                    <td>
                    {{ $pinjam->buku->nama_buku }}
                    </td>
                    <td>{{ $pinjam->tgl_pinjam }}</td>
                    <td>{{ $pinjam->tgl_tenggat }}</td>
                    <td>{{ $pinjam->status_pengembalian}}</td>
                    <td>
                        <form action="{{ route('pinjam.destroy',$pinjam->id) }}" method="Post">
                            <a class="btn btn-primary" href="{{ route('pinjam.edit',$pinjam->id) }}">Edit</a>
                            @csrf
                            @method('DELETE')
                            <button type="submit" class="btn btn-danger">Delete</button>
                        </form>
                    </td>
                </tr>
            @endforeach
            </tbody>
        </table>

//previous error

However I got error sql join on field: **SQLSTATE[42S22]: Column not found: 1054 Unknown column 'bukus.bukus_users_id' in 'field list' **, so what did I do wrong? was it because tables on database? or the way I just forcing join table and needed better outer or inner join code?

Here's the ss of the error

SQLSTATE[42S22]

I was expecting tables on views retrieve data from table bukus_users with the very left of table containing username and nama_buku in respective column after id column that replacing both users_id and bukus_id. So I only needed bukus_user on views with a little bit of users and bukus data on the left corresponded with respective id saved in bukus_user database table.

And what I hope at database looking like this:

id | bukus_id | users_id| tgl_pinjam| tgl_tenggat| status

1 | 1 | 1 | 10/02/22 | 21/02/22 | No

2 | 2 | 1 | 09/05/23 | 20/02/23 | Yes

3 | 2 | 2 | 10/02/22 | 21/02/22 | No

4 | 1 | 2 | 09/05/23 | 20/02/23 | Yes

Into views table appear kind of like this:

No | username/email | nama buku| tgl pinjam| tgl tenggat| status

1 | user1, us1@email.com| book1 | 10/02/22 | 21/02/22 | No

2 | user1, us1@email.com| book2 | 09/05/23 | 20/02/23 | Yes

3 | user2, us2@email.com| book2 | 10/02/22 | 21/02/22 | No

4 | user2, us2@email.com| book1 | 09/05/23 | 20/02/23 | Yes

Update 2: New errors is Attempt to read property "user" on int

stacktrack

[previous exception] [object] (ErrorException(code: 0): Attempt to read property \"user\" on int at C:\\xampp\\htdocs\\perpusperkantas\\storage\\framework\\views\\33cf3e32f260a058cbd92996194f24de8700ba85.php:35)
[stacktrace]
#0 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Bootstrap\\HandleExceptions.php(266): Illuminate\\Foundation\\Bootstrap\\HandleExceptions->handleError(2, 'Attempt to read...', 'C:\\\\xampp\\\\htdocs...', 35)
#1 C:\\xampp\\htdocs\\perpusperkantas\\storage\\framework\\views\\33cf3e32f260a058cbd92996194f24de8700ba85.php(35): Illuminate\\Foundation\\Bootstrap\\HandleExceptions->Illuminate\\Foundation\\Bootstrap\\{closure}(2, 'Attempt to read...', 'C:\\\\xampp\\\\htdocs...', 35)
#2 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Filesystem\\Filesystem.php(109): require('C:\\\\xampp\\\\htdocs...')
#3 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Filesystem\\Filesystem.php(110): Illuminate\\Filesystem\\Filesystem::Illuminate\\Filesystem\\{closure}()
#4 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\View\\Engines\\PhpEngine.php(58): Illuminate\\Filesystem\\Filesystem->getRequire('C:\\\\xampp\\\\htdocs...', Array)
#5 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\View\\Engines\\CompilerEngine.php(70): Illuminate\\View\\Engines\\PhpEngine->evaluatePath('C:\\\\xampp\\\\htdocs...', Array)
#6 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\View\\View.php(195): Illuminate\\View\\Engines\\CompilerEngine->get('C:\\\\xampp\\\\htdocs...', Array)
#7 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\View\\View.php(178): Illuminate\\View\\View->getContents()
#8 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\View\\View.php(147): Illuminate\\View\\View->renderContents()
#9 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Http\\Response.php(69): Illuminate\\View\\View->render()
#10 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Http\\Response.php(35): Illuminate\\Http\\Response->setContent(Object(Illuminate\\View\\View))
#11 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Router.php(906): Illuminate\\Http\\Response->__construct(Object(Illuminate\\View\\View), 200, Array)
#12 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Router.php(875): Illuminate\\Routing\\Router::toResponse(Object(Illuminate\\Http\\Request), Object(Illuminate\\View\\View))
#13 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Router.php(797): Illuminate\\Routing\\Router->prepareResponse(Object(Illuminate\\Http\\Request), Object(Illuminate\\View\\View))
#14 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(141): Illuminate\\Routing\\Router->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))
#15 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Middleware\\SubstituteBindings.php(50): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#16 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Routing\\Middleware\\SubstituteBindings->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#17 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Auth\\Middleware\\Authenticate.php(44): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#18 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Auth\\Middleware\\Authenticate->handle(Object(Illuminate\\Http\\Request), Object(Closure), 'admin')
#19 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Middleware\\VerifyCsrfToken.php(78): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#20 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\VerifyCsrfToken->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#21 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\View\\Middleware\\ShareErrorsFromSession.php(49): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#22 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\View\\Middleware\\ShareErrorsFromSession->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#23 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Session\\Middleware\\StartSession.php(121): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#24 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Session\\Middleware\\StartSession.php(64): Illuminate\\Session\\Middleware\\StartSession->handleStatefulRequest(Object(Illuminate\\Http\\Request), Object(Illuminate\\Session\\Store), Object(Closure))
#25 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Session\\Middleware\\StartSession->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#26 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Cookie\\Middleware\\AddQueuedCookiesToResponse.php(37): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#27 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Cookie\\Middleware\\AddQueuedCookiesToResponse->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#28 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Cookie\\Middleware\\EncryptCookies.php(67): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#29 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Cookie\\Middleware\\EncryptCookies->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#30 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(116): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#31 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Router.php(797): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))
#32 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Router.php(776): Illuminate\\Routing\\Router->runRouteWithinStack(Object(Illuminate\\Routing\\Route), Object(Illuminate\\Http\\Request))
#33 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Router.php(740): Illuminate\\Routing\\Router->runRoute(Object(Illuminate\\Http\\Request), Object(Illuminate\\Routing\\Route))
#34 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Router.php(729): Illuminate\\Routing\\Router->dispatchToRoute(Object(Illuminate\\Http\\Request))
#35 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Kernel.php(190): Illuminate\\Routing\\Router->dispatch(Object(Illuminate\\Http\\Request))
#36 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(141): Illuminate\\Foundation\\Http\\Kernel->Illuminate\\Foundation\\Http\\{closure}(Object(Illuminate\\Http\\Request))
#37 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest.php(21): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#38 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Middleware\\ConvertEmptyStringsToNull.php(31): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#39 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\ConvertEmptyStringsToNull->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#40 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest.php(21): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#41 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Middleware\\TrimStrings.php(40): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#42 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\TrimStrings->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#43 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Middleware\\ValidatePostSize.php(27): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#44 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\ValidatePostSize->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#45 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Middleware\\PreventRequestsDuringMaintenance.php(86): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#46 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\PreventRequestsDuringMaintenance->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#47 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Http\\Middleware\\HandleCors.php(49): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#48 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Http\\Middleware\\HandleCors->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#49 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Http\\Middleware\\TrustProxies.php(39): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#50 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Http\\Middleware\\TrustProxies->handle(Object(Illuminate\\Http\\Request), Object(Closure))
#51 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(116): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))
#52 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Kernel.php(165): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))
#53 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Kernel.php(134): Illuminate\\Foundation\\Http\\Kernel->sendRequestThroughRouter(Object(Illuminate\\Http\\Request))
#54 C:\\xampp\\htdocs\\perpusperkantas\\public\\index.php(51): Illuminate\\Foundation\\Http\\Kernel->handle(Object(Illuminate\\Http\\Request))
#55 C:\\xampp\\htdocs\\perpusperkantas\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\resources\\server.php(16): require_once('C:\\\\xampp\\\\htdocs...')
#56 {main}
"} 

var_dump respone

array(13) { ["current_page"]=> int(1) ["data"]=> array(1) { [0]=> array(10) { ["id"]=> int(5) ["bukus_id"]=> int(4) ["users_id"]=> int(1) ["tgl_pinjam"]=> string(19) "2023-04-13 08:39:57" ["tgl_tenggat"]=> string(19) "2023-04-24 08:39:57" ["status_pengembalian"]=> int(1) ["created_at"]=> string(27) "2023-04-13T08:39:57.000000Z" ["updated_at"]=> string(27) "2023-04-13T08:39:57.000000Z" ["buku"]=> array(9) { ["id"]=> int(4) ["cover"]=> string(18) "20230412074134.jpg" ["nama_buku"]=> string(17) "Pemrograman Web X" ["author"]=> string(10) "Yudishtira" ["terbitan"]=> string(4) "2014" ["barcode"]=> string(13) "9789790929999" ["ketersediaan"]=> int(1) ["created_at"]=> string(27) "2023-04-12T07:41:34.000000Z" ["updated_at"]=> string(27) "2023-04-12T07:41:34.000000Z" } ["user"]=> array(9) { ["id"]=> int(1) ["name"]=> string(14) "Muhammad Yusuf" ["username"]=> string(8) "pengguna" ["email"]=> string(22) "betweenerkex@gmail.com" ["email_verified_at"]=> NULL ["address"]=> string(13) "Kudus, Jateng" ["phone"]=> string(12) "087874837301" ["created_at"]=> string(27) "2023-04-06T03:31:33.000000Z" ["updated_at"]=> string(27) "2023-04-06T03:31:33.000000Z" } } } ["first_page_url"]=> string(41) "http://127.0.0.1:8000/admin/pinjam?page=1" ["from"]=> int(1) ["last_page"]=> int(1) ["last_page_url"]=> string(41) "http://127.0.0.1:8000/admin/pinjam?page=1" ["links"]=> array(3) { [0]=> array(3) { ["url"]=> NULL ["label"]=> string(16) "« Previous" ["active"]=> bool(false) } [1]=> array(3) { ["url"]=> string(41) "http://127.0.0.1:8000/admin/pinjam?page=1" ["label"]=> string(1) "1" ["active"]=> bool(true) } [2]=> array(3) { ["url"]=> NULL ["label"]=> string(12) "Next »" ["active"]=> bool(false) } } ["next_page_url"]=> NULL ["path"]=> string(34) "http://127.0.0.1:8000/admin/pinjam" ["per_page"]=> int(5) ["prev_page_url"]=> NULL ["to"]=> int(1) ["total"]=> int(1) }

vardump response

Answer 1

You need to use the belongsToMany relation in your Book model:

public function users(): BelongsToMany
{
    return $this->belongsToMany(
        User::class,
        BookUserPivot::class,
        'book_id',
        'user_id'
    )->withPivot((new BookUserPivot())->getColumns());
}

This will retrieve collection of books that will contain users, each user with a pivot key in which you will find the pivot values.

// test it

foreach (Book::query()->with('users')->get() as $book) {
    echo ($firstUser = $book->users->first())?->name . ' ' . $book->name . ' ' . $firstUser?->pivot->borrowdate;
{

or

foreach (Book::query()->with('users')->get() as $book) {
    foreach ($book->users as $user) {
        echo $user->name . ' ' . $book->name . ' ' . $user->pivot->borrowdate;
    {
{

// or use it in views

return \view('views.view', ['rows' => Book::query()->with('users')->get()]) // {{$rows}} in the blade

// for pagination return json to fe ajax call:

return \response()->json(Book::query()->with('users')->paginate(
    \max(1, $allRequest['limit'] ?? 10),
    ['*'],
    'page',
    \max((int)($allRequest['page'] ?? 1), 1)
)->toArray())

UPDATE

Model/Buku.php

public function users()
{
    return $this->belongsToMany(
        User::class,
        bukus_users::class,
        'bukus_id',
        'users_id'
    )->withPivot((new bukus_users())->getColumns());
}

Model/User.php

public function bukus()
{
    return $this->belongsToMany(
        Buku::class,
        bukus_users::class,
        'users_id',
        'bukus_id',
    )->withPivot((new bukus_users())->getColumns());
}

Model/bukus_users.php

use Illuminate\Database\Eloquent\Relations\HasOne;

public function user(): HasOne
{
    return $this->hasOne(User::class, 'id', 'users_id');
}
public function buku(): HasOne
{
    return $this->hasOne(Buku::class, 'id', 'bukus_id');
}

controller.php

protected function index()
{
    $rows = bukus_users::query()->with(['buku', 'user'])->paginate(5)->toArray(); // the rows will be in data key
    return view('admin.pinjam.index', ['rows' => $rows]);
}


 
["current_page"]=> int(1) 
    ["data"]=> array(1) {
    [0]=> array(10) {
        ["id"]=> int(5)
            ["bukus_id"]=> int(4)
            ["users_id"]=> int(1)
            ["tgl_pinjam"]=> string(19) "2023-04-13 08:39:57"
            ["tgl_tenggat"]=> string(19) "2023-04-24 08:39:57"
            ["status_pengembalian"]=> int(1)
            ["created_at"]=> string(27) "2023-04-13T08:39:57.000000Z"
            ["updated_at"]=> string(27) "2023-04-13T08:39:57.000000Z"
            ["buku"]=> array(9) {
                ["id"]=> int(4)
                ["cover"]=> string(18) "20230412074134.jpg"
                ["nama_buku"]=> string(17) "Pemrograman Web X" 
                ["author"]=> string(10) "Yudishtira"
                ["terbitan"]=> string(4) "2014"
                ["barcode"]=> string(13) "9789790929999"
                ["ketersediaan"]=> int(1)
                ["created_at"]=> string(27) "2023-04-12T07:41:34.000000Z"
                ["updated_at"]=> string(27) "2023-04-12T07:41:34.000000Z"
            }
            ["user"]=> array(9) {
                ["id"]=> int(1)
                ["name"]=> string(14) "Muhammad Yusuf"
                ["username"]=> string(8) "pengguna"
                ["email"]=> string(22) "betweenerkex@gmail.com"
                ["email_verified_at"]=> NULL
                ["address"]=> string(13) "Kudus, Jateng"
                ["phone"]=> string(12) "087874837301"
                ["created_at"]=> string(27) "2023-04-06T03:31:33.000000Z"
                ["updated_at"]=> string(27) "2023-04-06T03:31:33.000000Z"
            }
    } } ["first_page_url"]=> string(41) "http://127.0.0.1:8000/admin/pinjam?page=1" ["from"]=> int(1) ["last_page"]=> int(1) ["last_page_url"]=> string(41) "http://127.0.0.1:8000/admin/pinjam?page=1" ["links"]=> array(3) { [0]=> array(3) { ["url"]=> NULL ["label"]=> string(16) "« Previous" ["active"]=> bool(false) } [1]=> array(3) { ["url"]=> string(41) "http://127.0.0.1:8000/admin/pinjam?page=1" ["label"]=> string(1) "1" ["active"]=> bool(true) } [2]=> array(3) { ["url"]=> NULL ["label"]=> string(12) "Next »" ["active"]=> bool(false) } } ["next_page_url"]=> NULL ["path"]=> string(34) "http://127.0.0.1:8000/admin/pinjam" ["per_page"]=> int(5) ["prev_page_url"]=> NULL ["to"]=> int(1) ["total"]=> int(1) }