Criteria limit not accurate

[forby]

Pretty simple setup, just trying something like this:

{% set similar = craft.similar.find({
        element: entry,
        context: entry.workTags,
        criteria: craft.entries.limit(2)
}) %}

(In this case, each entry has a tag field called 'workTag')

I have many tags used throughout my entries, but I'm only retrieving one result. I've tripple-checked and I know that my handful of entries should at least have 2 connections each. Most of them share at least two tags. Am I doing something wrong?

After looking around for answers I finally tried increasing the limit to 4, now I retrieve 2 results on almost all entries (with the occasional 3 or 4). Maybe I'm just missing something.

[drifteaur]

I'm having the same issue, with a limit of 3 I'm usually getting 2 entries, setting the limit to 9 gives me about 5.

[forby]

🤔

[khalwat]

hmmm. I'll have a look.

[khalwat]

@forby @drifteaur what happens if you pass it in like this:

{% set similar = craft.similar.find({
        element: entry,
        context: entry.workTags,
        criteria: {
            'limit': 2,
        }
}) %}

[drifteaur]

Still buggy for me. For some entries (about 1 in 10 or so, I'm setting the limit to 3 and getting just 2. If I set the limit higher, I can get more than 3. I'm mixing in more than one category field, but @forby had the same issue with just one. I'll try to take a look at the queries performed.

    {% set ids = entry.category.ids() | merge(entry.audience.ids()) | merge(entry.category_en.ids()) %}
    {% set similarEntries = craft.similar.find({
        element: entry,
        context: ids,
        criteria: {
            'limit': 3,
        } 
    }) %}

[drifteaur]

So, the limit is in a subquery, and then it gets inner joined to several tables which might filter the results.

SELECT `elements`.`id`, `elements_sites`.`siteId`, COUNT(*) as count
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `elements` `elements`
INNER JOIN `entries` `entries` ON `entries`.`id` = `elements`.`id`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON `content`.`elementId` = `elements`.`id`
LEFT JOIN `structureelements` `structureelements` ON `structureelements`.`elementId` = `elements`.`id`
LEFT JOIN `relations` ON elements.id=`relations`.sourceId
WHERE (`elements_sites`.`siteId`='1') AND ((elements.id != '193') AND (`relations`.`targetId` IN ('34', '46'))) AND (`content`.`siteId`='1') AND (`elements`.`archived`=FALSE) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`entries`.`postDate` <= '2018-03-21 15:05:18') AND ((`entries`.`expiryDate` IS NULL) OR (`entries`.`expiryDate` > '2018-03-21 15:05:18'))) AND (`elements_sites`.`enabled`=TRUE)
ORDER BY `structureelements`.`lft`, `entries`.`postDate` DESC
LIMIT 3) `subquery`
INNER JOIN `entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
LEFT JOIN `structureelements` `structureelements` ON `structureelements`.`elementId` = `subquery`.`elementsId`
LEFT JOIN `relations` ON elements.id=`relations`.sourceId
GROUP BY `relations`.`sourceId`
ORDER BY `count` DESC

If I move the LIMIT to the end, it works, but I couldn't figure out how to do it in the plugin itself, modifying $query only modifies the subquery.

SELECT `elements`.`id`, `elements_sites`.`siteId`, COUNT(*) as count
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`
FROM `elements` `elements`
INNER JOIN `entries` `entries` ON `entries`.`id` = `elements`.`id`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
INNER JOIN `content` `content` ON `content`.`elementId` = `elements`.`id`
LEFT JOIN `structureelements` `structureelements` ON `structureelements`.`elementId` = `elements`.`id`
LEFT JOIN `relations` ON elements.id=`relations`.sourceId
WHERE (`elements_sites`.`siteId`='1') AND ((elements.id != '193') AND (`relations`.`targetId` IN ('34', '46'))) AND (`content`.`siteId`='1') AND (`elements`.`archived`=FALSE) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`entries`.`postDate` <= '2018-03-21 15:05:18') AND ((`entries`.`expiryDate` IS NULL) OR (`entries`.`expiryDate` > '2018-03-21 15:05:18'))) AND (`elements_sites`.`enabled`=TRUE)
ORDER BY `structureelements`.`lft`, `entries`.`postDate` DESC) `subquery`
INNER JOIN `entries` `entries` ON `entries`.`id` = `subquery`.`elementsId`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
INNER JOIN `content` `content` ON `content`.`id` = `subquery`.`contentId`
LEFT JOIN `structureelements` `structureelements` ON `structureelements`.`elementId` = `subquery`.`elementsId`
LEFT JOIN `relations` ON elements.id=`relations`.sourceId
GROUP BY `relations`.`sourceId`
ORDER BY `count` DESC
LIMIT 3

[khalwat]

Fixed in https://github.com/nystudio107/craft-similar/commit/76cfca9bfff26fd095e4479c541e30658c147ab6

Thx to @Robin for figuring it out!

[forby]

👏👏👏

[khalwat]

Actually not fixed, and related to https://github.com/nystudio107/craft-similar/issues/6

[khalwat]

I think the core issue is we're using the limitCriteria we as passed in to create the Element Query to begin with. This causes it to end up in the subQuery rather than in the query, due to the way that Element Queries work.

[khalwat]

Fixed in https://github.com/nystudio107/craft-similar/commit/066948c78090a49280f3b658c538fa1716f20bc1 thanks to @Anubarak