Getting the rows that don’t exist yet

Databases
#1

I’m trying to retrieve all records of all posts that haven’t been seen yet for a particular user, but I can’t seem to get it right. When I run the raw query I always get an return and if I change the post.ownerId => 9 and the posts.is_public => 30 it still returns records but I don’t have posts in the post table :thinking:

I’ve made the next sql script:

select `posts`.`id` from `posts` left join `post_seens` on `posts`.`id` = `post_seens`.`postId` and `post_seens`.userId = 2 and `posts`.`ownerId` = 2 and `posts`.`is_public` = ? where `post_seens`.`postId` is null and `post_seens`.`userId` is
    null limit 500;

And with this PHP / Laravel code:

              $items = DB::table('posts')->leftJoin('post_seens', function ($join) use ($user, $postOwner) {
            $join->on('posts.id', '=', 'post_seens.postId')
                ->where('post_seens.userId', '=', $user->id)
                ->where('posts.ownerId', '=', $postOwner)
                ->where('posts.is_public', '=', 1);
        })
            ->whereNull('post_seens.postId')
            ->whereNull('post_seens.userId')
            ->select(['posts.id'])
            ->limit(500)->get();

I wanted to write a matches, so if the records exists in the post_seen table and some visit some xprofile again, only the new posts are added to the post_seen table.

(This is it without escaping)

select `posts`.`id` from `posts` left join `post_seens` on `posts`.`id` = `post_seens`.`postId` and `post_seens`.userId = 2 and `posts`.`ownerId` = 15 and `posts`.`is_public` = 30 where `post_seens`.`postId` is null and `post_seens`.`userId` is
    null limit 500;

What did I do wrong :thinking:

#2

Well without escaping you’re calling is_public = 9 instead of 1, but…

To be clear; if you’re finding records because the filters on POSTS isnt applying, the filters should be in the WHERE clause, not the join clause. The join clause is to tell it what to not pair up; the where clause is for things to exclude from the result.

(EDIT: but then, if it didnt pair up, the nulls should be booting it from the result anyway… hrm…)

#3

Okay how would you write the query then? The main problem is that it has to be mismatched records :slight_smile:. I think you should have a sub join :thinking:

#4

So when i move the ‘where’ to before the ownerID check… (the bottom one)

For reference, my data set is: The user has seen post 2. Post 11 is not public. Post 12 has the wrong owner.

#5

So I think this would be the correct one?

       $items = DB::table('posts')->leftJoin('post_seens', function ($join) use ($user, $postOwner) {
            $join->on('posts.id', '=', 'post_seens.postId')
                ->where('post_seens.userId', '=', $user->id);
        })
            ->whereNull('post_seens.postId')
            ->whereNull('post_seens.userId')
            ->where('posts.is_public', '=', 1)
            ->where('posts.ownerId', '=', $postOwner)
            ->select(['posts.id'])
            ->limit(500)->get()

So in SQL it would be:

select `posts`.`id` from `posts` left join `post_seens` on `posts`.`id` = `post_seens`.`postId` and `post_seens`.`us
erId` = 1 where `post_seens`.`postId` is null and `post_seens`.`userId` is null and `posts`.`is_public` = 1 and `posts`.`ownerId`
 = 2 limit 10

Now you still have the post_seens.userId = 1 in the join. But the post_seens.userId = 1 is also some filter right? Since you only get the records for the post_seens.userid = x?

It’s working btw

#6

the post_seens filter is working on the joined table (and therefore, the join), so its where it should be.

1 Like