Skip to main content

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 App\Models;

use Illuminate\Database\Eloquent\Model;

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

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

    ];

public function opciones(){
    return $this->hasMany('App\Models\OpcionProducto', 'producto_id');
}

public function skus(){
    return $this->hasMany('App\Models\ProductoSku', 'producto_id');
}
}
"Options" Model:
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

use App\Traits\HasCompositePrimaryKey;

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('App\Models\Producto', 'producto_id');
    }

    public function valores(){
        return $this->hasMany('App\Models\OpcionValorProducto', 'opcion_id', 'opcion_id');
    }

    public function skusValores(){
        return $this->hasMany('App\Models\SkuValor', 'opcion_id', 'opcion_id');
    }
}
"OptionValues" Model:
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

use App\Traits\HasCompositePrimaryKey;

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('App\Models\Producto', 'producto_id');
    }

    public function opcion(){
        return $this->belongsTo('App\Models\OpcionProducto', 'opcion_id', 'opcion_id');
    }
}
"Product_SKUS" model:
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

use App\Traits\HasCompositePrimaryKey;

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('App\Models\Producto', 'producto_id');
    }

    public function valoresSku(){
        return $this->hasMany('App\Models\SkuValor', 'sku_id');
    }

    }
}
"SKU_VALUES" model:
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

use App\Traits\HasCompositePrimaryKey;

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('App\Models\Producto', 'producto_id');
    }

    public function opcion(){
        return $this->belongsTo('App\Models\OpcionProducto', 'opcion_id', 'opcion_id');
    }

    public function sku(){
        return $this->belongsTo('App\Models\ProductoSku', 'sku_id', 'sku_id');
    }
}


Original post from: https://stackoverflow.com/questions/61803433/mysql-laravel-eloquent-get-record-when-two-columns-have-two-or-more-values-a by (Aarón Gutiérrez)


Answers:

After going through your question, this is the code I came up with. Of course this is un-tested. Please give this a shot.
$skuValor = SkuValor::with('producto', 'opcion.valores', 'sku')
    ->whereHas('producto', function ($q) use ($request) {
        $q->where('id', $request->get('product_id')); // id: 1
    })
    ->whereHas('opcion', function ($q) use ($request) {
        $q->whereIn('id', $request->get('option_ids')) // id: [1, 2] where 1=size, 2=color
        ->whereHas('valores', function($q2) use ($request) {
            $q2->whereIn('id', $request->get('value_ids')); // id: [1, 3] where 1=small, 3=pink
        });
    })
    ->get();

$skuValor->sku->id; // sky id
with() : This is called Eager Loading. Load some relationships when retrieving a model.
whereHas() : Querying Relationship Existence. This method allow you to add customized constraints to a relationship constraint.
use() : Passing data so that inner (that particular) query can use it. Notice we have used 1 for opcion and another one for valores.
whereIn() : This method verifies that a given column's value is contained within the given array.
Comment if you get stuck.
Answer by: Digvijay

Comments

Popular posts from this blog

Confusion between commands.Bot and discord.Client | Which one should I use?

Whenever you look at YouTube tutorials or code from this website there is a real variation. Some developers use client = discord.Client(intents=intents) while the others use bot = commands.Bot(command_prefix="something", intents=intents) . Now I know slightly about the difference but I get errors from different places from my code when I use either of them and its confusing. Especially since there has a few changes over the years in discord.py it is hard to find the real difference. I tried sticking to discord.Client then I found that there are more features in commands.Bot . Then I found errors when using commands.Bot . An example of this is: When I try to use commands.Bot client = commands.Bot(command_prefix=">",intents=intents) async def load(): for filename in os.listdir("./Cogs"): if filename.endswith(".py"): client.load_extension(f"Cogs.{filename[:-3]}") The above doesnt giveany response from my Cogs ...

How to show number of registered users in Laravel based on usertype?

i'm trying to display data from the database in the admin dashboard i used this: <?php use Illuminate\Support\Facades\DB; $users = DB::table('users')->count(); echo $users; ?> and i have successfully get the correct data from the database but what if i want to display a specific data for example in this user table there is "usertype" that specify if the user is normal user or admin i want to user the same code above but to display a specific usertype i tried this: <?php use Illuminate\Support\Facades\DB; $users = DB::table('users')->count()->WHERE usertype =admin; echo $users; ?> but it didn't work, what am i doing wrong? source https://stackoverflow.com/questions/68199726/how-to-show-number-of-registered-users-in-laravel-based-on-usertype

Why is my reports service not connecting?

I am trying to pull some data from a Postgres database using Node.js and node-postures but I can't figure out why my service isn't connecting. my routes/index.js file: const express = require('express'); const router = express.Router(); const ordersCountController = require('../controllers/ordersCountController'); const ordersController = require('../controllers/ordersController'); const weeklyReportsController = require('../controllers/weeklyReportsController'); router.get('/orders_count', ordersCountController); router.get('/orders', ordersController); router.get('/weekly_reports', weeklyReportsController); module.exports = router; My controllers/weeklyReportsController.js file: const weeklyReportsService = require('../services/weeklyReportsService'); const weeklyReportsController = async (req, res) => { try { const data = await weeklyReportsService; res.json({data}) console...