Geek Answers Handbook

Postgres array request

(The following is a very simplified description of my problem. The company policy does not allow me to describe the real scenario in detail.)

Used DB tables:

PRODUCTS:
ID   Name
---------
1    Ferrari
2    Lamborghini
3    Volvo


CATEGORIES:
ID    Name
----------
10    Sports cars
20    Safe cars
30    Red cars

PRODUCTS_CATEGORIES
ProductID    CategoryID
-----------------------
1            10
1            30
2            10
3            20

LOCATIONS:
ID      Name
------------
100     Sports car store
200     Safe car store
300     Red car store
400     All cars r us


LOCATIONS_CATEGORIES:
LocationID    CategoryID
------------------------
100           10
200           20
300           30
400           10
400           20
400           30

Please note that locations are not directly related to products, only categories. The customer should be able to see a list of locations that can provide all categories of products to which the products they want to buy belong. So for example:

A customer wants to buy a Ferrari. It will be available from stores in categories 10 or 30. This gives us stores of 100, 300 and 400, but not 200.

However, if a customer wants to buy Volvo and Lamborghini, it will be available from stores in categories 10 and 20. What only gives us store 400.

Ferrari Volvo. 10 + 20 ( ) 30 + 20 ( ).

postgres, , . < @, . SQL, , Ferrari Lamborghini. , , , . 400, 400 100.

SELECT l.* FROM locations l
WHERE 
(SELECT array_agg(DISTINCT(categoryid)) FROM products_categories WHERE productid IN (1,2))
<@
(SELECT array_agg(categoryid) FROM locations_categories WHERE locationid = l.id);

, .

+4
4

. Ids pc.ProductId in (1,3), , , 1 3, HAVING COUNT(DISTINCT pc.ProductId) = 2, 3 , 3. HAVING , :

SELECT Id FROM Locations l
JOIN Locations_Categories lc on l.Id=lc.LocationId
JOIN Products_Categories pc on lc.CategoryId=pc.CategoryID
where pc.ProductId in (1,3)
GROUP BY l.id
HAVING COUNT(DISTINCT pc.ProductId) = 2

- Sqlfiddle

, :

SELECT Id FROM Locations l
JOIN Locations_Categories lc on l.Id=lc.LocationId
JOIN Products_Categories pc on lc.CategoryId=pc.CategoryID
where pc.ProductId in (1)
GROUP BY l.id
HAVING COUNT(DISTINCT pc.ProductId) = 1

Ferrary Volvo Lamborghini

+3

( @valex, , , , @valex ).

, .

, . ( ) . , .

, , ( ). HAVING, , , , .

:

WITH wantedproducts(productname) AS (VALUES('Volvo'), ('Lamborghini'))
SELECT l."ID"
FROM locations l
INNER JOIN locations_categories lc ON (l."ID" = lc."LocationID")
INNER JOIN categories c ON (c."ID" = lc."CategoryID")
INNER JOIN products_categories pc ON (pc."CategoryID" = c."ID")
INNER JOIN products p ON (p."ID" = pc."ProductID")
INNER JOIN wantedproducts wp ON (wp.productname = p."Name")
GROUP BY l."ID"
HAVING count(DISTINCT p."ID") = (SELECT count(*) FROM wantedproducts);

- , , .

" " HAVING.

ORDER BY , , .

string_agg(p."Name") SELECT, , .

, , , VALUES (...) SELECT unnest($1) $1 $1.

+3

: ( , )

:

Ferrari. 10 30. 100, 300 400, 200.

SELECT DISTINCT l.id, l.name
FROM Products p
LEFT JOIN Product_Categories p_c
ON p.id = p_c.ProductId
LEFT JOIN Categories c
ON p_c.CategoryId = c.id
LEFT JOIN Locations_Categories l_c
ON c.id = l_c.CategoryId
LEFT JOIN Locations l
ON l_c.LocationId = l.id
WHERE p.id = 1

:

, Volvo Lamborghini, 10 20. 400.

SELECT DISTINCT l.id, l.name
FROM Products p
LEFT JOIN Product_Categories p_c
ON p.id = p_c.ProductId
LEFT JOIN Categories c
ON p_c.CategoryId = c.id
LEFT JOIN Locations_Categories l_c
ON c.id = l_c.CategoryId
LEFT JOIN Locations l
ON l_c.LocationId = l.id
WHERE l.id in (select id
               from locations loc
               join locations_categories locat1              
               on loc.id = locat1.LocationId
               join locations_categories locat2
               on loc.id = locat2.LocationId
               where locat1.CategoryId = 10
               AND locat2.categoryId = 20)

INTERSECT: intersect , :

SELECT DISTINCT l.id, l.name
FROM Products p
LEFT JOIN Product_Categories p_c
ON p.id = p_c.ProductId
LEFT JOIN Categories c
ON p_c.CategoryId = c.id
LEFT JOIN Locations_Categories l_c
ON c.id = l_c.CategoryId
LEFT JOIN Locations l
ON l_c.LocationId = l.id
WHERE p.id = 2
INTERSECT
SELECT DISTINCT l.id, l.name
FROM Products p
LEFT JOIN Product_Categories p_c
ON p.id = p_c.ProductId
LEFT JOIN Categories c
ON p_c.CategoryId = c.id
LEFT JOIN Locations_Categories l_c
ON c.id = l_c.CategoryId
LEFT JOIN Locations l
ON l_c.LocationId = l.id
WHERE p.id = 3

INTERSECT SQLFIDDLE: http://sqlfiddle.com/#!15/ce97d/15

+1

, , , .

.

WITH needed_categories AS (
  SELECT p."ID", array_agg(pc."CategoryID") AS at_least_one_should_match
  FROM Products p
  JOIN Products_Categories pc ON p."ID" = pc."ProductID"
  WHERE p."ID" IN (1, 3)
  GROUP BY p."ID"
),
not_valid_locations AS (
  SELECT DISTINCT lc."LocationID", unnest(nc.at_least_one_should_match)
  FROM Locations_Categories lc
  JOIN needed_categories nc ON NOT ARRAY[lc."CategoryID"] && nc.at_least_one_should_match 
  EXCEPT
  SELECT * FROM Locations_Categories
) 
SELECT * 
FROM Locations
WHERE "ID" NOT IN (
  SELECT "LocationID" FROM not_valid_locations
);

SQLFiddle: http://sqlfiddle.com/#!15/e138d/78

This works, but I'm still trying to avoid double scanning Location_Categories. The fact that cars can belong to several categories is a bit more complicated, I decided to use arrays, but I'm also trying to get rid of them.

+1
source

More articles:


All Articles