exclude first 3 rows, arranging by vote count

@MohamedTammam hello sir, I have this on my variable

        $latestPost = Post::where('active', '=', 1)
            ->where('published_at', '<', Carbon::now())
            ->orderBy('published_at', 'desc')
            ->with('categories')
            ->limit(3)
            ->first();

If i change first to ->get() i get an error because If im not mistaken get retrieves a collection. This error appears:

Method Illuminate\Database\Eloquent\Collection::getThumbnail does not exist.

It's a relationship i have currently on my Posts model, i am just making sure I retrieve the thumbnail with that method.

how can i overcome this, i simply want to use this variable to retrieve the frist 3 posts so i dont have to make a 2nd variable just for the other 2 posts. Thank you.

@warpig I don't understand what do you want exactly, but if your question on how to get the first three posts and store the first on a variable, here's how

$firstPosts = Post::where('active', '=', 1)
            ->where('published_at', '<', Carbon::now())
            ->orderBy('published_at', 'desc')
            ->with('categories')
            ->limit(3)
            ->get();

$latestPost = $firstPosts->count() ? $firstPosts[0] : null;

@warpig

If i change first to ->get() i get an error because If im not mistaken get retrieves a collection. This error appears:

you MUST use get. you want a collection of posts

This error appears:

because you must loop over the collection to get the image for individual posts

@MohamedTammam

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous 

        //show the latest post created
        $latestPosts = Post::where('active', '=', 1)
            ->where('published_at', '<', Carbon::now())
            ->orderBy('published_at', 'desc')
            ->limit(3)
            ->get();

        // show the 3 most popular posts
        $popularPosts = Post::query()
            ->whereNotIn('id', $latestPosts->pluck('id'))
            ->leftJoin('upvote_downvotes', 'post_id', '=', 'upvote_downvotes.post_id')
            ->select('posts.*', DB::raw('COUNT(upvote_downvotes.id) as upvote_count'))
            ->where(function($query) {
                $query->whereNull('upvote_downvotes.is_upvote')
                    ->orWhere('upvote_downvotes.is_upvote', '=', 1);
            })
            ->where('active', '=', 1)
            ->where('published_at', '<', Carbon::now())
            ->orderByDesc('upvote_count')
            ->groupBy('posts.id')
            ->limit(8)
            ->get();

@warpig Change this line

->whereNotIn('id', $latestPosts->pluck('id'))

To be

->whereNotIn('posts.id', $latestPosts->pluck('id'))

@MohamedTammam thanks, and how do i exclude the current post from showing in the "latest posts" sidebar while being in the the post.view page?

i've tried this ->where('id', '!=', $post->id) but doesn't work

    public function show(Post $post, Request $request)
    {
        if (!$post->active || $post->published_at > Carbon::now()) {
            throw new NotFoundHttpException();
        }
	        $popularPosts = Post::query()
            ->where('id', '!=', $post->id)
            ->get();
}

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous

@warpig It tells you..... you are joining tables and there are two tables with id

        $popularPosts = Post::query()
        ->where('posts.id', '!=', $post->id)
        ->get();

@Snapey i thought i was saying not to use the post id of the current post id, current post id being the variable in relation with the id column last thing i want to do is join anything wtf

@warpig in which case, the error is from some other code. Look more closely at the error message.

Your previous code, you were definitely joining tables. My guess is that still the code you are running.

@Snapey yes, definitely, im making a left join with the "upvote_downvotes", and the "posts" table, but then what thing in common from these 2 tables can i use to exclude the current post and avoid from getting it rendered? this being the code:

        $popularPosts = Post::query()
            ->where($post->id, '=', 'post_id')->pluck('id')
            ->leftJoin('upvote_downvotes', 'post_id', '=', 'upvote_downvotes.post_id')
            ->select('posts.*', DB::raw('COUNT(upvote_downvotes.id) as upvote_count'))
            ->where(function($query) {
                $query->whereNull('upvote_downvotes.is_upvote')
                    ->orWhere('upvote_downvotes.is_upvote', '=', 1);
            })
            ->where('active', '=', 1)
            ->where('published_at', '<', Carbon::now())
            ->orderByDesc('upvote_count')
            ->groupBy('posts.id')
            ->limit(5)
            ->get();

i think im missing to communicating the id of the post... i believe i am doing the right thing in grabbing the current post id ?? and then applying the pluck but it doesn't make human sense to say "grab this current post and exclude it from itself" that's how im viewing it. sorry if that doesn't make any sense 🤷‍♂️

@warpig I already showed you by using posts.id

but why have you ruined your query with this crap;

->where($post->id, '=', 'post_id')->pluck('id')

        $popularPosts = Post::query()
            ->whereNotIn('id', $latestPosts->pluck('id'))
            ->where('posts.id', '!=', $post->id)
            ->leftJoin('upvote_downvotes', 'post_id', '=', 'upvote_downvotes.post_id')
            ->select('posts.*', DB::raw('COUNT(upvote_downvotes.id) as upvote_count'))
            ->where(function($query) {
                $query->whereNull('upvote_downvotes.is_upvote')
                    ->orWhere('upvote_downvotes.is_upvote', '=', 1);
            })
            ->where('active', '=', 1)
            ->where('published_at', '<', Carbon::now())
            ->orderByDesc('upvote_count')
            ->groupBy('posts.id')
            ->limit(8)
            ->get();

@Snapey yea that might be correct only that i forgot to mention i am doing this from another function but in the same controller. so i guess calling the home function and then grabbing the $latestpost like this:

->whereNotIn('id', $this->home($latestPosts)->pluck('id'))

needs to be switched to something else, or maybe that's not where the error is..

@warpig who can tell? Just seems complete chaos from here, and if you can't read error messages then you are going to struggle.

@Snapey thank you, for your time invested in here now get lost lol

@warpig bye