Home>

I have the following relationship model.
BookUser (one) to Book (many).
So, when I want to make a list function of a book, I want a controller to create a query including a free book by creating a query, so I want to change the contents such as orderBy in ajax.

But what I want now is different from what you actually want. The book I borrowed is not out,

public function index (BookIndexRequest $request)
    {
        $query = Book :: select ('books. *')
            ->john ('bookUsers', 'bookUsers.id', '=', 'books.borrowing_user_id');
        if ($request->ajax ()) {
            if (! empty ($request->get ('seachText'))) {
                $query->where ('book.name', 'LIKE', '%'. $request->get ('seachText'). '%');
            }
            if (! empty ($request->get ('orderBys'))) {
                foreach ($request->get ('orderBys') as $key =>$value) {
                    $query->orderBy ($key, $value);
                }
            }
            return \ Response :: json ($query->get ());
        }
        $books = $query->get ();
        return view ('books.index', compact ('books'));
    }
    / **
    query Contents
    books.book_name | books.borrowing_user_id | bookUsers.name
    BookA | 1 | Tom
    BookC | 5 | Peter
    */


This is what I really want

books.book_name | books.borrowing_user_id | bookUsers.name
BookA | 1 | Tom
BookB | Null | Null
BookC | 5 | Peter
BookD | Null | Null
BookE | Null | Null
BookF | Null | Null
BookG | 11 | Ken

Teach us how to create a query with the content I want.

Applicable source code

BookUser model

class BookUser extends Model
{
    use SoftDeletes;
    protected $fillable = [
        'name'
        , 'created_at'
        , 'updated_at'
        , 'deleted_at'
    ];
}
public function books ()
{
    return $this->hasMany (Book :: class, 'borrowing_user_id', 'id');
}


Book model

class Book extends Model
{
    use SoftDeletes;
    protected $fillable = [
        'borrowing_user_id' // available when null
        , 'book_name'
        , 'created_at'
        , 'updated_at'
        , 'deleted_at'
    ];
}
public function borrowing_user ()
{
    return $this->belongsTo (User :: class, 'borrowing_user_id', 'id');
}
Supplemental information (FW/tool version etc.)

Laravel 5.7
PHP 7.2
CentOS 7

  • Answer # 1

    Controller

    public function index (BookIndexRequest $request)
        {
            $books = Book :: query ()
                ->with (['borrowing_user'])
                ->when ($request->get ('seachText'), function ($query) use ($request) {
                    $query->where ('name', 'LIKE', '%'. $request->get ('seachText'). '%');
                })
                ->when ($request->get ('orderBys', function ($query) use ($request) {
                    foreach ($request->get ('orderBys') as $key =>$value) {
                        $query->orderBy ($key, $value);
                    }
                }))
                ->get ();
            if ($request->ajax ()) {
                return \ Response :: json ($books);
            }
            return view ('books.index', compact ('books'));
        }
    Model
    class Book extends Model
    {
        use SoftDeletes;
        protected $fillable = [
            'borrowing_user_id',
            'book_name',
            'created_at',
            'updated_at',
            'deleted_at'
        ];
        public function borrowing_user ()
        {
            return $this->belongsTo (User :: class, 'borrowing_user_id', 'id')
                ->withDefault ();
        }
    }

Trends