thebookcollector's avatar

Eloquent - Need help hiding a relationship

I have a book collecting app. Users can add books to their collection and then go to a page and view their collection. They can also mark books in their collection as "read". There is a "reads" table that simply contains the book_id and the user_id. I believe I have all of the appropriate relationships set up. I'd like to be able to have a feature where users can hide all of their read books so they can see just their backlog of books they haven't read.

I've tried:

Book::whereHas('collection', function (Builder $query) use ($this->user->id) 
{ $query->where('collections.user_id', '=', $this->user->id); })
->doesntHave('reads')

but that removes any books that have been read regardless of which user read it.

So if user1 is viewing their collection and has "The Way of Kings" in their collection and hasn't read it but user2 has read it - this query will remove the book from the results.

I've also tried:

Book::whereHas('collection', function (Builder $query) use ($this->user->id) 
{ $query->where('collections.user_id', '=', $this->user->id); })
->whereDoesntHave('reads', function (Builder $query) {
                $query->where('user_id', $this->user->id);

And this correctly removes all reads by the user but books that other users have read are returned in the relationship - so my view logic for showing a "read" badge sees the relationship on the record as a read and indicates that a book is read even though another user read it.

Any ideas on how to cleanly get the results I'm after?

0 likes
2 replies
Tray2's avatar

Something like this then

SELECT * 
FROM book_collection
WHERE user_id = :auth_id
AND book_id NOT IN (SELECT book_id 
					FROM book_read
					 WHERE user_id = :auth_id);
jlrdw's avatar

Or a checkbox then the where could be where 0. Checked would be non zero 1. But just another suggestion.

  • 0 not read
  • 1 read

Please or to participate in this conversation.