I try to join 3 tables (products, prices, categories) together to get a result of latest & lowest prices from the joined tables.
I was able to get the expected result on MySQL 5.5, but after upgrading to 5.6, ORDER BY
on the subquery seems be ignored. How to change the query to make the ORDER BY
work?
The query is supposed to:
- find products which is under specified category id (eg: category id 488).
- find the latest price of each product.
- group products with same 'matchkey' (matchkey generated by removing stopwords from the product name. generated by other programming)
- find the lowest price of each grouped products
- show each grouped products info with lowest price
The 3 tables relationship are as below:
- products <-> many-to-many <-> categories
- products -> one-to-many -> prices
table information:
mysql> show columns from products;
+----------------+------------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(300) | NO | MUL | NULL | |
| active | tinyint(1) | YES | | 1 | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| updated_at | timestamp | NO | | 0000-00-00 00:00:00 | |
| matchkey | varchar(300) | NO | MUL | NULL | |
| sku | varchar(50) | YES | MUL | NULL | |
+----------------+------------------+------+-----+---------------------+-----------------------------+
mysql> show columns from prices;
+---------------+---------------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------------------+-----------------------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| price_selling | decimal(8,2) | NO | MUL | NULL | |
| price_before | decimal(8,2) | NO | | 0.00 | |
| product_id | int(10) unsigned | NO | MUL | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| updated_at | timestamp | NO | | 0000-00-00 00:00:00 | |
+---------------+---------------------+------+-----+---------------------+-----------------------------+
mysql> show columns from categories;
+------------------+------------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| parent_id | int(11) | YES | MUL | NULL | |
| lft | int(11) | YES | MUL | NULL | |
| rgt | int(11) | YES | MUL | NULL | |
| depth | int(11) | YES | | NULL | |
| name | varchar(255) | NO | MUL | NULL | |
| active | tinyint(1) | YES | | 1 | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| updated_at | timestamp | NO | | 0000-00-00 00:00:00 | |
+------------------+------------------+------+-----+---------------------+-----------------------------+
mysql> show columns from category_product;
+--------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+-------+
| product_id | int(10) unsigned | NO | PRI | NULL | |
| category_id | int(10) unsigned | NO | PRI | NULL | |
+--------------------+------------------+------+-----+---------+-------+
The query that works on MySQL 5.5 is:
SELECT
p3.pid AS id, category_product.product_id, p3.name, p3.priceId,
p3.product_id, p3.price_selling, p3.price_before, MIN(p3.price_selling) AS minprice,
p3.matchkey, categories.url_key AS category_urlkey
FROM category_product
RIGHT JOIN (
SELECT
pp.priceId, pp.product_id, pp.price_selling, pp.price_before,
pp.pid, pp.name, pp.matchkey
FROM (
SELECT
prices.id AS priceId, prices.product_id, prices.price_selling, prices.price_before,
products.id AS pid, products.name, products.matchkey
FROM prices
RIGHT JOIN products ON prices.product_id = products.id
WHERE prices.id = (SELECT MAX(p.id) FROM prices p WHERE p.product_id = prices.product_id)
) AS pp
GROUP BY pp.pid
ORDER BY pp.price_selling ASC
) AS p3 ON category_product.product_id = p3.pid
RIGHT JOIN categories ON categories.id = category_product.category_id AND categories.id = 488
GROUP BY p3.matchkey
ORDER BY p3.pid DESC;
The table structures & sample data can be used for test:
--
-- Table structure for table `products`
--
CREATE TABLE IF NOT EXISTS `products` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
`active` tinyint(1) DEFAULT '1',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`matchkey` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
`sku` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `products_sku_index` (`sku`),
KEY `id` (`id`),
KEY `matchkey` (`matchkey`),
FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=60057 ;
--
-- Dumping data for table `products`
--
INSERT INTO `products` (`id`, `name`, `active`, `created_at`, `updated_at`, `matchkey`, `sku`) VALUES
(26195, 'Samsung Galaxy S3 Mini GT-I8200 Unlocked Cellphone, White, 8GB', 1, '2016-01-23 14:38:25', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(26425, 'Samsung Galaxy S3 Mini GT-i8200 Factory Unlocked Cellphone, International Version, 8GB, White', 1, '2016-01-23 14:38:28', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(26632, 'Samsung Galaxy S3 Mini GT-i8200 Factory Unlocked Cellphone, International Version, 8GB, White', 1, '2016-01-23 14:38:29', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(28212, 'Samsung Galaxy S3 Mini GT-I8200 Unlocked Cellphone, White, 8GB', 1, '2016-01-23 14:38:51', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(35111, 'Samsung Galaxy S3 Mini GT-i8200 Factory Unlocked Cellphone, International Version, 8GB, White', 1, '2016-01-23 14:39:03', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(37611, '[From U.S.A]Samsung Galaxy S3 Mini GT-i8200 Factory Unlocked Cellphone, International Version, 8GB, White', 1, '2016-01-23 14:39:29', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL),
(60017, 'Samsung Galaxy S3 Mini GT-i8200 Factory Unlocked Cellphone, International Version, 8GB, White', 1, '2016-01-23 14:38:11', '0000-00-00 00:00:00', '8gb cellphone galaxy gt i8200 mini s3 samsung white', NULL);
--
-- Table structure for table `prices`
--
CREATE TABLE IF NOT EXISTS `prices` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`price_selling` decimal(8,2) NOT NULL,
`price_before` decimal(8,2) NOT NULL DEFAULT '0.00',
`product_id` int(10) unsigned NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `price_selling` (`price_selling`),
KEY `id` (`id`),
KEY `product_id` (`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=114663 ;
--
-- Dumping data for table `prices`
--
INSERT INTO `prices` (`id`, `price_selling`, `price_before`, `product_id`, `created_at`, `updated_at`) VALUES
(33585, '1052.40', '0.00', 26195, '2016-01-22 09:53:02', '0000-00-00 00:00:00'),
(65594, '1052.40', '0.00', 26195, '2016-01-23 01:27:08', '0000-00-00 00:00:00'),
(110038, '1052.40', '0.00', 26195, '2016-01-23 14:38:25', '0000-00-00 00:00:00'),
(33815, '1052.40', '0.00', 26425, '2016-01-22 09:55:17', '0000-00-00 00:00:00'),
(65790, '1052.40', '0.00', 26425, '2016-01-23 01:27:11', '0000-00-00 00:00:00'),
(110209, '1016.40', '0.00', 26425, '2016-01-23 14:38:28', '0000-00-00 00:00:00'),
(34022, '1052.40', '0.00', 26632, '2016-01-22 09:57:43', '0000-00-00 00:00:00'),
(66020, '1052.40', '0.00', 26632, '2016-01-23 01:27:13', '0000-00-00 00:00:00'),
(110373, '1052.40', '0.00', 26632, '2016-01-23 14:38:29', '0000-00-00 00:00:00'),
(35602, '960.25', '0.00', 28212, '2016-01-22 10:12:50', '0000-00-00 00:00:00'),
(67396, '960.25', '0.00', 28212, '2016-01-23 01:27:45', '0000-00-00 00:00:00'),
(111555, '960.25', '0.00', 28212, '2016-01-23 14:38:51', '0000-00-00 00:00:00'),
(71346, '929.20', '0.00', 35111, '2016-01-23 01:37:05', '0000-00-00 00:00:00'),
(112318, '899.30', '0.00', 35111, '2016-01-23 14:39:03', '0000-00-00 00:00:00'),
(73852, '936.10', '0.00', 37611, '2016-01-23 01:54:47', '0000-00-00 00:00:00'),
(113711, '936.10', '0.00', 37611, '2016-01-23 14:39:29', '0000-00-00 00:00:00'),
(109601, '1108.99', '2369.91', 60017, '2016-01-23 14:38:08', '0000-00-00 00:00:00');
--
-- Table structure for table `categories`
--
CREATE TABLE IF NOT EXISTS `categories` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`lft` int(11) DEFAULT NULL,
`rgt` int(11) DEFAULT NULL,
`depth` int(11) DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`url_key` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
`active` tinyint(1) DEFAULT '1',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `categories_parent_id_index` (`parent_id`),
KEY `categories_lft_index` (`lft`),
KEY `categories_rgt_index` (`rgt`),
KEY `url_key` (`url_key`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=825 ;
--
-- Dumping data for table `categories`
--
INSERT INTO `categories` (`id`, `parent_id`, `lft`, `rgt`, `depth`, `name`, `active`, `url_key`, `created_at`, `updated_at`) VALUES
(1, NULL, 1, 968, 0, 'Root', 1, NULL, '2016-01-23 13:19:51', '2016-01-23 13:19:51'),
(487, 1, 446, 453, 1, 'Mobile & Communication', 1, 'mobile-communication', '2016-01-23 13:19:51', '2016-01-23 13:19:51'),
(488, 487, 447, 448, 2, 'Mobile Phones', 1, 'mobile-phones', '2016-01-25 06:27:37', '2016-01-25 06:27:37');
--
-- Table structure for table `category_product`
--
CREATE TABLE IF NOT EXISTS `category_product` (
`product_id` int(10) unsigned NOT NULL,
`category_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`product_id`,`category_id`),
KEY `product_id` (`product_id`),
KEY `category_id` (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
--
-- Dumping data for table `category_product`
--
INSERT INTO `category_product` (`product_id`, `category_id`) VALUES
(26195, 1),
(26195, 487),
(26195, 488),
(26425, 1),
(26425, 487),
(26425, 488),
(26632, 1),
(26632, 487),
(26632, 488),
(28212, 1),
(28212, 487),
(28212, 488),
(35111, 1),
(35111, 487),
(35111, 488),
(37611, 1),
(37611, 487),
(37611, 488),
(60017, 1),
(60017, 487),
(60017, 488);
Responses to comments
Downgrading to 5.5 is my last option if can't fix it. I would like to rewrite the query, I tried changing the query still no lucky to get it work. I think is something wrong in the query I write.
For the testing data provided above, the query should return products under (category id: 488). The query is used for a price comparison site. When a user clicks on the category ('Mobile Phones' - category id in database- 'categories' table is 488) link on website, the website will list all the products (grouped by 'matchkey' column), showing the lowest price of that product group.
Adding WHERE categories.id = 488
or categories.name = 'Mobile Phones'
, I still get the same result. The problem is the query result not showing the lowest price, even I write ORDER BY pp.price_selling ASC
on the subquery.
Demo:
Both demos above are using the same query, but the results are different.