www.webdeveloper.com
Results 1 to 3 of 3

Thread: [RESOLVED] MySQL product multi filter

  1. #1
    Join Date
    Oct 2005
    Location
    UK
    Posts
    630

    resolved [RESOLVED] MySQL product multi filter

    I have the following 4 tables (simplified for example) for a product filter scenario each product. Each product can have multiple attributes which have multiple values (but only ever one assigned to a product.
    Code:
    CREATE TABLE `products` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(150) NOT NULL,
    )
    
    CREATE TABLE `attributes` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(20) character set latin1 NOT NULL
    )
    
    CREATE TABLE `attributes_entity` (
      `product_id` int(11) NOT NULL,
      `attribute_id` int(11) NOT NULL,
      `value_id` int(11) NOT NULL,
       FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
       FOREIGN KEY (`attribute_id`) REFERENCES `attributes` (`id`),
       FOREIGN KEY (`attribute_id`) REFERENCES `attributes` (`id`)
    )
    
    CREATE TABLE `attributes_values` (
      `id` int(11) NOT NULL auto_increment,
      `value` varchar(255) default NULL,
    )
    I want to be able to retrieve a result of all the products that match all the filter values (attributes) passed through.
    Code:
    SELECT
        `p`.`id`
       `p`.`name`, 
    FROM `products` `p
        INNER JOIN `attributes_entity` `ae` ON `p`.`id`=`ae`.`product_id`
        INNER JOIN `attributes` `a` ON `ae`.`attribute_id`=`a`.`id`
    WHERE (`a`.`name`='size' AND `av`.`value`='12mm')
        --this is where it currently creates an "IMPOSSIBLE WHERE"
        AND (`a`.`name`='color' AND `av`.`value`='red')
    It's easy to retrieve a product with a single WHERE cause because the inner joins create a single row relationship for each attributes. However as soon as you add an AND, and try to add a second attribute to filter by no single row meets both criteria but multiple (attribute) rows of the same `product` row will match all the criteria.

    Is this possible with a single query or will I have to look at sub/multiple queries?

  2. #2
    Join Date
    Oct 2005
    Location
    UK
    Posts
    630
    Updated code to add in the missing av.values join:

    Code:
    SELECT
        `p`.`id`,
        `p`.`name`
    FROM `attributes_entity` `ae`
        INNER JOIN `products` `p` ON `ae`.`product_id`=`p`.`id`
        INNER JOIN `attributes` `a` ON `ae`.`attribute_id`=`a`.`id`
        INNER JOIN `attributes_values` `av` ON `ae`.`value_id`=`av`.`id`
    WHERE (`a`.`perma_name`='size' AND `av`.`value`='12mm')
    AND (`a`.`perma_name`='color' AND `av`.`value`='red')
    If I use an OR for the 2nd condition I get half way there to the desired result:
    Code:
    SELECT
        `p`.`name`
    FROM `attributes_entity` `ae`
        INNER JOIN `products` `p` ON `ae`.`product_id`=`p`.`id`
        INNER JOIN `attributes` `a` ON `ae`.`attribute_id`=`a`.`id`
        INNER JOIN `attributes_values` `av` ON `ae`.`value_id`=`av`.`id`
    WHERE `p`.`delete`!='1'
    AND (`a`.`perma_name`='size' AND `av`.`value`='12mm')
    OR (`a`.`perma_name`='color' AND `av`.`value`='red')
    The 2nd query returns the following
    product 1 /* matching size=12mm */
    product 1 /* matching color=red */
    product 2 /* matching size=12mm */

    However I only want it to return results matching both/all of the conditions but at the same not creating an "IMPOSSIBLE WHERE"

  3. #3
    Join Date
    Oct 2005
    Location
    UK
    Posts
    630
    Resolved:
    Code:
    SELECT
        `p`.`name`
    FROM `attributes_entity` `ae`
        INNER JOIN `products` `p` ON `ae`.`product_id`=`p`.`id`
        INNER JOIN `attributes` `a` ON `ae`.`attribute_id`=`a`.`id`
        INNER JOIN `attributes_values` `av` ON `ae`.`value_id`=`av`.`id`
    WHERE `p`.`delete`!='1'
    AND (`a`.`perma_name`='size' AND `av`.`value`='12mm')
    OR (`a`.`perma_name`='color' AND `av`.`value`='red')
    GROUP BY `p`.`id` HAVING COUNT(`a`.`id`)=2

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles