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');
}
}
Advertisement
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