CODEWITHSUNDEEP
laraveleloquentlaravel-5
user6697005
user6697005

Reputation: 11

Exclude results from query if relationship in table exists

I have two tables:

posts table:

id | user_id | message
----------------------
 1 |      24 | hello
 2 |      96 | world
...

posts_seen table:

id | user_id | post_id
----------------------
 1 |      24 |       2
 2 |      96 |       1

The second table, posts_seen, simply tracks which posts have been "seen" (or read) by a user. For example, user #24 has seen post #2.

In my Post.php model, I have this:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    protected $table = 'posts';

    public function seen()
    {
        return $this->hasOne('App\Models\PostSeen');
    }
}

However, when I query posts, I want to exclude posts that have already been seen by the user. Right now, I only have this:

$posts = Post::with('seen')
    ->get();

I want to modify this so that, if I am user #24, the query should not return post #2 since I have already seen it.

How do I do this?

Upvotes: 1

Views: 1753

Answers (3)

r.cartojano
r.cartojano

Reputation: 11

Can use whereDoesntHave

Add a relationship count / exists condition to the query with where clauses.

$posts = Post::whereDoesntHave('seen', function($query) use ($request){
            $query->where('user_id', $request->user()->id);
        })
->get();

Upvotes: 0

Diego Oliveira
Diego Oliveira

Reputation: 452

What you should do is use the method whereNotExists in your query builder, like this:

$posts = Post::whereNotExists(function ($query) {
    $query->select(DB::raw(1))
          ->from('posts_seen')
          ->whereRaw('posts_seen.user_id = posts.user_id');
})
->get();

Note that I'm also assuming that you don't need the seen relationship anymore. If I'm wrong, you can just call the with again.

Upvotes: 3

jaysingkar
jaysingkar

Reputation: 4435

You can do something like this:
First select the post ids which are seen by user identified by user_id

$posts_seen = PostSeen::where('user_id',$user_id)->get()->lists('post_id');

Then Select all the posts excluding post ids in $posts_seen using whereNotIn() method.

The whereNotIn method verifies that the given column's value is not contained in the given array:

Post::whereNotIn('id',$posts_seen)->get();

Upvotes: 0

Related Questions