Форум пользователей MySQL

public function attribute_id($id_lang, $attributes, $count = false, $pageNumber = 1, $pageSize = 10, $orderBy = 'name', $orderWay = 'ASC')

    {

        if (!is_numeric($pageNumber) OR !is_numeric($pageSize)

        OR !Validate::isBool($count) OR !Validate::isValidSearch($tag))

            die(Tools::displayError());

       

        if ($pageNumber < 0) $pageNumber = 0;

        if ($pageSize < 1) $pageSize = 10;



        if ($orderBy == 'position') {

            $orderBy = 'name';

        }



        $counter = 0;

        foreach ($attributes AS $attribute) {

            ++$counter;

        }

       

        $query_begin = 'SELECT DISTINCT * FROM (';

   

        $i = $counter;

   

        if ($i==1) {

            $query_end = ') AS tmp GROUP BY tmp.`id_product` HAVING COUNT(*) = ' . $counter . ' ORDER BY tmp.`name`  LIMIT '.((intval($pageNumber) - 1) * intval($pageSize)).','.intval($pageSize);

        } else {

            $query_end = ') AS tmp GROUP BY tmp.`id_product_attribute` HAVING COUNT(*) = ' . $counter . ' ORDER BY tmp.`name`  LIMIT '.((intval($pageNumber) - 1) * intval($pageSize)).','.intval($pageSize);                

        }



        $attr = '';

   



        foreach ($attributes AS $attribute) {

            //if ($count) {

                $attr = $attr . '

                SELECT p.`id_product` , ppac.`id_attribute` , ppac.`id_product_attribute` , ppa.`quantity` , i.`id_image` , pl.`description_short` , pl.`link_rewrite` , pl.`name`

FROM

                `'._DB_PREFIX_.'product` AS p,

                `'._DB_PREFIX_.'product_attribute` AS ppa,

                `'._DB_PREFIX_.'product_attribute_combination` AS ppac,

                `'._DB_PREFIX_.'product_lang` AS pl,

                `'._DB_PREFIX_.'product_attribute_image` AS pai,

                `'._DB_PREFIX_.'image` AS i

                WHERE

               

                ppac.`id_attribute` = '. $attribute .'

AND pai.`id_product_attribute` = ppac.`id_product_attribute`

AND ppa.`id_product_attribute` = ppac.`id_product_attribute`

AND p.`id_product` = ppa.`id_product`

AND ppa.`quantity` > 0

AND i.`id_product` = p.`id_product`

AND i.`id_image` = pai.`id_image`

AND p.`id_product` = pl.`id_product`

AND p.`active` =1

AND pl.`id_lang` =1';

if ($counter == 1) {

                $attr = $attr . ' GROUP BY p.`id_product` ';

            }



            if ($i != 1) {

                $attr = $attr . ' UNION ';

            }



            --$i;

        }





        $query = $query_begin . $attr . $query_end;

       

        $result = Db::getInstance()->ExecuteS($query);



        if ($count) {

            return sizeof($result);

        }



        if (!$result) {

            return false;

        }



        return Product::getProductsProperties($id_lang, $result);

    }
 

SELECT DISTINCT * FROM ( SELECT p.`id_product` , ppac.`id_attribute` , ppac.`id_product_attribute` , ppa.`quantity` , i.`id_image` , pl.`description_short` , pl.`link_rewrite` , pl.`name` FROM `pstore_product` AS p, `pstore_product_attribute` AS ppa, `pstore_product_attribute_combination` AS ppac, `pstore_product_lang` AS pl, `pstore_product_attribute_image` AS pai, `pstore_image` AS i WHERE ppac.`id_attribute` = 22 AND pai.`id_product_attribute` = ppac.`id_product_attribute` AND ppa.`id_product_attribute` = ppac.`id_product_attribute` AND p.`id_product` = ppa.`id_product` AND ppa.`quantity` > 0 AND i.`id_product` = p.`id_product` AND i.`id_image` = pai.`id_image` AND p.`id_product` = pl.`id_product` AND p.`active` =1 AND pl.`id_lang` =1 GROUP BY p.`id_product` ) AS tmp GROUP BY tmp.`id_product` HAVING COUNT(*) = 1 ORDER BY tmp.`name` LIMIT 0,10
 

Table    Create Table
pstore_product    CREATE TABLE `pstore_product` (
 `id_product` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `id_supplier` int(10) unsigned DEFAULT NULL,
 `id_manufacturer` int(10) unsigned DEFAULT NULL,
 `id_tax` int(10) unsigned NOT NULL,
 `id_category_default` int(10) unsigned DEFAULT NULL,
 `id_color_default` int(10) unsigned DEFAULT NULL,
 `on_sale` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `ean13` varchar(13) DEFAULT NULL,
 `ecotax` decimal(17,2) NOT NULL DEFAULT '0.00',
 `quantity` int(10) unsigned NOT NULL DEFAULT '0',
 `price` decimal(20,6) NOT NULL DEFAULT '0.000000',
 `wholesale_price` decimal(20,6) NOT NULL DEFAULT '0.000000',
 `reduction_price` decimal(17,2) DEFAULT NULL,
 `reduction_percent` float DEFAULT NULL,
 `reduction_from` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
 `reduction_to` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
 `reference` varchar(32) DEFAULT NULL,
 `supplier_reference` varchar(32) DEFAULT NULL,
 `location` varchar(64) DEFAULT NULL,
 `weight` float NOT NULL DEFAULT '0',
 `out_of_stock` int(10) unsigned NOT NULL DEFAULT '2',
 `quantity_discount` tinyint(1) DEFAULT '0',
 `customizable` tinyint(2) NOT NULL DEFAULT '0',
 `uploadable_files` tinyint(4) NOT NULL DEFAULT '0',
 `text_fields` tinyint(4) NOT NULL DEFAULT '0',
 `active` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `indexed` tinyint(1) NOT NULL DEFAULT '0',
 `date_add` datetime NOT NULL,
 `date_upd` datetime NOT NULL,
 PRIMARY KEY (`id_product`),
 KEY `product_supplier` (`id_supplier`),
 KEY `product_manufacturer` (`id_manufacturer`),
 KEY `id_tax` (`id_tax`),
 KEY `id_category_default` (`id_category_default`),
 KEY `id_color_default` (`id_color_default`),
 KEY `date_add` (`date_add`)
) ENGINE=MyISAM AUTO_INCREMENT=60 DEFAULT CHARSET=utf8

pstore_product_attribute    CREATE TABLE `pstore_product_attribute` (
 `id_product_attribute` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `id_product` int(10) unsigned NOT NULL,
 `reference` varchar(32) DEFAULT NULL,
 `supplier_reference` varchar(32) DEFAULT NULL,
 `location` varchar(64) DEFAULT NULL,
 `ean13` varchar(13) DEFAULT NULL,
 `wholesale_price` decimal(20,6) NOT NULL DEFAULT '0.000000',
 `price` decimal(17,2) NOT NULL DEFAULT '0.00',
 `ecotax` decimal(17,2) NOT NULL DEFAULT '0.00',
 `quantity` int(10) unsigned NOT NULL DEFAULT '0',
 `weight` float NOT NULL DEFAULT '0',
 `default_on` tinyint(1) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`id_product_attribute`),
 KEY `product_attribute_product` (`id_product`),
 KEY `reference` (`reference`),
 KEY `supplier_reference` (`supplier_reference`),
 KEY `product_default` (`id_product`,`default_on`),
 KEY `id_product_id_product_attribute` (`id_product_attribute`,`id_product`)
) ENGINE=MyISAM AUTO_INCREMENT=44 DEFAULT CHARSET=utf8

pstore_product_attribute_combination    CREATE TABLE `pstore_product_attribute_combination` (
 `id_attribute` int(10) unsigned NOT NULL,
 `id_product_attribute` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id_attribute`,`id_product_attribute`),
 KEY `id_product_attribute` (`id_product_attribute`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

pstore_product_lang    CREATE TABLE `pstore_product_lang` (
 `id_product` int(10) unsigned NOT NULL,
 `id_lang` int(10) unsigned NOT NULL,
 `description` text,
 `description_short` text,
 `link_rewrite` varchar(128) NOT NULL,
 `meta_description` varchar(255) DEFAULT NULL,
 `meta_keywords` varchar(255) DEFAULT NULL,
 `meta_title` varchar(128) DEFAULT NULL,
 `name` varchar(128) NOT NULL,
 `available_now` varchar(255) DEFAULT NULL,
 `available_later` varchar(255) DEFAULT NULL,
 UNIQUE KEY `product_lang_index` (`id_product`,`id_lang`),
 KEY `id_lang` (`id_lang`),
 KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

pstore_product_attribute_image    CREATE TABLE `pstore_product_attribute_image` (
 `id_product_attribute` int(10) unsigned NOT NULL,
 `id_image` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id_product_attribute`,`id_image`),
 KEY `id_image` (`id_image`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

pstore_image    CREATE TABLE `pstore_image` (
 `id_image` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `id_product` int(10) unsigned NOT NULL,
 `position` tinyint(2) unsigned NOT NULL DEFAULT '0',
 `cover` tinyint(1) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`id_image`),
 KEY `image_product` (`id_product`),
 KEY `product_position` (`id_product`,`position`),
 KEY `id_product_cover` (`id_product`,`cover`)
) ENGINE=MyISAM AUTO_INCREMENT=343 DEFAULT CHARSET=utf8