Add custom field to EntryQuery

[mikolamin]

In Craft 4 I used query with custom fields

$where = [
                'and',
                ['<=', 'content.field_publishDate', $currentDate],
                [
                    'or',
                    [
                        'and',
                        ['content.field_endDate' => null],
                        ['>=', 'content.field_startDate', $yesterdayDate],
                    ],
                    ['>=', 'content.field_endDate', $yesterdayDate]
                ]
            ];

$query = Entry::find()
            ->section('events')
            ->where($where)
            ->orderBy('startDate asc');

How I can describe the custom fields in the where condition in Craft 5?

[brandonkelly]

You can use the fields’ getValueSql() methods to get a SQL fragment that can be used in place of the column names:

// Get the “Event” entry type's field layout
$entryType = Craft::$app->entries->getEntryTypeByHandle('event');
$fieldLayout = $entryType->getFieldLayout();

// Get the fields we care about
$publishDateField = $fieldLayout->getFieldByHandle('publishDate');
$endDateField = $fieldLayout->getFieldByHandle('endDate');
$startDateField = $fieldLayout->getFieldByHandle('startDate');

$where = [
    'and',
    ['<=', $publishDateField->getValueSql(), $currentDate],
    [
        'or',
        [
            'and',
            [$endDateField->getValueSql() => null],
            ['>=', $startDateField->getValueSql(), $yesterdayDate],
        ],
        ['>=', $endDateField->getValueSql(), $yesterdayDate]
    ]
];

[mikolamin]

Thanks a lot! It works.