Getting column not found when using 'Entry::find()' to find the matching channel entry in custom plugin

Question

We are building a plugin to perform some custom operations with an ERP system.

We created a section for managing the users from ERP. We are able to create new users as channel entries and update them when required programmatically using a custom plugin as expected.

But we are having a problem when using 'Entry::find()' with the where clause to use the user entered email address in the front end form to find the matching entry and to get some other field values from channel entry.

But when using the where condition with the field handle on 'Entry::find()' gets catch error of column not found. On inspecting the error seems like there are some random strings added as suffixes to the column name(field_customerEmailAddress_ywsmdgzc) to the newly created fields.

 $entries = Entry::find()
        ->section('usersAccount')
        ->where(['field_customerEmailAddress' => [ '=', $email]])
        ->one();

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_customerEmailAddress' in 'where clause' The SQL being executed was: 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`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_charityDescription_grtrmddb`, `content`.`field_address`, `content`.`field_australianPhoneNumber`, `content`.`field_customerBusinessName_kahdekjw`, `content`.`field_categoryGroupNo`, `content`.`field_charityLink_suzufaks`, `content`.`field_contactnumber`, `content`.`field_customerCountry_ioredmop`, `content`.`field_customerStatus_twshxbwr`, `content`.`field_dataSheet_nbwfwopp`, `content`.`field_csdescription`, `content`.`field_description`, `content`.`field_displayProductCatelogue`, `content`.`field_displayRequestPrintedCatalogues`, `content`.`field_drawingCaption_fyfecavf`, `content`.`field_email`, `content`.`field_customerEmailAddress_ywsmdgzc`, `content`.`field_embedUrl`, `content`.`field_facebook`, `content`.`field_finishCode`, `content`.`field_fittingInstructionsDoc`, `content`.`field_fittingProductCode`, `content`.`field_fittingProgrammingDetails`, `content`.`field_csheading`, `content`.`field_heading`, `content`.`field_instagram`, `content`.`field_introParagraph`, `content`.`field_leadTime`, `content`.`field_LinkFiles`, `content`.`field_linkItNoCustomText`, `content`.`field_linkedin`, `content`.`field_linkit`, `content`.`field_nameOfDataSheet_lamtsznd`, `content`.`field_nameOfTheCharity_yhiznjge`, `content`.`field_newsBrief`, `content`.`field_newsHeading`, `content`.`field_note`, `content`.`field_Ourteamcontent`, `content`.`field_OurTeamTitle`, `content`.`field_customerPassword_krakvwtn`, `content`.`field_pinterest`, `content`.`field_plainText`, `content`.`field_customerPostcode_dopoowwd`, `content`.`field_productDescription`, `content`.`field_richtext`, `content`.`field_salesHtml`, `content`.`field_customerSalesRepresentativeEmailAddress_dnblmyey`, `content`.`field_customerSalesRepresentativeID_xxrlbnem`, `content`.`field_customerSalesRepresentativeName_kadjeopo`, `content`.`field_customerSalesRepresentativePhoneNumber_sgquwray`, `content`.`field_seo`, `content`.`field_singleLineText`, `content`.`field_staffBio`, `content`.`field_staffEmail`, `content`.`field_staffLinkedin`, `content`.`field_staffName`, `content`.`field_staffPhone`, `content`.`field_staffPosition`, `content`.`field_stockistExcerpt`, `content`.`field_storyHeading`, `content`.`field_storyHistory`, `content`.`field_storySubIntroduction`, `content`.`field_customerStreetAddress_sgmdegps`, `content`.`field_customerSuburb_nzhbwxbi`, `content`.`field_swatchDescription`, `content`.`field_swatchName`, `content`.`field_whoWeAre` 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`) AND (`content`.`siteId` = `elements_sites`.`siteId`) WHERE (`entries`.`sectionId`='28') AND (`field_customerEmailAddress` IN ('=', 'test@g.c')) AND (`elements_sites`.`siteId`=1) AND (`elements`.`archived`=FALSE) AND (((`elements`.`enabled`=TRUE) AND (`elements_sites`.`enabled`=TRUE)) AND (`entries`.`postDate` <= '2022-05-25 00:23:11') AND ((`entries`.`expiryDate` IS NULL) OR (`entries`.`expiryDate` > '2022-05-25 00:23:11'))) AND (`elements`.`dateDeleted` IS NULL) AND (`elements`.`draftId` IS NULL) AND (`elements`.`revisionId` IS NULL) ORDER BY `entries`.`postDate` DESC LIMIT 1) `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` ORDER BY `entries`.`postDate` DESC
An Error occurred while handling another error:
yii\web\HeadersAlreadySentException: Headers already sent in /container/application/Windsor/plugins/windsor/src/controllers/CustomersController.php on line 340. in /container/application/Windsor/vendor/yiisoft/yii2/web/Response.php:368
Stack trace:
#0 /container/application/Windsor/vendor/yiisoft/yii2/web/Response.php(341): yii\web\Response->sendHeaders()
#1 /container/application/Windsor/vendor/yiisoft/yii2/web/ErrorHandler.php(136): yii\web\Response->send()
#2 /container/application/Windsor/vendor/craftcms/cms/src/web/ErrorHandler.php(192): yii\web\ErrorHandler->renderException(Object(yii\web\HeadersAlreadySentException))
#3 /container/application/Windsor/vendor/yiisoft/yii2/base/ErrorHandler.php(135): craft\web\ErrorHandler->renderException(Object(yii\web\HeadersAlreadySentException))
#4 /container/application/Windsor/vendor/craftcms/cms/src/web/ErrorHandler.php(71): yii\base\ErrorHandler->handleException(Object(yii\web\HeadersAlreadySentException))
#5 [internal function]: craft\web\ErrorHandler->handleException(Object(yii\web\HeadersAlreadySentException))
#6 {main}
Previous exception:
yii\web\HeadersAlreadySentException: Headers already sent in /container/application/Windsor/plugins/windsor/src/controllers/CustomersController.php on line 340. in /container/application/Windsor/vendor/yiisoft/yii2/web/Response.php:368
Stack trace:
#0 /container/application/Windsor/vendor/yiisoft/yii2/web/Response.php(341): yii\web\Response->sendHeaders()
#1 /container/application/Windsor/vendor/yiisoft/yii2/base/Application.php(390): yii\web\Response->send()
#2 /container/application/Windsor/web/index.php(21): yii\base\Application->run()
#3 {main}

Can you please advise whether this is related to the CMS or related to the plugin?

This happened to all the new fields that have been created in the development area. The live website is still working as expected in the same Craft version. The issue started to occur after adding new fields to the section.

Craft version: 3.7.42

If it is related to a plugin how can I resolve this?

Answer 1

All custom fields get the field_ prefix and a pseudo-random suffix like _ywsmdgzc for their column name in the database. If you want to use custom conditions in a query, you need to include both. To make this easier, you can use the helper method ElementHelper::fieldColumnFromField:

$customerEmailAddressField = \Craft::$app->getFields()->getFieldByHandle('customerEmailAddress');
$customerEmailAddressColumn = \craft\helpers\ElementHelper::fieldColumnFromField($customerEmailAddressField);
$entries = Entry::find()
    ->section('usersAccount')
    ->where([$customerEmailAddressColumn => [ '=', $email]])
    ->one();

Note that field suffixes were introduced in 3.7, so if your plugin needs to support older Craft versions, you'll have to check the version and adjust the query accordingly.