I have two tables posts as a parent and 'posts_tags' as a child, I want to search by two fields year from the post table and also tag_id from posts_tags, how to add search query for child table in our query.

My Models Post.php

<?php
namespace App\Models\Posts;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    use HasFactory;

    protected $table="posts";
    /**
     * The attributes that are mass assignable.
     *
     * @var string[]
     */
    protected $guarded = [];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [];

    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [];

    //---Get Post Tags
    public function PostTags() {
        return $this->hasMany('App\Models\Posts\PostTag');
    }//---End of Function PostTags
}

PostTag.php

<?php
namespace App\Models\Posts;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class PostTag extends Model
{
    use HasFactory;

    protected $table="posts_tags";

    //protected $primaryKey = 'post_tag_id';
    /**
     * The attributes that are mass assignable.
     *
     * @var string[]
     */
    protected $guarded = [];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [];

    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [];

    //---Get The Post
    public function GetPost() {
        return $this->belongsTo('App\Models\Posts\Post');
    }//---End of Function GetPost
}

My View

<form action="{{ route('movies.movie_search') }}" method="GET">
    @csrf
    <select name="tag" >
        <option value="" selected="selected">
            @lang('movies.movie_all')
        </option>
        @if ($tags)
            @foreach ($tags AS $tag)
                <option value="{{ $tag->id }}">{{ $tag->name }}</option>
            @endforeach
        @endif
    </select>
    <select name="year" >
        <option value="" selected="selected">
            @lang('public.public_year')
        </option>
        @php
            $date = date('Y');
            for ($i = $date; $i > 1970; $i--) {
                echo "<option value=".$i.">".$i."</option>";
            }
        @endphp
    </select>
    <button type="submit" name="" >
        @lang('public.public_filter')
    </button>
</form>

web.php

Route::prefix('/movies')->group(function (){
    Route::get('/search', [MovieController::class, 'MoviesDataSearch'])->name('movies.movie_search');
});

MovieController.php

<?php

namespace App\Http\Controllers\Movies;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Crypt;
use App\Models\Posts\Post;
use App\Models\Posts\PostTag;

class MovieController extends Controller
{
    //---Filter Movies By Tags & Year
    public function MoviesDataSearch(Request $request) {
        if (empty($request)) {
            return Post::where('post_type', "movie")->where('is_delete', "0")->orderBy('id', 'DESC')->Paginate(12);
        } else {
            $movies = Post::where('post_type', "movie")
                ->where('is_delete', "0")
                ->where('year', 'like', '%'.$request->year.'%')

                //---How to add a query for posts_tags


                /*->whereHas('posts_tags', function ($posts_tags) use ($request) {
                    $posts_tags->where('tag_id', 'like', '%'.$request->tag.'%');
                })*/
                ->orderBy('id', 'DESC')
                ->Paginate(2);

            return $movies->appends($request->all());
        }
    }//---End of Function MoviesDataSearch
}

CodePudding user response：

You could use a join clause. Something like this:

$movies = Post::join('post_tags', [['posts.id', 'posts_tags.post_id']])
    ->where('posts.post_type', "movie")
    ->where('posts.is_delete', "0")
    ->where('posts.year', 'like', '%'.$request->year.'%')
    ->where('posts_tags.tag_id', 'like', '%'.$request->tag.'%')
    ->orderBy('posts.id', 'DESC')
    ->Paginate(2);

I have assumed that the foreign key in posts_tags is postsid, but I really do not know this, you will have to place the corresponding field.