where query doesnt work right and I can’t find out why? - development

where query doesnt work right and I can’t find out why?

I have written this query using eloquent in laravel but it acts strangely .the query that i have written here is working properly:

$likes = Like::select('likes.post_id')
        ->whereIn('likes.user_account_id', $accounts_ids);
$bids = Bid::select('bids.post_id')
        ->whereIn('bids.user_account_id', $accounts_ids);
$visits = PostVisit::select('post_visits.post_id')
        ->whereIn('post_visits.user_account_id', $accounts_ids);

$raw_query = $visits->union($likes)->union($bids);



$data = DB::table(DB::raw("({$raw_query->toSql()}) as sub"))->select(
        DB::raw('second_hashtag_post.*'),
        DB::raw('Concat("https://", post_images.file_system , "." , "' . env('FS_URL') . 'asset/img/posts/small/", posts.id, "_", post_images.order, ".", post_images.image_mime_type) as image_url'),
        DB::raw('count(*) as rate'),
        DB::raw('Rand() as random_number')
    )->mergeBindings($raw_query->getQuery())
        ->join('hashtag_post as first_hashtag_post', 'sub.post_id', '=', 'first_hashtag_post.post_id')
        ->join('hashtag_post as second_hashtag_post',     'first_hashtag_post.hashtag_id', '=', 'second_hashtag_post.hashtag_id')
        ->join('posts', function ($join) use ($accounts_ids){
            $join->on('second_hashtag_post.post_id', '=', 'posts.id')
                ->where('posts.deleted_at', null)
                ->where('posts.post_show_status_id', 1)
                ->whereNotIn('posts.user_account_id', $accounts_ids);
        })->leftJoin('post_images', function ($join){
            $join->on('posts.id', '=', 'post_images.post_id')
                ->where('order', 1);
        })
        ->groupby('posts.id')
        ->orderby('rate', 'desc')
        ->get();

but when I add the condition

  ->whereNotIn('posts.post_state_id', [4,5])

or any where condition for posts.post_state_id join then the query returns nothing,it’s strange because before that it returns many raws and most of them satisfy this condition?

from Laravel Questions and Answers https://laravelquestions.com/laravel/where-query-doesnt-work-right-and-i-cant-find-out-why/
via Lzo Media