How can I filter posts by status with Laravel's query builder?-CodePudding

I am working on a Laravel 8 app with users and posts.

From all the entries in the posts table, I need to make 2 lists of posts, depending on whether they are active or pending. The statuses come from a post_statuses table, with columns id and status.

In the Post model I have:

class Post extends Model {
    use HasFactory;
    
    protected $fillable = [
        'title',
        'category_id',
        'description',
        'body'
        'status'
    ];
}

In the controller:

namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Post;

class PostsController extends Controller {

    public $page_title = "Posts";

    public function index(Request $request) {
        $viewData = $this->loadViewData();
        $viewData['page_title'] = $this->page_title;
        $viewData['posts'] = $this->posts($request);
        // send active posts to the view
        return view('active_posts', $viewData);
    }

    public function posts(Request $request) {

            $query = $request->input('query');

            return Post::select (
                "posts.title",
                "posts.description",
                "posts.body",
                "categories.name as category_name",
                "post_statuses.status as post_status",
            )
            ->leftJoin("categories", "posts.category_id", "=", "categories.id")
            ->leftJoin("post_statuses", "posts.status", "=", "post_statuses.id")
            // filter by status
            ->where('post_statuses.status', '=', 'active')
            ->orWhere('title', 'like', '%' . $query . '%')
            ->orWhere('description', 'like', '%' . $query . '%')
            ->orWhere('body', 'like', '%' . $query . '%')
            ->orWhere('categories.name', 'like', '%' . $query . '%')
            ->orWhere('post_statuses.status', 'like', '%' . $query . '%')
            ->orderBy('posts.id','DESC')
            ->paginate(10);
    }
}

The problem

In the active_posts view, all the posts are displayed, regardless of their status.

Where is my mistake?

CodePudding user response：

I guess orWhere is messing up with your query.

Try like this:

    public function posts(Request $request) {

        $query = $request->input('query');

        return Post::select (
            "posts.title",
            "posts.description",
            "posts.body",
            "categories.name as category_name",
            "post_statuses.status as post_status",
            )
            ->leftJoin("categories", "posts.category_id", "=", "categories.id")
            ->leftJoin("post_statuses", "posts.status", "=", "post_statuses.id")
            // filter by status
            ->where('post_statuses.status', '=', 'active')->where(function ($q) use($query){
                $q->orWhere('title', 'like', '%' . $query . '%')
                    ->orWhere('description', 'like', '%' . $query . '%')
                    ->orWhere('body', 'like', '%' . $query . '%')
                    ->orWhere('categories.name', 'like', '%' . $query . '%')
                    ->orWhere('post_statuses.status', 'like', '%' . $query . '%');
            })
            ->orderBy('posts.id','DESC')
            ->paginate(10);
    }