Laravel Eloquent关联查询：仅筛选所有产品共有的相同taxonomy列值的分类

Got it, let's tackle this problem step by step. Your goal is to retrieve only those pa_-prefixed taxonomies that are associated with every single product in the target ProductCategory (ID 401 in your case). Here's how to adjust your Eloquent query to make this happen:

Core Idea #

The key is to:

1. Target all products in the specified category.
2. For each pa_ taxonomy, count how many of these category products link to it.
3. Keep only the taxonomies where this count matches the total number of products in the category (meaning every product has this taxonomy).

Adjusted Eloquent Query #

// Fetch the target product category first
$category = \Corcel\WooCommerce\Model\ProductCategory::find(401);

// Retrieve products along with ONLY the shared pa_ taxonomies
$products = $category->products()->with(['taxonomies' => function ($query) use ($category) {
    $query->where('taxonomy', 'like', 'pa_%')
          // Join term relationships to connect taxonomies to category products
          ->join('wp_term_relationships as tr', 'wp_term_taxonomy.term_taxonomy_id', '=', 'tr.term_taxonomy_id')
          ->join('wp_term_relationships as tr_category', 'tr.object_id', '=', 'tr_category.object_id')
          // Filter to only products in our target category
          ->where('tr_category.term_taxonomy_id', $category->term_taxonomy_id)
          // Group by taxonomy to count associated products
          ->groupBy('wp_term_taxonomy.taxonomy', 'wp_term_taxonomy.term_taxonomy_id')
          // Keep only taxonomies linked to ALL products in the category
          ->havingRaw('COUNT(DISTINCT tr.object_id) = (
              SELECT COUNT(DISTINCT object_id) 
              FROM wp_term_relationships 
              WHERE term_taxonomy_id = ?
          )', [$category->term_taxonomy_id]);
}])->get(['ID'])->makeHidden(['pivot']);

Breakdown of the Query #

• Joins: We use two joins on wp_term_relationships to bridge taxonomies to the products in our target category. This lets us count how many category products are associated with each pa_ taxonomy.
• Group By: Grouping by taxonomy (and term_taxonomy_id to avoid conflicts if multiple terms share the same taxonomy slug) lets us aggregate product counts per taxonomy.
• Having Clause: The subquery calculates the total number of products in the target category. We only retain taxonomies where the count of linked products matches this total—ensuring every product in the category has this taxonomy.

Alternative: Precompute Total Product Count #

If you prefer to calculate the total product count upfront (which might be more efficient if you reuse this value elsewhere), use this version:

$category = \Corcel\WooCommerce\Model\ProductCategory::find(401);
$totalCategoryProducts = $category->products()->count();

$products = $category->products()->with(['taxonomies' => function ($query) use ($totalCategoryProducts, $category) {
    $query->where('taxonomy', 'like', 'pa_%')
          ->join('wp_term_relationships as tr', 'wp_term_taxonomy.term_taxonomy_id', '=', 'tr.term_taxonomy_id')
          ->join('wp_term_relationships as tr_category', 'tr.object_id', '=', 'tr_category.object_id')
          ->where('tr_category.term_taxonomy_id', $category->term_taxonomy_id)
          ->groupBy('wp_term_taxonomy.taxonomy', 'wp_term_taxonomy.term_taxonomy_id')
          ->havingRaw('COUNT(DISTINCT tr.object_id) = ?', [$totalCategoryProducts]);
}])->get(['ID'])->makeHidden(['pivot']);

Why This Works #

You mentioned wondering how to access wp_posts.ID in the with subquery—but we don't need to target individual product IDs here. Instead, we're aggregating across all products in the category to find taxonomies that are universal to all of them. This approach ensures we only load the taxonomies that meet your "shared by all products" requirement.

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