product tags filter sql query sample

gistfile1.txt

SELECT 
p.*, 
(SELECT GROUP_CONCAT(rel.grup_tag_admin_name) FROM product_grup_tag_relation AS rel WHERE p.admin_name = rel.product_admin_name) AS gan, 
(SELECT ei.thumb_path FROM product_images AS ei WHERE ei.product_admin_name = p.admin_name ORDER BY ei.cover DESC LIMIT 1) AS thumb_path 
FROM product AS p 
WHERE p.lang='tr' 
GROUP BY p.id 
DESC 
HAVING gan LIKE '%5ad217158154e%' 
AND gan LIKE '%5ad2191521ae6%'

Tables Structure
---------------------------
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE `grup_tag_relation` (
  `id` int(11) NOT NULL,
  `grup_admin_name` varchar(250) COLLATE utf8_bin NOT NULL,
  `grup_tag_admin_name` varchar(222) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `product` (
  `id` int(11) NOT NULL,
  `admin_name` varchar(222) COLLATE utf8_bin NOT NULL,
  `lang` varchar(2) COLLATE utf8_bin NOT NULL,
  `title` varchar(255) COLLATE utf8_bin NOT NULL,
  `slug` varchar(255) COLLATE utf8_bin NOT NULL,
  `description` text COLLATE utf8_bin,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `price` int(11) NOT NULL,
  `price_type` varchar(10) COLLATE utf8_bin NOT NULL COMMENT 'try, eur, usd',
  `status` varchar(255) COLLATE utf8_bin DEFAULT '1' COMMENT 'yayin/degil',
  `meta_keyword` text COLLATE utf8_bin,
  `meta_description` text COLLATE utf8_bin,
  `meta_title` text COLLATE utf8_bin,
  `store_link` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `notes` text COLLATE utf8_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `product_grup_tag_relation` (
  `id` int(11) NOT NULL,
  `product_admin_name` varchar(250) COLLATE utf8_bin NOT NULL,
  `grup_tag_admin_name` varchar(222) COLLATE utf8_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

ALTER TABLE `grup_tag_relation`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `product`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `admin_name` (`admin_name`,`lang`);

ALTER TABLE `product_grup_tag_relation`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `grup_tag_relation`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=51;

ALTER TABLE `product`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;

ALTER TABLE `product_grup_tag_relation`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;