Complex where condition with entity field query (D8)

Question

Is there a method similar to the D7 dynamic query where method to allow us to use arbitrary snippets of SQL to restrict the result set?

Take the following MYSQL query as an example:

SELECT id, publish_date
FROM teaching_period
WHERE publish_date IS NULL OR UNIX_TIMESTAMP(STR_TO_DATE(CONCAT(publish_date, ' 23:59:59'), '%Y-%m-%d %H:%i:%s')) > UNIX_TIMESTAMP(CURRENT_TIMESTAMP)
ORDER BY created DESC
LIMIT 0, 1

How can I create the where statement in this query using the D8 entity query API? Is it even possible?

Answer 1

No, in general, it is not. Entity query is not an SQL-query replacement, it designed to also work with other storage backends, so you can not use 100% of the SQL features.

If you need that, you have to implement your own SQL query.

That said, I don't quite understand why your special SQL logic is even necessary, date fields can be queried natively, e.g. publish_date > :date should just work, as long as you provide the date in the correct format as and timezone input.

See How to do a date range entityQuery with a Date only field in Drupal 8?