Can I ORM this query?

[LanceH]

Silverstripe Version: 4.1

Goal is to get the records and iterate in the view to display them.

Raw SQL:

SELECT Products.*
FROM Products
INNER JOIN Products_Filters ON Products_Filters.ProductsID = Products.ID
WHERE Products.ProductListPagesID = 6 AND (
 (Products_Filters.FilterID IN (1,13)) OR 
 (Products_Filters.FilterID IN(5, 7))
)
GROUP BY Products.ID
HAVING COUNT(Products.ID) = 2
ORDER BY Products.Title ASC

I’m struggling to get the WHERE how I need it with a mix of AND/OR. Need to AND the first part, and OR the rest which is a variable amount.

I’ve tried:

# $where[] = '(Products_Filters.FilterID IN ('.implode(',', $ids).'))';

$sql = new SQLSelect();
$sql->setSelect('Products.*');
$sql->setFrom('Products');
$sql->addInnerJoin('Products_Filters','"Products_Filters"."ProductsID" = "Products"."ID"');
$sql->addWhere(['Products.ProductListPagesID' => $this->ID]);
$sql->useDisjunction();
$sql->addWhere($where);
$sql->setGroupBy('Products.ID');
$sql->setHaving('COUNT(Products.ID) = '.count($where));
$sql->setOrderBy('Products.Title');

Added $sql->useDisjunction() in there for the OR but it does it on everything.

Resulting query:

SELECT Products.*
FROM Products 
INNER JOIN "Products_Filters" ON "Products_Filters"."ProductsID" = "Products"."ID"
WHERE (Products.ProductListPagesID = ?) OR (
  (Products_Filters.FilterID IN (1,3))) OR 
  ((Products_Filters.FilterID IN (4))
)
GROUP BY Products.ID
HAVING (COUNT(Products.ID) = 2)
ORDER BY Products.Title ASC

So that query doesn’t get the WHERE part as I need.

It also generates an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '“Products_Filters” ON “Products_Filters”.“ProductsID” = “Products”.“ID”

WHERE (’ at line 3

I changed the ? for 6 when running it…

If I remove the quotes around the JOIN fields from the generated SQL it doesn’t error. I quoted them like that from the docs (https://docs.silverstripe.org/en/3/developer_guides/model/sql_query/#usage)

If I remove them from the $sql->addInnerJoin(‘Product_Filters’, ‘Product_Filters.ProductsID = Products.ID’) the resulting query errors:

SELECT Products.*
 FROM Products 
 INNER JOIN "Products_Filters" ON Products_Filters.ProductsID = Products.ID
 WHERE (Products.ProductListPagesID = ?)
 OR ((Products_Filters.FilterID IN (1,3)))
 OR ((Products_Filters.FilterID IN (4,5)))
 GROUP BY Products.ID
 HAVING (COUNT(Products.ID) = 2)
 ORDER BY Products.Title ASC

As the JOIN table is still quoted. Error is the same:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '“Products_Filters” ON Products_Filters.ProductsID = Products.ID

WHERE (Products’ at line 3

How can I get this query running correctly without errors?

[LanceH]

In case it helps anyone down the road, I go it with:

# Example of the $where value
# $where[] = '(Products_Filters.FilterID IN ('.implode(',', $ids).'))';

$sql->setSelect('Products.*')
    ->setFrom('Products')
    ->addInnerJoin('Products_Filters','"Products_Filters"."ProductsID" = "Products"."ID"')
    ->addWhere(['Products.ProductListPagesID' => $this->ID])
    ->addWhereAny($where)
    ->setGroupBy('Products.ID')
    ->setHaving('COUNT(Products.ID) = '.count($where))
    ->setOrderBy('Products.Title');

# Query generated:

SELECT Products.* 
FROM Products 
INNER JOIN "Products_Filters" ON "Products_Filters"."ProductsID" = "Products"."ID" 
WHERE (Products.ProductListPagesID = ?) AND (
    ((Products_Filters.FilterID IN (1,14))) OR 
    ((Products_Filters.FilterID IN (5,20)))
) 
GROUP BY Products.ID 
HAVING (COUNT(Products.ID) = 2) 
ORDER BY Products.Title ASC

To retain the template functionality in a view:

if ($result = $sql->execute())
{
	$list = ArrayList::create();

	while ($record = $result->next())
	{
		$list->push(Product::create($record));
	}

	$Products = $list;
}

That seems to work.

Worth noting is the docs (https://www.silverstripe.org/learn/lessons/v4/beyond-the-orm-building-custom-sql-1) use $record->nextRecord(). Using that bricked the server with an out of memory error. Changing to $record->next() works.

[LanceH]

Ignore the quote related errors. Is due to SS running sql_mode as ANSI.

From: https://www.silverstripe.org/community/forums/data-model-questions/show/23505

[Firesphere]

This would probably be two-parted, first off, use the ORM to get your relational data:

$products = Products::get()->filterAny(['Products.Filters.ID' => [1,2], 'Products.ProductListPages.ID' => 1])->sort('Title ASC')

And then pass that in to a GroupedList, grouped by the products ID. And in your template, do a <% if $products.Count %>

I think that’s what you are trying to achieve?

[LanceH]

Hey, thanks for replying.

I’ll have a go at that. Is that equivalent to the raw SQL though?

The raw SQL in words is saying, get products for page 6 that have a tag in this list of tags OR a tag in the other list of tags. The HAVING is important because it’s making sure (counting) at least one match was made across each OR. It kinda forms an AND between them but not a real one.

The scenario is a faceted search:

Brand
Lexus
Toyota
Audi

Type
sedan
wagon

The query will get all products that are a Lexus or a Toyota but are both Sedans. So it OR’s in the groups but AND’s across them by counting the rows returned by the OR’s.

If there are no Lexus or Toyota sedans it won’t show anything.

[Firesphere]

Wait, you’re doing a faceted search? What are you using to get the facets? Because… ehm… this is exactly what Solr would be good at, instead of having it handled by the ORM!

[LanceH]

I’m not sure what you mean by “get the facets”? They’re created and applied through the CMS. When actioned, I AJAX them to a controller and parse them into the IN() part of the SQL query above.

Yeah I know. I really can’t be buggered with the drama of adding Solr or ElasticSearch to the stack, managing the “bridge” between the DB and them and all it entails. Has been a while since I used either, maybe they’re better now.

There’s likely to be less than 50 products per page over 4 pages with probably a max of 2 FilterGroups and 5 Filters per group. In other words, not a big catalogue/complex searching by any stretch.

Seems a far quicker and less entangled approach to just run some simple SQL I already have.

Is that query really not possible in SS ORM? I know very little of SS but… I’m pretty sure even CodeIgniter could get that done lol

[LanceH]

So this:

Product::get()
->filter(['ProductListPages.ID' => 6])
->filterAny(['Filters.ID' => [1,13]])
->filterAny(['Filters.ID' => [5,7]])
->sort('Title ASC')
->sql();

Generates this…

 SELECT DISTINCT <lots of fields>,
	CASE WHEN "Products"."ClassName" IS NOT NULL THEN "Products"."ClassName"
	ELSE 'Product' END AS "RecordClassName"
 FROM "Products" 
 LEFT JOIN "SiteTree" AS "productlistpages_SiteTree" ON "productlistpages_SiteTree"."ID" = "Products"."ProductListPagesID" 
 LEFT JOIN "ProductListPage" AS "productlistpages_ProductListPage" ON "productlistpages_SiteTree"."ID" = "productlistpages_ProductListPage"."ID"
 LEFT JOIN "Products_Filters" AS "filters_Products_Filters" ON "filters_Products_Filters"."ProductsID" = "Products"."ID" 
LEFT JOIN "Filter" AS "filters_Filter" ON "filters_Products_Filters"."FilterID" = "filters_Filter"."ID"
WHERE ("productlistpages_SiteTree"."ID" = 6)
AND (("filters_Filter"."ID" IN (1,13)))
AND (("filters_Filter"."ID" IN (5,7)))
ORDER BY "Products"."Title" ASC

Looking past all the (wrong, should be INNER) JOIN kludge, it doesn’t use the right OR/AND. The second AND there I’d need to be an OR.

Just go wild and run the raw SQL then or?

[JonoM]

The second AND there I’d need to be an OR.

I want you to be able to use the ORM here so I’m replying even though late to the party

Assuming you’re using one filterAny for Brand IDs and another for Type IDs, it sounds like this is working the way you want it to, and an AND is correct here:

The query will get all products that are a Lexus or a Toyota but are both Sedans. So it OR’s in the groups but AND’s across them by counting the rows returned by the OR’s. If there are no Lexus or Toyota sedans it won’t show anything.

If you used an OR, you would match all sedans, including Audis.

Since you’re only passing one array element in to filterAny it’s going to work the same as filter. That means your three filter calls are probably equivalent to just:

->filter([
    'ProductListPages.ID' => 6,
    'Filters.ID' => [1,13],
    'Filters.ID' => [5,7]
])

BTW if this code is on a ProductListPage page or ProductListPageController then Product::get()->filter(['ProductListPages.ID' => 6]) can be substituted for $this->Products() (or whatever you named that many_many relation)

[LanceH]

@JonoM I can’t seem to reply to you except for down here.

Mmm, I think it is an OR.

The HAVING clause is the important bit. It makes sure the product has at least one match across all the Filters. I made a slight change to it so it’s HAVING is >= instead of just =.

For an example head here: Wayne | PES

Choose Lexus, Toyota and Silver. It finds Products that have Lexus OR Toyota but both must have Silver. I’m outputting the query on that screen so you can see it. If I use an AND it finds nothing.

It’s not my own work, based on an SO post I’ve since lost but it’s copied here: http://thedigilife.com/faceted-search-layered-search-on-mysql-database-with-example/

I’m happy to use ORM, current query is now:

# This is built up inside a couple of loops. 
$where[] = 'Products_Filters.FilterID IN ('.implode(',', $ids).')';

$sql = new SQLSelect();
$sql->setSelect('Products.*')
    ->setFrom('Products')
	->addInnerJoin('Products_Filters', '"Products_Filters"."ProductsID" = "Products"."ID"')
	->addWhere(['Products.ProductListPagesID' => $this->ID])
	->addWhereAny($where)
	->setGroupBy('Products.ID')
	->setHaving('COUNT(Products.ID) >= '.count($where))
	->setOrderBy('Products.Title');


if ($result = $sql->execute())
{
    $list = ArrayList::create();

    while ($record = $result->next())
    {
        $list->push(Product::create($record));
    }

    $Products = $list;
}

return $this->customise(['Products' => $Products])->renderWith('Includes/ProductList');

Off topic: man posting code from PHPStorm in here is a total PITA!

[JonoM]

@LanceH

I can’t seem to reply to you except for down here.

Yeah replying isn’t intuitive. There is a reply button on each top level reply only, so if you want to continue a sub-thread you have to go to the post where the sub-thread started to find the reply button.

The having/grouping stuff is doing my head in but I’m not very good with raw SQL I don’t think you should need to do any grouping though. Would you humour me and try substituting in this bit of code where your product list is generated?

$Products = $this->Products()->filter([
    'Filters.ID' => [1,13], // Substitute for array of selected Brand IDs
    'Filters.ID' => [5,7] // Substitute for array of selected Body Type IDs
]);

[LanceH]

Sure man.

I changed it to:

$Products = Product::get()->filter([
    'ProductListPagesID' => $this->ID,
    'Filters.ID' => [1,3], // Substitute for array of selected Brand IDs
    'Filters.ID' => [5,20] // Substitute for array of selected Body Type IDs
]);

1 = Lexus
3 = Toyota
5 = Black
20 = Silver

So looking for a Lexus or a Toyota that is also either Black or Silver.

I expect one Product to come back for that. With mine, I get one.

With the query above though I get back four. Appears to bring back anything with that filter on it. Like a Black Audi for example.

The query the ORM builds makes me think it doesn’t like the duplicate array key of Filters.ID either:

SELECT DISTINCT "Products"."ClassName", "Products"."LastEdited", "Products"."Created", "Products"."Title", "Products"."Permalink", "Products"."SubTitle", "Products"."ProductCode", "Products"."Description", "Products"."Views", "Products"."DateAdded", "Products"."MainImageID", "Products"."ProductListPagesID", "Products"."ID", CASE WHEN "Products"."ClassName" IS NOT NULL THEN "Products"."ClassName" ELSE 'Product' END AS "RecordClassName" 
FROM "Products" 
LEFT JOIN "Products_Filters" AS "filters_Products_Filters" ON "filters_Products_Filters"."ProductsID" = "Products"."ID" 
LEFT JOIN "Filter" AS "filters_Filter" ON "filters_Products_Filters"."FilterID" = "filters_Filter"."ID" 
WHERE ("Products"."ProductListPagesID" = ?) AND ("filters_Filter"."ID" IN (?, ?))

I mention the array keys as there only two ? for the IN() not four.

If it helps you with the grouping/having I can show you results before that happens.

The jist of it is MYSQL will return a row per hit on the IN statement(s). The Having() counts those hits. If there is less than the amount of IN() statement(s) a product only matched some of them, not all. We want to “AND” across those OR groups so the rows returns have to be at least the number of IN()'s. They can be more. Like with the example above, a product that had Lexus, Toyota, Black, Silver on it would get four rows.

The grouping then takes those multiple rows returned from the IN()'s and just sends us one back for each product.

[LanceH]

@JonoM Gotta shoot off, will try your updated query later tonight.

Nope not a typo. It’s a foreign key to the current page.

[JonoM]

@LanceH

The query the ORM builds makes me think it doesn’t like the duplicate array key of Filters.ID either:

Oh yeah of course, that was dumb of me. I wonder if this would work better? I’m just surprised if this can’t be done with the ORM! But if you have something working it’s all good

$Products = Product::get()->filter([
    'ProductListPages.ID' => $this->ID,
    'Filters.ID' => [1,3], // Substitute for array of selected Brand IDs
])->filter([
    'Filters.ID' => [5,20] // Substitute for array of selected Body Type IDs
]);

Note that it looked to me like there was a typo in your last code paste - ProductListPagesID should be ProductListPages.ID right?

[LanceH]

@JonoM

$Products = Product::get()->filter([
‘ProductListPages.ID’ => $this->ID,
‘Filters.ID’ => [1,3], // Substitute for array of selected Brand IDs
])->filter([
‘Filters.ID’ => [5,20] // Substitute for array of selected Body Type IDs
]);

I tried this one. Returns 0 results.

SELECT 
    DISTINCT "Products"."ClassName"
    , "Products"."LastEdited"
    , "Products"."Created"
    , "Products"."Title"
    , "Products"."Permalink"
    , "Products"."SubTitle"
    , "Products"."ProductCode"
    , "Products"."Description"
    , "Products"."Views"
    , "Products"."DateAdded"
    , "Products"."MainImageID"
    , "Products"."ProductListPagesID"
    , "Products"."ID"
    , CASE WHEN "Products"."ClassName" IS NOT NULL 
      THEN "Products"."ClassName"
      ELSE 'Product' 
      END AS "RecordClassName"			
FROM 
    "Products" 
LEFT JOIN 
    "SiteTree" AS "productlistpages_SiteTree" ON "productlistpages_SiteTree"."ID" = "Products"."ProductListPagesID" 
LEFT JOIN 
    "ProductListPage" AS "productlistpages_ProductListPage" ON "productlistpages_SiteTree"."ID" = "productlistpages_ProductListPage"."ID" 
LEFT JOIN  
    "Products_Filters" AS "filters_Products_Filters" ON "filters_Products_Filters"."ProductsID" = "Products"."ID" 
LEFT JOIN 
    "Filter" AS "filters_Filter" ON "filters_Products_Filters"."FilterID" = "filters_Filter"."ID"
WHERE 
    ("productlistpages_SiteTree"."ID" = ?)
    AND 
    ("filters_Filter"."ID" IN (?, ?))
    AND 
    ("filters_Filter"."ID" IN (?, ?))

It finds nothing because a product would need to have all those options Lexus, Toyota, Black and Silver applied to it. That works fine for exclusive facets but not for Inclusive.

The product it should find has the filters Lexus, Toyota, Silver.

Well… actually I think the real problem with it is the logic of AND’ing the same field from the same JOIN. The JOIN field can’t ever be in both sets can it.

It doesn’t always make sense to have exclusive and it doesn’t always make sense to have inclusive.

It’s most likely FilterGroups will end up having an Enum column on it called Type that users can set which way to swing it.