Pagination on Eager Load

You're using get instead of paginate.

I'd go the opposite direction, and get Books::with(user)->where('user_id', $user_id), and paginate on books.

Books::with('user')
    ->where('user_id', $user_id)
    ->where('books.title', 'LIKE', '%'. $search .'%')
    ->orWhere('books.isbn', 'LIKE', '%'. $search .'%')
    ->orderby($orderby, 'asc')
    ->paginate(5);

@Cronix The query you posted provides me all the books, regardless of the user_id ... but I need to request only the books owned by the user. This is what I have now:

$books = Book::with([
    'users' => function($query) use ($user_id){
        $query->where('users.id', $user_id)->get();
    }
])->where('books.title', 'LIKE', '%'. $search .'%')
    ->orWhere('books.isbn', 'LIKE', '%'. $search .'%')
    ->orderby($orderby, 'asc')
    ->paginate(5); 

My tables are: users, books, and book_user (pivot).

True, it wouldn't work on a pivot. I didn't know what your relationship was. Is it working now though?

Well, pagination is working, but it is pulling all books regardless of user. From what I read, paginate() does not work within an eager load. Is there a different approach than what I'm doing? Should I do multiple requests? If all else fails, I'll just do the paginate manually with limit. I know this works.

Try using whereHas(). https://laravel.com/docs/5.5/eloquent-relationships#querying-relationship-existence

$books = Book::whereHas('users' => function($query) use ($user_id){
        $query->where('id', $user_id);
})->where('title', 'LIKE', '%'. $search .'%')
    ->orWhere('isbn', 'LIKE', '%'. $search .'%')
    ->orderby($orderby, 'asc')
    ->paginate(5); 

No, it doesn't work.

Unknown column 'books.id' in 'where clause' (SQL: select * from `users` inner join `book_user` on `users`.`id` = `book_user`.`user_id` where `books`.`id` = `book_user`.`book_id` and `users`.`id` = 1)

does books have an id column?

The query @Cronix posted is correct, however you need to group wheres because of logical ordering.

Books::with('user')
    ->where('user_id', $user_id)
    ->where(function ($query) {
        $query->where('title', 'LIKE', '%'. $search .'%');
        $query->orWhere('isbn', 'LIKE', '%'. $search .'%');
    })
    ->orderby($orderby, 'asc')
    ->paginate(5);

This will return books owned by the $user_id where (title or isbn matches $search) and paginate them.

How exactly is your DB structured? Name of the primary and foreign keys?

Update: Did you try this solution?

User::find($user_id)
    ->books()
    ->where('title', 'like', '...')
    ->orWhere('isbn', 'like', '...')
    ->paginate(10);

@Snapey Yes. Books has an id column.

@crnkovic I have 3 tables. Here they are with their respectful keys.

users

• id

book_user

• user_id
• book_id

books

• id

I think your second query did the trick. I'm still testing. Does books() utilize the method on the pivot table model? This is terrific and short.

https://laravel.com/docs/5.6/eloquent-relationships#many-to-many

The docs show the similar example clearly:

class User extends Model
{
    public function roles()
    {
        return $this->belongsToMany('App\Role');
    }
}


$roles = App\User::find(1)->roles()->orderBy('name')->get();

So second query operates on a Books rather than intermediate table. So title and ISBN constraints are applied to the books table.

To get immediate (pivot) model, for example let's say you want to see the date user has returned a book you would make a returned_at date at the book_user table and call it:

@foreach ($user->books()->where(...)->get() as $book)
    {{ dd($book->pivot->returned_at) }}
@endforeach