Laravel查询构建器关联：多态关联下查询主题类产品

Hey there! Let's walk through common polymorphic relationship operations using Laravel's query builder, based on your database setup. First, I spotted a tiny typo in your original code: your products table uses commentable_type as the polymorphic type field, but your query references productable_type—let's fix that first, then dive into more useful operations.

Corrected Base Query (Get All Theme-Associated Products) #

This is the fixed version of your initial code to fetch products linked to Themes:

$themeProducts = DB::table('products')
    ->orderBy('id', 'asc')
    ->where('commentable_type', Theme::class)
    ->get();

1. Join to Fetch Associated Theme Data #

If you want to pull in data from the themes table alongside your products (like the composer_package value), use a conditional join to enforce the polymorphic type match:

$themeProductsWithDetails = DB::table('products')
    ->join('themes', function ($join) {
        $join->on('products.commentable_id', '=', 'themes.id')
             ->where('products.commentable_type', Theme::class);
    })
    ->select('products.*', 'themes.composer_package as theme_composer_package')
    ->orderBy('products.id', 'asc')
    ->get();

The join closure ensures we only match products that are actually linked to a Theme, not just any random ID match.

2. Filter Products by a Theme's Field #

Suppose you need products linked to a specific Theme (e.g., one with a particular composer_package). Use whereExists to create a subquery that checks the associated Theme's data:

$specificThemeProducts = DB::table('products')
    ->where('commentable_type', Theme::class)
    ->whereExists(function ($query) {
        $query->select(DB::raw(1))
              ->from('themes')
              ->whereColumn('themes.id', 'products.commentable_id')
              ->where('themes.composer_package', '=', 'vendor/laravel-bootstrap-theme');
    })
    ->orderBy('products.id', 'asc')
    ->get();

Alternatively, you could use a join here too—whereExists is great for avoiding duplicate product rows (though in your case, IDs are unique, so either approach works).

3. Get Products for Multiple Polymorphic Types #

If you ever need to fetch products linked to both Themes and Plugins, use whereIn on the commentable_type field:

$multiTypeProducts = DB::table('products')
    ->whereIn('commentable_type', [Theme::class, Plugin::class])
    ->orderBy('id', 'asc')
    ->get();

4. Count Products per Theme #

To generate a report of how many products each Theme has associated with it, use a left join and grouping:

$themeProductCounts = DB::table('themes')
    ->leftJoin('products', function ($join) {
        $join->on('themes.id', '=', 'products.commentable_id')
             ->where('products.commentable_type', Theme::class);
    })
    ->select('themes.id', 'themes.composer_package', DB::raw('count(products.id) as product_count'))
    ->groupBy('themes.id', 'themes.composer_package')
    ->get();

The left join ensures even Themes with no products show up with a count of 0.

Quick Note: Eloquent Alternative #

While you asked for query builder operations, if you're using Laravel's Eloquent models, defining the polymorphic relationships will make these tasks even cleaner. For example:

• In your Product model:

  public function commentable()
  {
      return $this->morphTo();
  }
  

• In your Theme model:

  public function products()
  {
      return $this->morphMany(Product::class, 'commentable');
  }
  

Then you could fetch Theme products with:

$themeProducts = Theme::with('products')->get();

内容的提问来源于stack exchange，提问作者user7301337