MySql | Faceted search changed

Question

Maybe you will help me with my sql query. My question is based on another question it's here: Mysql | Faceted search Everything is the same and I need the same result but the tables are little bit different. I can't construct my query. Please take a look at this sql fiddle:

My table structure:

CREATE TABLE products
    (`id` int, `description` varchar(9), `user_id` int);

INSERT INTO products
    (`id`, `description`, `user_id`)
VALUES
    (1, 'my car', 3),
    (2, 'dream car', 3),
    (3, 'New car', 3),
    (4, 'Old car', 4);

CREATE TABLE fields
    (`id` int, `field_name` varchar(14)); /*meta_name*/

INSERT INTO fields
    (`id`, `field_name`)
VALUES
    (1, 'Make'),
    (2, 'Model'),
    (3, 'Color'),
    (4, 'Car Type'),
    (5, 'Interior Color');

CREATE TABLE fields_values
    (`id` int, `field_id` int, `field_value` varchar(7)); /*meta_value*/

INSERT INTO fields_values
    (`id`, `field_id`, `field_value`)
VALUES
    (1, 1, 'BMW'),
    (2, 2, '3Series'),
    (3, 3, 'White'),
    (4, 4, 'Coupe'),
    (5, 5, 'Black'),
    (6, 1, 'BMW'),
    (7, 2, '2Series'),
    (8, 3, 'Black'),
    (9, 4, 'Coupe'),
    (10, 5, 'Grey'),
    (11, 1, 'Honda'),
    (12, 2, 'Civic'),
    (13, 3, 'Red'),
    (14, 4, 'Sedan'),
    (15, 5, 'Black');

CREATE TABLE products2fields_values
    (`id` int, `product_id` int, `field_value_id` int);
INSERT INTO products2fields_values
    (`id`, `product_id`, `field_value_id`)
VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 1, 3),
    (4, 1, 4),
    (5, 1, 5),
    (6, 2, 1),
    (7, 2, 2),
    (8, 2, 3),
    (9, 2, 4),
    (10, 2, 5),
    (11, 3, 1),
    (12, 3, 2),
    (13, 3, 3),
    (14, 3, 4),
    (15, 3, 5); 

And my wrong query:

SELECT field_name, field_value, COUNT(DISTINCT pid) count
FROM fields ft 
JOIN fields_values fvt
    ON fvt.field_id = ft.id

JOIN products2fields_values p2fv
    ON p2fv.field_value_id = fvt.id

LEFT JOIN (
    SELECT p.id pid
    FROM products p   
    JOIN products2fields_values p2fv
        ON p2fv.product_id = p.id
    JOIN fields_values fvt
        ON fvt.id = p2fv.field_value_id
    JOIN fields ft
        ON ft.id = fvt.field_id
    GROUP BY p.id
    HAVING MAX(ft.id = 1 AND p2fv.field_value_id = 1) = 1
       AND MAX(ft.id = 4 AND p2fv.field_value_id = 4) = 1
)

LJ ON p2fv.product_id = LJ.pid
GROUP BY field_name, field_value;

I'm trying to get result:

|      field_name| field_value| count |
|----------------|------------|-------|
|       Car Type |      Coupe |     2 |
|       Car Type |      Sedan |     0 |
|          Color |      Black |     1 |
|          Color |        Red |     0 |
|          Color |      White |     1 |
| Interior Color |      Black |     2 |
| Interior Color |       Grey |     1 |
|           Make |        BMW |     2 |
|           Make |      Honda |     0 |
|          Model |    2Series |     0 |
|          Model |    3Series |     1 |
|          Model |      Civic |     0 |

Answer 1

I managed to get the solution for this in my instance of SQL Server 2014 by Pivoting the tables into a new table and utilizing this with some Unions as shown below:

Declare @Make varchar(7)
Declare @CarType varchar(7)

Create table #CarDetails (CarId int, Make varchar(7), Model varchar(7), Colour varchar(7), CarType varchar(7), InteriorColour varchar(7))
Insert into #CarDetails
Select CarId, [1] as Make, [2] Model, [3] Colour, [4] CarType, [5] InteriorColour
from
(Select ROW_NUMBER() over (partition by field_id order by id asc) CarId, field_id, field_value
from fields_values) a
PIVOT
(Max(field_value) for field_id in ([1],[2],[3],[4],[5])) as pvt
order by pvt.CarId

Set @Make = 'BMW'
Set @CarType = 'Coupe'

Select 'Car Type' field_name, CarType field_value, Count(CarType) [count]
from #CarDetails
Where Make = @Make and CarType = @CarType
Group by CarType
UNION
Select 'Colour', Colour, Count(Colour)
from #CarDetails
Where Make = @Make and CarType = @CarType
Group by Colour
UNION
Select 'Interior Colour', InteriorColour, Count(InteriorColour)
from #CarDetails
Where Make = @Make and CarType = @CarType
Group by InteriorColour
UNION
Select 'Make', Make, Count(Make)
from #CarDetails
Where Make = @Make and CarType = @CarType
Group by Make
UNION
Select 'Model', Model, Count(Model)
from #CarDetails
Where Make = @Make and CarType = @CarType
Group by Model

However this is for MySQL and MySQL does not have a RowNumber function nor a Pivot function so I've tried to make it work but unfortunately have not been able to test it yet as SQLFiddle is having problems. Hopefully this will work:

Set @Make = 'BMW'
Set @CarType = 'Coupe'

Create temporary table CarDetails (CarId int, Make varchar(7), Model varchar(7), Colour varchar(7), CarType varchar(7), InteriorColour varchar(7));
Insert into CarDetails (CarId, Make)
Select CarId, field_value
from
(Select @RowNum := @RowNum + 1 as CarId, @fieldid := field_id as field_id, field_value
from fields_values where field_id = 1) a;
Set @RowNum = 0;

Update CarDetails c
JOIN
(Select @RowNum := @RowNum + 1 as CarId, @fieldid := field_id as field_id, field_value
from fields_values where field_id = 2) a on a.CarId = c.CarId
Set c.Model = a.field_value;
Set @RowNum = 0;

Update CarDetails c
JOIN
(Select @RowNum := @RowNum + 1 as CarId, @fieldid := field_id as field_id, field_value
from fields_values where field_id = 3) a on a.CarId = c.CarId
Set Colour = a.field_value;
Set @RowNum = 0;

Update CarDetails c
JOIN
(Select @RowNum := @RowNum + 1 as CarId, @fieldid := field_id as field_id, field_value
from fields_values where field_id = 4) a on a.CarId = c.CarId
Set CarType = field_value;
Set @RowNum = 0;

Update CarDetails c
JOIN
(Select @RowNum := @RowNum + 1 as CarId, @fieldid := field_id as field_id, field_value
from fields_values where field_id = 5) a on a.CarId = c.CarId
Set InteriorColour = field_value;

Select 'Car Type' field_name, CarType field_value, Count(CarType) count
from CarDetails
Where Make = @Make and CarType = @CarType
Group by CarType
UNION
Select 'Colour', Colour, Count(Colour)
from CarDetails
Where Make = @Make and CarType = @CarType
Group by Colour
UNION
Select 'Interior Colour', InteriorColour, Count(InteriorColour)
from CarDetails
Where Make = @Make and CarType = @CarType
Group by InteriorColour
UNION
Select 'Make', Make, Count(Make)
from CarDetails
Where Make = @Make and CarType = @CarType
Group by Make
UNION
Select 'Model', Model, Count(Model)
from CarDetails
Where Make = @Make and CarType = @CarType
Group by Model

** Update ** SQLFiddle is back up and here it is working: http://www.sqlfiddle.com/#!9/aa769/4/2

Answer 2

You need to correct data for field_value_id column in products2fields_values table.

With current data, Table relationships have got broken and we can't get expected results with those values sitting there.

Insert should be as below :

INSERT INTO products2fields_values
    (id, product_id, field_value_id)
VALUES
    (1,1, 1),
    (2,1, 2),
    (3,1, 3),
    (4,1, 4),
    (5,1, 5),
    (6,2, 6),
    (7,2, 7),
    (8,2, 8),
    (9,2, 9),
    (10,2, 10),
    (11,3, 11),
    (12,3, 12),
    (13,3, 13),
    (14,3, 14),
    (15,3, 15); 

Running below query as suggested by @steven should give expected results:

SELECT 
    field_name, field_value, COUNT(val.id) as count
FROM 
    fields ft 
INNER JOIN 
    fields_values fvt ON fvt.field_id = ft.id
LEFT JOIN 
    products2fields_values val ON val.field_value_id = fvt.id
GROUP BY field_name, field_value;

You can see results here

Answer 3

As far as i understand your question this should be the query you want:

SELECT field_name, field_value, COUNT(val.id) as count
FROM fields ft 
JOIN fields_values fvt
ON fvt.field_id = ft.id
LEFT JOIN products2fields_values val
ON val.field_value_id = fvt.id 
GROUP BY  field_name, field_value;

I don't know why you are joining your product table because it doesn't seems to be necessary for your results. And i do not understand why you implemented your strange HAVING clause.

Please take a look at the results of my query.