Laravel查询：获取产品封面图，无封面则返回随机图片

Got it, let's fix this query so it returns all products—using their cover image if available, otherwise a random image from their collection. First, let's break down what's wrong with your current code:

Your existing where('product_pictures.cover_image', '=', 1) clause filters out:

1. All non-cover images for products that do have a cover
2. Entirely excludes products that don't have any cover image (since their product_pictures fields will be NULL after the left join, which fails the =1 check)

The Fix: Use Window Functions to Prioritize & Select the Right Image #

We can use SQL's ROW_NUMBER() window function to rank images per product:

• First, prioritize cover images (sort by cover_image DESC)
• For products without a cover, sort images randomly
• Then pick the top-ranked image for each product

Here's how to implement this in Laravel's query builder:

public static function get_all_products() {
    return \DB::table('products')
        // Join with a subquery that ranks images per product
        ->leftJoinSub(
            \DB::table('product_pictures')
                ->select(
                    'product_id',
                    'image_name',
                    // Rank images: cover first, then random
                    \DB::raw('ROW_NUMBER() OVER (
                        PARTITION BY product_id 
                        ORDER BY cover_image DESC, RAND()
                    ) as image_rank')
                ),
            'ranked_images',
            function ($join) {
                $join->on('products.id', '=', 'ranked_images.product_id')
                     // Only keep the top-ranked image for each product
                     ->where('ranked_images.image_rank', '=', 1);
            }
        )
        ->select('products.product_name', 'ranked_images.image_name')
        ->get();
}

How This Works: #

1. Subquery Ranking: The subquery assigns a rank to each image in product_pictures:
   • For products with a cover image, that image gets image_rank = 1
   • For products without a cover, a random image is assigned image_rank = 1
2. Left Join: We join this ranked subquery back to the products table, only keeping the top-ranked image per product
3. All Products Included: Since we use leftJoinSub, even products with no images will appear in the result (with image_name as NULL—you can adjust this if needed, e.g., add a default placeholder image)

Alternative: Raw SQL Version (if you prefer) #

If you want to write the raw SQL directly, it looks like this:

SELECT 
    p.product_name, 
    pi.image_name
FROM products p
LEFT JOIN (
    SELECT 
        product_id, 
        image_name,
        ROW_NUMBER() OVER (
            PARTITION BY product_id 
            ORDER BY cover_image DESC, RAND()
        ) as image_rank
    FROM product_pictures
) pi ON p.id = pi.product_id AND pi.image_rank = 1;

This will return every product from the products table, paired with its cover image (if exists) or a random image from its gallery.

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