同時從父表和子表中過濾（搜索）

我有兩個表posts作為父表，“posts_tags”作為子表，我想從表中搜索兩個欄位yearpost和tag_id from posts_tags，如何在我們的查詢中添加子表的搜索查詢。

我的模型 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
}

郵政標簽.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
}

我的觀點

<form action="{{ route('movies.movie_search') }}" method="GET">
    @csrf
    <select name="tag" class="select">
        <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" class="select">
        <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="" class="filter">
        @lang('public.public_filter')
    </button>
</form>

網頁.php

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

電影控制器.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
}

uj5u.com熱心網友回復：

您可以使用 join 子句。像這樣的東西：

$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);

我假設posts_tags中的外鍵是postsid，但我真的不知道這個，你必須放置相應的欄位。

標籤：php mysql 拉拉维尔

上一篇：在Laravel中返??回JSON回應以通過API進行重復的電子郵件登錄

下一篇：SQLServer決議XML命名空間