[3.7.40.1]: Custom element query

[CMeldgaard]

What happened?

These lines prevents custom element query:

cms/src/elements/db/ElementQuery.php

Lines 1353 to 1355 in e9914a9

	if (!$this->_joinedElementTable && $this->elementType) {
	$this->subQuery->andWhere(['elements.type' => $this->elementType]);
	}

For instance, in commerce i need to query two elements, to get their ID's for a paginated category page:

$query = Purchasable::find();
$query->elementType = "LIKE = '%'";
$query = $query
    ->leftJoin('giftvoucher_vouchers', '[[giftvoucher_vouchers.id]] = [[elements.id]]')
    ->leftJoin('commerce_products', '[[commerce_products.id]] = [[elements.id]]')
    ->leftJoin('content', '[[content.elementId]] = [[elements.id]]')
    ->groupBy('elements.id')
    ->where(
        'elements.enabled = true',
    )
    ->relatedTo(
        [
            'or',
            [
                'targetElement' => $category,
                'sourceSite' => $currentSite
            ],
            [
                'targetElement' => $category->getChildren(),
                'sourceSite' => $currentSite
            ]
        ]
    )
    ->orderBy($orderBy);

Up into the _joinedElementTable was introduced, this was a possible query, where i could get multiple purchasables that was related to a category, but its no possible anymore because the _joinedElementTable now adds element.type to the query, limiting it to base element class.

[brandonkelly]

Which full class name is Purchasable referencing? Pretty sure Commerce doesn’t have a generic multi-element-type query class.

[CMeldgaard]

@brandonkelly, It's referencing craft\base\Element

Befor the update, this was the query that was beeing run:

SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `elements`.`canonicalId`, `elements`.`dateLastMerged`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`
FROM `elements` `elements`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
LEFT JOIN `giftvoucher_vouchers` ON `giftvoucher_vouchers`.`id` = `elements`.`id`
LEFT JOIN `commerce_products` ON `commerce_products`.`id` = `elements`.`id`
LEFT JOIN `content` ON `content`.`elementId` = `elements`.`id`
WHERE (elements.enabled = true) AND (`elements_sites`.`siteId`=2) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`id` IN (SELECT `targets1`.`sourceId`
FROM `relations` `targets1`
WHERE (`targets1`.`targetId`=20) AND ((`targets1`.`sourceSiteId` IS NULL) OR (`targets1`.`sourceSiteId`=2)))) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `field_promoted` DESC, `title`, `elements`.`id`) `subquery`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
LEFT JOIN `giftvoucher_vouchers` ON `giftvoucher_vouchers`.`id` = `elements`.`id`
LEFT JOIN `commerce_products` ON `commerce_products`.`id` = `elements`.`id`
LEFT JOIN `content` ON `content`.`elementId` = `elements`.`id`
GROUP BY `elements`.`id`
ORDER BY `field_promoted` DESC, `title`, `elements`.`id`

But after the update, this is the query now:

SELECT `elements`.`id`, `elements`.`fieldLayoutId`, `elements`.`uid`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_sites`.`id` AS `siteSettingsId`, `elements_sites`.`slug`, `elements_sites`.`siteId`, `elements_sites`.`uri`, `elements_sites`.`enabled` AS `enabledForSite`, `elements`.`canonicalId`, `elements`.`dateLastMerged`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`
FROM `elements` `elements`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
LEFT JOIN `giftvoucher_vouchers` ON `giftvoucher_vouchers`.`id` = `elements`.`id`
LEFT JOIN `commerce_products` ON `commerce_products`.`id` = `elements`.`id`
LEFT JOIN `content` ON `content`.`elementId` = `elements`.`id`
WHERE (elements.enabled = true) AND (`elements_sites`.`siteId`=2) AND (`elements`.`archived`=FALSE) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`id` IN (SELECT `targets1`.`sourceId`
FROM `relations` `targets1`
WHERE (`targets1`.`targetId`=20) AND ((`targets1`.`sourceSiteId` IS NULL) OR (`targets1`.`sourceSiteId`=2)))) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL) AND (`elements`.`type`='craft\\commerce\\base\\Purchasable')
ORDER BY `field_promoted` DESC, `title`, `elements`.`id`) `subquery`
INNER JOIN `elements` `elements` ON `elements`.`id` = `subquery`.`elementsId`
INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`id` = `subquery`.`elementsSitesId`
LEFT JOIN `giftvoucher_vouchers` ON `giftvoucher_vouchers`.`id` = `elements`.`id`
LEFT JOIN `commerce_products` ON `commerce_products`.`id` = `elements`.`id`
LEFT JOIN `content` ON `content`.`elementId` = `elements`.`id`
GROUP BY `elements`.`id`
ORDER BY `field_promoted` DESC, `title`, `elements`.`id`

And here the issue is that it adds AND (``elements``.``type``='craft\\commerce\\base\\Purchasable'), meaning that it won't find any vouchers or products, because their element.type isn't craft\\commerce\\base\\Purchasable

[brandonkelly]

Gotcha. Got this fixed for the next release. That’s not an intended use of craft\base\Element::find() though – it should only be called from an actual element type class, not craft\base\Element or craft\commerce\base\Purchasable. So just be aware of that, as I can’t promise this is the last time something like this will come up for you.

[CMeldgaard]

Thank you, will note that down - is there a better way to query for multiple elements, and at the same time beeing able to limit them to relations to categories ? Because it's actually tricky to make a commerce category page if you have multiple purchasables in the shop.

[brandonkelly]

Querying for multiple elements isn’t supported yet. Aware it’s needed, so we’re hoping to come up with a reliable way to do it for Craft 5.

[brandonkelly]

Craft 3.7.41 is out now with that change, by the way.