Jump to content
php.lv forumi
Sign in to follow this  
Sasa

MySQL produktu atribūti

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

Share this post


Link to post
Share on other sites

Ja būs 5 atribūti bus 5 vaicājumi.  noteikti tas jāveido 1nā apakš vaicājumā - product_has_specification.value (implodo visas vērtības un salīdzini ar  IN ) un pārbaudi skaitu  = x  (counto visus elementus masīvā)

Share this post


Link to post
Share on other sites

Jā, šādi var krietni noīsināt, darīšu tā.

 

Edit:

Otra lieta kas ir ne mazāk svarīga ir tā ka notiek Full table scan produktu tabulai :(

Edited by Sasa

Share this post


Link to post
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

Share this post


Link to post
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 

Share this post


Link to post
Share on other sites

kāpēc ir vajadzīgs join ar specifications?

vai nevajag pārbaudīt konkrētu specification, jo pretējā gadījumā var tikt atrasts nepareizs specification (nevis s izmērs, bet s krāsa, ja tāda ir)?

Share this post


Link to post
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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

Aha un ja specification vērtības ir cipari kā piemēram type?

 

Vispār kāda starpība ko ģenerēt joinus vai tos concat?

Ja izmanto fw tas būs vienkāršs un saprotams cikls:

while ($specs as $spec)
{
    $model->join('product_has_specification',...);
}

Share this post


Link to post
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...
Sign in to follow this  

×
×
  • Create New...