Jump to content
php.lv forumi

MySQL produktu atribūti


Sasa
 Share

Recommended Posts

select distinct products.* from products
WHERE
(select COUNT(*) from product_has_specification, specifications WHERE product_has_specification.spec_id = specifications.id_specification AND product_has_specification.value IN ('S','black') AND products.product_id = product_has_specification.product_id ) = 2

Ja es pareizi sapratu ka gribi visus produktus ar atribūtiem :

  • melns
  • izmērā S

Cik atribūtus gribi lai sakrīt - tik raksti ciparu 2,3,5 utt.

Kas liktos loģiski - vismaz man.

 

Es īsti nesapratu "OR" tavā pieprasījumā (nelikās) - ja gribi visus produktus kam ir kaut viens atribūts ir ... šim pašam pieprasījumam vari uzlikt > 0

Edited by zeCode
Link to comment
Share on other sites

SELECT 
   p.product_id, COUNT(*) as atribute_count 
FROM 
   product_has_specification p,  specifications s
WHERE
   p.value IN ('black','s') AND p.spec_id = s.id_specification
GROUP BY
   p.product_id 
HAVING 
   atribute_count = 2

Varbūt izlaid products table, un pie attēlošanas pieprasi visu nepieciešamo no tabulas

Link to comment
Share on other sites

Uz kādu apjomu ierakstiem mēģināji? Cik explain rindas atgrieza?

Ja svarīgi ir produkta nosaukums to noteikti vari selekta daļā pievienot...

SELECT 
   p.product_id, (SELECT products.name FROM products WHERE products.product_id =   p.product_id) as name, COUNT(*) as atribute_count 
FROM 
Link to comment
Share on other sites


select products.*

from products

 

inner join product_has_specification as prodSpec1

on prodSpec1.product_id = products.product_id and

prodSpec1.value ='black' and

prodSpec1.spec_id = 1

 

inner join product_has_specification as prodSpec2

on prodSpec2.product_id = products.product_id and

prodSpec2.value ='s' and

prodSpec2.spec_id = 2

Link to comment
Share on other sites

inner join product_has_specification as prodSpec%i%

  on prodSpec%i%.product_id = products.product_id and

     prodSpec%i%.value ='%value%' and

     prodSpec%i%.spec_id = %atributeid%

man liekas katram atribūtam tā ir lielāka apstrāde ( cikls kas izveido sql body) - bet tas ir subjektīvi

 

select products.* from products
WHERE
(
	select COUNT(*) 
	from product_has_specification p, specifications s
	WHERE 
	p.spec_id = s.id_specification AND CONCAT(p.value,p.spec_id) IN ('s2','black1') 
	AND products.product_id = p.product_id 
) = 2
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...