MySQL (Laravel Eloquent) – Get record when two columns have two or more values at the same time

I have this database that I got from this post that manages products and its variants:

+---------------+     +---------------+
| PRODUCTS      |-----< PRODUCT_SKUS  |
+---------------+     +---------------+
| #product_id   |     | #product_id   |
|  product_name |     | #sku_id       |
+---------------+     |  sku          |
        |             |  price        |
        |             +---------------+
        |                     |
+-------^-------+      +------^------+
| OPTIONS       |------< SKU_VALUES  |
+---------------+      +-------------+
| #product_id   |      | #product_id |
| #option_id    |      | #sku_id     |
|  option_name  |      | #option_id  |
+---------------+      |  value_id   |
        |              +------v------+
+-------^-------+             |
| OPTION_VALUES |-------------+
+---------------+
| #product_id   |
| #option_id    |
| #value_id     |
|  value_name   |
+---------------+

The problem is, that I don’t know how would I get the SKU at the moment that a user selects the options of the product he wants:

SKU_VALUES
==========
product_id sku_id option_id value_id
---------- ------ --------- --------
1          1      1         1        (W1SSCW; Size; Small)
1          1      2         1        (W1SSCW; Color; White)
1          2      1         1        (W1SSCB; Size; Small)
1          2      2         2        (W1SSCB; Color; Black)

Let’s suppose that the user selects the product with ID 1 and the options size-small and color-black, how am I able to get the sku_id (in this case I would want value 2 from sku_id) in order to get the price that’s inside the PRODUCT_SKUS table.

I cannot do something like this for obvious reasons:

SELECT sku_id FROM SKU_VALUES 
WHERE (SKU_VALUES.option_id = 1 AND SKU_VALUES.value_id = 1) 
AND (SKU_VALUES.option_id = 2 AND SKU_VALUES.value_id = 2)

NOTE that it seems that I would need to append the same number of conditions (or whatever I need) as the number of options that are available from a product, in this case there are just 2 rows because the product has 2 options (size and color), but the product may have “n” options.

I would appreciate if someone could guide me for this query and if it’s possible doing it with Laravel Eloquent instead of using RAW query.

The models I have created are the following:

“Product” Model:

namespace AppModels;

use IlluminateDatabaseEloquentModel;

class Producto extends Model
{
    protected $table = 'productos';

    protected $fillable = [
        'nombre',
        'descripcion'

    ];

public function opciones(){
    return $this->hasMany('AppModelsOpcionProducto', 'producto_id');
}

public function skus(){
    return $this->hasMany('AppModelsProductoSku', 'producto_id');
}
}

“Options” Model:

namespace AppModels;

use IlluminateDatabaseEloquentModel;

use AppTraitsHasCompositePrimaryKey;

class OpcionProducto extends Model
{
    use HasCompositePrimaryKey;

    protected $table = 'productos_opciones';

    protected $primaryKey = array('producto_id', 'opcion_id');

    protected $fillable = [
        'producto_id',
        'opcion_id',
        'nombre_opcion',
        'valor'
    ];


    public function producto(){
        return $this->belongsTo('AppModelsProducto', 'producto_id');
    }

    public function valores(){
        return $this->hasMany('AppModelsOpcionValorProducto', 'opcion_id', 'opcion_id');
    }

    public function skusValores(){
        return $this->hasMany('AppModelsSkuValor', 'opcion_id', 'opcion_id');
    }
}

“OptionValues” Model:

namespace AppModels;

use IlluminateDatabaseEloquentModel;

use AppTraitsHasCompositePrimaryKey;

class OpcionValorProducto extends Model
{
    use HasCompositePrimaryKey;

    protected $primaryKey = array('producto_id', 'opcion_id', 'valor_id');

    protected $table = 'productos_opciones_valores';

    protected $fillable = [
        'producto_id',
        'opcion_id',
        'valor_id',
        'valor_variacion',
        'valor'
    ];


    public function producto(){
        return $this->belongsTo('AppModelsProducto', 'producto_id');
    }

    public function opcion(){
        return $this->belongsTo('AppModelsOpcionProducto', 'opcion_id', 'opcion_id');
    }
}

“Product_SKUS” model:

namespace AppModels;

use IlluminateDatabaseEloquentModel;

use AppTraitsHasCompositePrimaryKey;

class ProductoSku extends Model
{
    use HasCompositePrimaryKey;

    protected $primaryKey = array('producto_id', 'sku_id');

    protected $table = 'productos_skus';

    protected $fillable = [
        'producto_id',
        'sku_id',
        'imagen_id',
        'precio',
        'stock',
        'sku'
    ];


    public function producto(){
        return $this->belongsTo('AppModelsProducto', 'producto_id');
    }

    public function valoresSku(){
        return $this->hasMany('AppModelsSkuValor', 'sku_id');
    }

    }
}

“SKU_VALUES” model:

namespace AppModels;

use IlluminateDatabaseEloquentModel;

use AppTraitsHasCompositePrimaryKey;

class SkuValor extends Model
{
    use HasCompositePrimaryKey;

    protected $primaryKey = array('producto_id', 'sku_id', 'opcion_id');

    protected $table = 'valores_skus';

    protected $fillable = [
        'producto_id',
        'sku_id',
        'opcion_id',
        'valor_id',
    ];


    public function producto(){
        return $this->belongsTo('AppModelsProducto', 'producto_id');
    }

    public function opcion(){
        return $this->belongsTo('AppModelsOpcionProducto', 'opcion_id', 'opcion_id');
    }

    public function sku(){
        return $this->belongsTo('AppModelsProductoSku', 'sku_id', 'sku_id');
    }
}

Answer

Here is a solution using pure SQL.

This is your attempt using a raw query:

select sku_id 
from sku_values 
where (option_id = 1 and value_id = 1) and (option_id = 2 and value_id = 2)

This doesn’t work because you need to search across rows sharing the same sku_id rather than on each row. This suggest aggregation:

select sku_id
from sku_values
where (option_id, value_id) in ((1, 1), (2, 2)) -- either one combination or the other
group by sku_id
having count(*) = 2                             -- both match

You can easily extend the query for more options by adding more combinations in the where clause predicate and incrementing the target count in the having clause accordingly. For example, this filters on 4 criterias:

select sku_id
from sku_values
where (option_id, value_id) in ((1, 1), (2, 2), (3, 10) (12, 17))
group by sku_id
having count(*) = 4

It is also possible to filter by option names and values by adding more joins in the subquery:

select sv.sku_id
from sku_values sv
inner join options o 
    on  o.product_id = sv.product_id 
    and o.option_id  = sv.option_id
inner join option_values ov 
    on  ov.product_id = sv.product_id 
    and ov.option_id  = sv.option_id 
    and ov.value_id   = sv.value_id
where (o.option_name, ov.value_name) in (('Size', 'Small'), ('Color', 'Black'))
group by sv.sku_id
having count(*) = 2

Now, say you want to get the corresponding product name and price: you can join the above query with the relevant tables.

select p.product_name, ps.price
from products p
inner join product_skus ps 
    on  ps.product_id = p.product_id
inner join (
    select sv.sku_id
    from sku_values sv
    inner join options o 
        on  o.product_id = sv.product_id 
        and o.option_id  = sv.option_id
    inner join option_values ov 
        on  ov.product_id = sv.product_id 
        and ov.option_id  = sv.option_id 
        and ov.value_id   = sv.value_id
    where (o.option_name, ov.value_name) in (('Size', 'Small'), ('Color', 'Black'))
    group by sv.sku_id
    having count(*) = 2
) x 
    on  x.sku_id = ps.sku_id