Building query

@webrobert Maybe my thinking is broken and it's a simple where on multiple columns... But my data are on rows, not on columns.

Let's say a user is editing the sku_id 41. The last 3 rows give me the value for the 3 attributes of this sku.

The 3 rows before that defined the sku_id 40 that shares the same article_attribut_valeur_id 279 for the attribute_id 41.

The combination of all **attribut_id / article_attribut_valeur_id ** should be unique for a specific article_id.

So, back to editing the sku 41, if the user change the value for the attribute_id 39 & 40 to the same article_attribut_valeur_id than that of the sku_id 40, i should prevent that change.

So how could i check if, for a given article_id, a sku exist with the same n (3 in my example) attribut_id => article_attribut_valeur_id and so i can prevent the user to save his changes ?

I think i must somehow group my query by sku_id and add a ->where("article_id", $article->id) but i cannot query each attribut_id at a time, i think i should query my n attribut_id in one query but i cannot do it...

@seb_run That’s not something you should do in a query, but in your table design.

Make a UNIQUE index constraint on article_id, attribut_id and article_attribut_valeur_id – then the database will automatically prevent you from inserting or updating rows with duplicate values, and you can then easily catch the exception in your application and show an error message to the user.

@kokoshneta Thx for ur reply.

I cannot set an unique index on article_id, attribut_id and article_attribut_valeur_id, it would prevent a Product (Article) from having 2 skus sharing a same article_attribut_valeur_id for a attribut_id

Example :

Product
id        Name
 19         Levi's 527 (<= the best ;)

attribut
id        Name
1           Length
2           Color

attribut_value

id							attribut_id						value
 1        						1						28				// Length 28	 													
 2        						1						30				// Length 30
 3       						1						32				// Length 28
 4       						2					    Blue			// Color Blue
 5       						2						Black			// Color Black

article_sku
So here's 2 Skus from this product_id 19.

Sku 1 
			Length 28 
			Color Blue
Sku 2
			Length 32
			Color Black		
															
id	sku_id	article_id		article_attribut_valeur_id	attribut_id
1		1			19				1				1         //Sku_id 1 is Length 28 
2		1			19				4				2		  //Sku_id 1 is Color Blue																	
3		2			19				3				1         //Sku_id 2 is Length 32
4		2			19				5				2         //Sku_id 2 is Color Black		
		

I want the db model to be able to handle a dynamic number of Attribute and Attribute value for a product, as many as the user would create.

A sku should represent a combination of those Attribute / Value for a Product, 1 row in article_sku per attribute / value.

Thx

I cannot set an unique index on article_id, attribut_id and article_attribut_valeur_id, it would prevent a Product (Article) from having 2 skus sharing a same article_attribut_valeur_id for a attribut_id

Sorry, it seems I accidentally left out the sku_id column. The index should be on sku_id, article_id, attribut_id, article_attribut_valeur_id. Each SKU should only allow one unique combination of article (product) ID, attribute type and attribute value – correct?

@kokoshneta Yes, it's right.

As for now, i have a unique index on sku_id, attribute_id which is the same thing. One Sku as one value for a given attribute.

I get your point that my model should not allow a sku to have multiple value for a attribute_id (and it would'nt) but i'd like to be able to check if a given set of attribut_id / attribut_value exist for a product_id.

Any idea on how i can query my db, with or without eloquent, to check if the article_id 19 has a sku with (Length 28 / Color Blue) so (attribute 1, value 1 | attribute 2, value 4) ?

@seb_run Ah, yes, of course – if each SKU can only define each attribute once (so Color Red and Color Black are separate SKUs), then the simpler index is all you need. Presumably you also have a unique index for sku_id, article_id to make sure two different products don’t accidentally end up under the same SKU.

Checking if an existing SKU already has a specific combination really is just simple where statements on multiple columns. Checking if an existing SKU already has multiple specific combinations is trickier, because you can’t match multiple rows with different values in one query. You can, however, group your where statements and make them inclusive rather than exclusive (i.e., use or instead of and), and then count the result:

SELECT COUNT(*) FROM article_sku WHERE sku_id = 1 AND article_id = 19 AND (
	(attribut_id = 1 AND article_attribut_valeur_id = 3)
	OR (attribut_id = 2 AND article_attribut_valeur_id = 4)
	OR (attribut_id = 3 AND article_attribut_valeur_id = 7)
)

This will give you the number of rows where any of the conditions you’re searching for is met. Compare that with the number of conditions you have, and you can see if an SKU exists with all of them:

// Attributes from a form submission or similar
// Make sure they’re in a nested array like so, with one attribute/value condition per element
$attributes = [
	['attribute' => 1, 'value' => 3],
	['attribute' => 2, 'value' => 4],
	['attribute' => 3, 'value' => 8]
];

$query = SKU::where('sku_id', $sku->id)
	->where('article_id', $sku->article_id)
	->where(function ($query) use ($attributes) {
		foreach($attributes as $a) {
			$query->orWhere(function ($query) use ($a) {
				$query->where('attribut_id', $a['attribute'])->where('article_attribut_valeur_id', $a['value']);
			});
		}
	})
;

if ($query->count() === count($attributes)) {
	// The combination exists
} else {
	// The combination doesn’t exist
}

@kokoshneta Brillant idea to count and compare to the number of attribut ! it's certainly a good way to approach this question.

The only problem is that i can't query on the sku_id because the all point of the query is to find if any sku already exist with a given set of attribut.

And if i remove the where sku_id = 1 in your request, it returns any row that match my attribut_id / article_attribut_valeur_id on the article_id, regardless of if they belong to the same sku_id. (sorry for my bad english).

In my previous example :

id	sku_id	article_id		article_attribut_valeur_id	attribut_id
1		1			19				1				1         //Sku_id 1 is Length 28 
2		1			19				4				2		  //Sku_id 1 is Color Blue																	
3		2			19				3				1         //Sku_id 2 is Length 32
4		2			19				5				2         //Sku_id 2 is Color Black	

If i launch your query without the sku_id to check if a sku exist in Length 32, color Blue

SELECT COUNT(*) FROM article_sku WHERE  article_id = 19 AND (
	(attribut_id = 1 AND article_attribut_valeur_id = 3)
	OR (attribut_id = 2 AND article_attribut_valeur_id = 4)
)

It will return 2 but in fact, the 2 comes from two different skus and the combination of Length 32, color Blue doesn't exist.

We have to find a way to apply the (XX or XX or XX) part of the query "GROUP BY" Sku...

I suppose i could get all the sku_id first, then launch the query with the where sku_id = XX for each sku_id...

I'm sure there's a cleaner way

@seb_run Ah, you don’t necessarily have an SKU to begin with!

In that case, you should be able to do COUNT(DISTINCT sku_id) AS ids – that should return either 0 or 1. Unfortunately, Eloquent doesn’t provide a neat, fluid way to do that, so you’d have to make the query a bit uglier.

Edit: Actually, perhaps it does – this is untested, but I think it should work:

$existing = SKU::distinct()
	->where('article_id', $sku->article_id)
	->where(function ($query) use ($attributes) {
		foreach($attributes as $a) {
			$query->orWhere(function ($query) use ($a) {
				$query->where('attribut_id', $a['attribute'])->where('article_attribut_valeur_id', $a['value']);
			});
		}
	})
	->count('sku_id')
;

if ($existing > 0) {
	// The combination exists
} else {
	// The combination doesn’t exist
}

@kokoshneta No, i don't always have a Sku.

The user should be able to setup a new Sku and in the db, i have 0 or N Sku representing each a unique combination of attribute.

My goal is to perform a clean test a gracefully display to the user that the Sku he's creating already exist if that's the case.

In your query, you set a ->where('sku_id', $sku->id) and select distinct sku_id. Shoudn't it always return 1 ?

If i remove the ->where('sku_id', $sku->id), i'm back to my previous explanation where the result couldn't be interpreted as i'm getting back a mix of sku that have 1 or more of my queried attribute.

During the night (morning here), i was thinking that i could maybe get somewhere using a different approach :

Querying the Sku table (not this sku_variation table) and using a multiple whereHas on this sku_variation table, 1 per attribute that i search for.

Something like this :

$query= Sku::query();
foreach($tabAttribute as $attribute_id=> $attribute_value_id){
      $query->whereHas('sku_variation', function (Builder $q) use ($product, $attribute_id, $attribute_value_id) {
       $q->where('product_id', $product->id)
                    ->where("attribute_id", $attribute_id)
                    ->where("product_attribute_value_id", $attribute_value_id);
        });
}
$skus = $query->get();

With this approach, i should work "group by sku_id" and get, if it exist, the Skus with this combination of attributes (Hopefully 1 max since the all point of this is to prevent a duplicate Sku on same Product with same combination of attributes).

I wil test this further today

@seb_run Just a quick note now before I’m off to bed, but just thought of this:

If you group your results by sku_id and then limit to groups having at least the same number of rows as the conditions you’re filtering by, I think that should give you the right result. Rows with different SKU IDs would end up in different groups, so they won’t falsely inflate the number.

So something like this:

SELECT sku_id FROM article_sku WHERE  article_id = 19 AND (
	(attribut_id = 1 AND article_attribut_valeur_id = 3) // $condition[1]
	OR (attribut_id = 2 AND article_attribut_valeur_id = 4) // $condition[2]
) GROUP BY sku_id HAVING COUNT(*) >= $conditionCount

Or in Eloquent:

$existing = SKU:select('sku_id')
	->where('article_id', $sku->article_id)
	->where(function ($query) use ($attributes) {
		foreach($attributes as $a) {
			$query->orWhere(function ($query) use ($a) {
				$query->where('attribut_id', $a['attribute'])->where('article_attribut_valeur_id', $a['value']);
			});
		}
	})
	->groupBy('sku_id')
	->havingRaw('COUNT(*) >= ?', count($attributes))
	->get()
;

if ($existing->count() > 0) {
	// The combination exists
} else {
	// The combination doesn’t exist
}

@kokoshneta

I think both our solutions are fine.

Thanks for all your help !

@seb_run If you managed to get it to work, you can mark the question as solved by clicking the ‘Set Best Answer’ button on the answer that provided the solution. That also gets it off the ‘Unsolved’ list.