Multiple AND conditions on the same column [Servicestack.OrmLite]-CodePudding

I was wondering if it's possible to have multiple AND conditions on the same column using Servicestack.OrmLite. This is the SELECT statement I printed out, but It always returns 0. I should get the product count from the products having both specifications with id 1016 and 17.

SELECT COUNT(DISTINCT "Product"."Id") 
FROM "Product" 
INNER JOIN "ProductManufacturer" 
ON ("Product"."Id" = "ProductManufacturer"."ProductId") 
INNER JOIN "ProductSpecificationAttribute" 
ON ("Product"."Id" = "ProductSpecificationAttribute"."ProductId")
WHERE ("ProductManufacturer"."ManufacturerId" = 6) 
AND ("ProductSpecificationAttribute"."SpecificationAttributeOptionId" = 1016) 
AND ("ProductSpecificationAttribute"."SpecificationAttributeOptionId" = 17)

CodePudding user response：

A single column value can't possibly have two values at the same time.

What you want is either:

AND
(
  ProductSpecificationAttribute.SpecificationAttributeOptionId = 1016
  OR  
  ProductSpecificationAttribute.SpecificationAttributeOptionId = 17
)

Or, more succinctly:

AND
(
  ProductSpecificationAttribute.SpecificationAttributeOptionId 
  IN (1016, 17)
)

And turn off whatever option is forcing your tool to "inject" "double" "quotes" "around" "every" "entity" "name" because it makes the query text unmanageable. You might also consider using aliases and schema prefixes, like INNER JOIN dbo.ProductSpecificationAttribute AS psa...

After further clarification... the goal is to find products where they have both of those attributes on different rows, which isn't clear from the description or the code ORMLite barfed out. Here's what you want in that case (there are several ways to do this, but converting everything to EXISTS also allows you to remove the DISTINCT from the COUNT, which is never free):

SELECT COUNT(Product.Id) FROM dbo.Product AS p
WHERE EXISTS
(
  SELECT 1 FROM dbo.ProductManufacturer AS pm 
    WHERE pm.ProductId = p.Id AND pm.ManufacturerId = 6
)
AND EXISTS
(
  SELECT 1 FROM dbo.ProductSpecificationAttribute AS psa
    WHERE psa.ProductId = p.Id
    AND psa.SpecificationAttributeOptionId = 1016
)
AND EXISTS
(
  SELECT 1 FROM dbo.ProductSpecificationAttribute AS psa
    WHERE psa.ProductId = p.Id
    AND psa.SpecificationAttributeOptionId = 17
);

If ProductSpecificationAttribute is poorly index and this leads to two scans, you could change that by saying something like this (untested, but I'm happy to test it out if you can produce a db<>fiddle:

SELECT COUNT(Product.Id) FROM dbo.Product AS p
WHERE EXISTS
(
  SELECT 1 FROM dbo.ProductManufacturer AS pm 
    WHERE pm.ProductId = p.Id 
    AND pm.ManufacturerId = 6
)
AND EXISTS
(
  SELECT 1 FROM dbo.ProductSpecificationAttribute AS psa
    WHERE psa.ProductId = p.Id
    AND psa.SpecificationAttributeOptionId IN (17, 1016)
    GROUP BY psa.ProductId, psa.SpecificationAttributeOptionId
    HAVING COUNT(DISTINCT psa.SpecificationAttributeOptionId) > 1
);

It's also really weird that the table ProductManufacturer has a list of ProductIDs in it that point back to Product - usually Product would have a ManufacturerID that points in the other direction.

Anyway, you might consider using stored procedures that your ORM can call if it has problems creating queries beyond basic CRUD (which is unfortunately a limitation of all ORMs to some degree - they're great at the basics, covering 80% of the use case, but they're terrible at the other 20% - unfortunately most of us end up needing that other 20% before too long).

CodePudding user response：

You can get all the product ids that you want if you group by product and set the conditions in the HAVING clause:

SELECT p.Id
FROM Product p
INNER JOIN ProductManufacturer pm ON p.Id = pm.ProductId 
INNER JOIN ProductSpecificationAttribute psa ON p.Id = psa.ProductId
WHERE pm.ManufacturerId = 6 AND psa.SpecificationAttributeOptionId IN (17, 1016)
GROUP BY p.Id
HAVING COUNT(DISTINCT psa.SpecificationAttributeOptionId) = 2; -- both specifications must exist

If you want to count these products you could either use the above query as a subquery or a cte and count the rows:

WITH cte AS (
  SELECT p.Id
  FROM Product p
  INNER JOIN ProductManufacturer pm ON p.Id = pm.ProductId 
  INNER JOIN ProductSpecificationAttribute psa ON p.Id = psa.ProductId
  WHERE pm.ManufacturerId = 6 AND psa.SpecificationAttributeOptionId IN (17, 1016)
  GROUP BY p.Id
  HAVING COUNT(DISTINCT psa.SpecificationAttributeOptionId) = 2; 
)
SELECT COUNT(*) FROM cte;

or, use COUNT() window function:

SELECT DISTINCT COUNT(*) OVER ()
FROM Product p
INNER JOIN ProductManufacturer pm ON p.Id = pm.ProductId 
INNER JOIN ProductSpecificationAttribute psa ON p.Id = psa.ProductId
WHERE pm.ManufacturerId = 6 AND psa.SpecificationAttributeOptionId IN (17, 1016)
GROUP BY p.Id
HAVING COUNT(DISTINCT psa.SpecificationAttributeOptionId) = 2;