Home > OS >  Get results from related table in Laravel
Get results from related table in Laravel

Time:03-15

I am beginner web developer. I make my project in Laravel 8.

I have articles. One article have many quest and categories. I make this on selected_categories and selected_quests.

I have this models:

Schema::create('articles', function (Blueprint $table) {
            $table->id();
            $table->bigInteger('company_id')->unsigned();
            $table->foreign('company_id')->references('id')->on('companies')->onDelete('cascade');
            $table->boolean('enable')->default(false);
            $table->text('author')->nullable();
            $table->text('title');
            $table->text('date_of_publication')->nullable();
            $table->text('publisher')->nullable();
            $table->string('page_number')->nullable();
            $table->string('filename')->nullable();
            $table->timestamps();
            $table->softDeletes();
            $table->engine = "InnoDB";
            $table->charset = 'utf8mb4';
            $table->collation = 'utf8mb4_unicode_ci';
        });

Schema::create('quests', function (Blueprint $table) {
            $table->id();
            $table->bigInteger('company_id')->unsigned();
            $table->foreign('company_id')->references('id')->on('companies')->onDelete('cascade');
            $table->boolean('enable')->default(false);
            $table->string('name')->nullable();
            $table->timestamps();
            $table->softDeletes();
            $table->engine = "InnoDB";
            $table->charset = 'utf8mb4';
            $table->collation = 'utf8mb4_unicode_ci';
        });

Schema::create('categories', function (Blueprint $table) {
            $table->id();
            $table->bigInteger('company_id')->unsigned();
            $table->foreign('company_id')->references('id')->on('companies')->onDelete('cascade');
            $table->boolean('enable')->default(false);
            $table->string('name')->nullable();
            $table->timestamps();
            $table->softDeletes();
            $table->engine = "InnoDB";
            $table->charset = 'utf8mb4';
            $table->collation = 'utf8mb4_unicode_ci';
        });

Schema::create('selected_article_quests', function (Blueprint $table) {
            $table->id();
            $table->bigInteger('article_id')->unsigned()->default(0);
            $table->foreign('article_id')->references('id')->on('articles')->onDelete('cascade');
            $table->bigInteger('quest_id')->unsigned()->default(0);
            $table->foreign('quest_id')->references('id')->on('quests')->onDelete('cascade');
            $table->timestamps();
            $table->softDeletes();
        });

Schema::create('selected_article_categories', function (Blueprint $table) {
            $table->id();
            $table->bigInteger('article_id')->unsigned()->default(0);
            $table->foreign('article_id')->references('id')->on('articles')->onDelete('cascade');
            $table->bigInteger('category_id')->unsigned()->default(0);
            $table->foreign('category_id')->references('id')->on('categories')->onDelete('cascade');
            $table->timestamps();
            $table->softDeletes();
        });

and controller:

public function searchResult(Request $request)
    {
        $query = Article::active();

        if ($request->input('query') !='') {
            $query->where(function ($query) use ($request) {
                $query->orWhere('title', 'like', '%' . $request->input('query') . '%');
                $query->orWhere('author', 'like', '%' . $request->input('query') . '%');
                $query->orWhere('publisher', 'like', '%' . $request->input('query') . '%');
            });
        }

        if ($request->input('author') !='') {
            $query->where('author', 'like', '%' . $request->input('author') . '%');
        }

        if ($request->input('title') !='') {
            $query->where('title', 'like', '%' . $request->input('title') . '%');
        }

        if ($request->input('date_from') !='') {
            $query->whereDate('date_of_publication', '>=', $request->input('date_from'));
        }

        if ($request->input('date_to') !='') {
            $query->whereDate('date_of_publication', '<=', $request->input('date_to'));
        }

        if ($request->input('quest') !='') {
            // ??
        }

        if ($request->input('category') !='') {
            // ??
        }

        $query = $query->orderBy('title')->paginate(40);

        return view('frontend.results',
            [
                'keywords' => env('KEYWORDS'),
                'descriptions' => env('DESCRIPTION'),
                'title' => env('TITLE'),
                'results' => $query,
            ]);
    }

This code is search engine on my website. I need add to my controller search by selected quests and selected category.

How can I make it?

CodePudding user response:

Okay, first of all, Request contains a has() method, use that in your ifs.

On the problem itself, I presume you have created the necessary relations on your Article model, don't really know what the active() static method does, if so, you can do:

if ($request->has('quest')) {
    $query->whereHas('quests', fn (Builder $builder) => $builder->where('name', 'like', '%' . $request->input('quest') . '%')));
}

and

if ($request->has('category')) {
    $query->whereHas('categories', fn (Builder $builder) => $builder->where('name', 'like', '%' . $request->input('category') . '%')));
}

Hope this helps get you on the right track.

  • Related