Laravel Eloquent ORM：如何精确查询指定尺码的商品数据？

Hey there, I’ve dealt with this exact problem before—comma-separated fields feel convenient at first, but they get messy fast when you need precise matching. Let’s walk through a few solid fixes for your Laravel query:

1. Use MySQL's FIND_IN_SET Function (Quick & Clean Fix) #

This is the simplest approach for your current database structure. MySQL's FIND_IN_SET function is built specifically to search for an exact value within a comma-separated string, so it won’t mistakenly match "XL" or "XXL" when you’re looking for "L".

Here’s how to implement it in Laravel:

return DB::table('products')
    ->whereRaw("FIND_IN_SET(?, sizes)", [$size])
    ->paginate(1);

The ? acts as a parameter placeholder to prevent SQL injection—always a good practice for security.

2. Use Regular Expressions (Alternative Quick Fix) #

If you’d rather avoid whereRaw, you can use a regex pattern that targets the size as a standalone value (whether it’s at the start, end, or middle of the sizes string):

return DB::table('products')
    ->where('sizes', 'REGEXP', "^$size$|,$size,|^$size,|,$size$")
    ->paginate(1);

Let’s break down what this regex does:

• ^$size$: Matches when the sizes field is exactly the target size (e.g., just "L" with no other sizes)
• ,$size,: Matches when the size is sandwiched between other sizes (e.g., "M,L,XL")
• ^$size,: Matches when the size is at the start of the string (e.g., "L,M,XL")
• ,$size$: Matches when the size is at the end of the string (e.g., "XS,M,L")

3. Long-Term: Refactor Your Database Structure (Best Practice) #

While the above fixes work for immediate needs, comma-separated fields aren’t ideal for relational databases. For a scalable, maintainable solution, refactor to use a separate pivot table (the standard many-to-many approach):

1. Create a product_sizes table with columns: product_id (foreign key linking to your products table) and size (varchar for the size value)
2. Update your Product model to define a relationship:

public function sizes()
{
    return $this->hasMany(ProductSize::class);
    // Or use belongsToMany if you have a separate `sizes` table with unique size records
}

3. Now your query becomes clean, efficient, and easy to extend:

return Product::whereHas('sizes', function ($query) use ($size) {
    $query->where('size', $size);
})->paginate(1);

This approach makes it simpler to add/remove sizes, run complex filtering, and keeps your database normalized.

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