使用Laravel的Eloquent查询构建

Question

I'm trying to code a search functionality which not only allows me to search by a category of a question but also by its content. I'm using PostgreSQL and its ts_vector capabilities for the content. For the categories, I want to use Laravel's Eloquent and Query Builder.

The content search is fully working, but I can't figure out how to limit the displayed questions to the ones with those categories. This is what I have so far:

$questions = Question::search($query_string)->paginate(NUM_PER_PAGE);

I also have an array of strings which are category names ($tag_names). As you can see from the schema each name is unique.

This is the schema:

CREATE TABLE questions (
    id BIGINT PRIMARY KEY REFERENCES commentables(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    correct_answer BIGINT UNIQUE,
    search tsvector
);

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    num_posts INTEGER DEFAULT 0 NOT NULL
);

CREATE TABLE questions_categories (
    question_id BIGINT REFERENCES questions(id) ON DELETE CASCADE,
    category_id INTEGER REFERENCES categories(id) ON DELETE CASCADE,
    PRIMARY KEY (question_id, category_id)
);

I've created the Model Classes for each of the database tables, so you can refer to them by Question, Category and QuestionsCategory.

Answer 1

Define a categories relationship in your Question model:

public function categories() {
    return $this->belongsToMany(Category::class, 'questions_categories')
}

Then extend your query:

$questions = Question::search($query_string)
    ->whereHas('categories', function($query) use($categoryIds) {
        $query->whereIn('id', $categoryIds);
    })->paginate(NUM_PER_PAGE);