[3.x]: ElementQuery::status with Element::STATUS_ARCHIVED adds conflicting parameters to query

[msbit]

What happened?

Running something like:

use craft\base\Element;
use craft\elements\Category;

assert(Craft::$app->getElements()->saveElement(new Category([
    'archived' => true,
    'groupId' => Craft::$app->getCategories()->getAllGroups()[0]->id,
    'slug' => 'slug',
    'title' => 'title',
])));
Category::find()->status(Element::STATUS_ARCHIVED)->one();

returns null, and looking at the output of the QueryBuilder::build(...) (from a var_dump at vendor/yiisoft/yii2/db/Query.php:158):

SQL:

string(3582) "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`, `categories`.`groupId`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_adminOrderNotes`, `content`.`field_body`, `content`.`field_bookingFee`, `content`.`field_productBusCode`, `content`.`field_userDateOfBirth`, `content`.`field_eventFriendlyName`, `content`.`field_ticketEventIdMsadb`, `content`.`field_eventStartDate`, `content`.`field_festivalKeyName`, `content`.`field_userGender`, `content`.`field_gpBody`, `content`.`field_hasMultipleDropoffLocations`, `content`.`field_ticketBallotRound`, `content`.`field_mmfBody`, `content`.`field_options`, `content`.`field_order`, `content`.`field_orderExpires`, `content`.`field_orderGoldenKey`, `content`.`field_orderManuallyCreated`, `content`.`field_userPhone`, `content`.`field_productPurchaseable`, `content`.`field_productRequiresShipping`, `content`.`field_productTimeable`, `content`.`field_ticketProductTypeSalesReportMultiplier`, `content`.`field_ticketRoundIdMsadb`, `content`.`field_singleExtrasSelectedOptions`, `content`.`field_systemToggle`, `content`.`field_ticketInitialStock`, `content`.`field_ticketPublicExpiryDate`, `content`.`field_ticketRoundRequiresPostback`, `content`.`field_ticketThresholdCautionReached`, `content`.`field_ticketThresholdDangerReached`, `content`.`field_ticketThresholdWarningReached`, `content`.`field_userEmergencyContact1Name`, `content`.`field_userEmergencyContact1Phone`, `content`.`field_userEmergencyContact2Name`, `content`.`field_userEmergencyContact2Phone`, `content`.`field_userMsadbPersonId`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level`, `structureelements`.`structureId`
FROM (SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `elementsSitesId`, `content`.`id` AS `contentId`, `structureelements`.`structureId`
FROM {{%elements}} `elements`
INNER JOIN {{%categories}} `categories` ON [[categories.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]]) AND (EXISTS (SELECT *
FROM {{%structures}}
WHERE ([[id]] = [[structureelements.structureId]]) AND (`dateDeleted` IS NULL)))
WHERE (`elements`.`archived`=:qp0) AND (`elements`.`archived`=:qp1) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL)
ORDER BY `structureelements`.`lft`, `elements`.`dateCreated` DESC
LIMIT 1) `subquery`
INNER JOIN {{%categories}} `categories` ON [[categories.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]]) AND ([[structureelements.structureId]] = [[subquery.structureId]])
ORDER BY `structureelements`.`lft`, `elements`.`dateCreated` DESC"

Parameters:

array(2) {
  [":qp0"]=>
  bool(false)
  [":qp1"]=>
  bool(true)
}

it looks like the subquery attempts to select for both archived true and false.

Craft CMS version

3.8.9

PHP version

7.4.33

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

[i-just]

Hi, thanks for reporting!
You can get the archived elements via ->archived(), but you’re also right; if you do it via ->status(), there’s a clash. I just raised a PR to adjust this.

[brandonkelly]

Craft 3.8.17 and 4.4.17 are out with a fix for this. Thanks again!