CODEWITHSUNDEEP
postgresqldrupal-8drupal-database
Chris
Chris

Reputation: 79

Drupal 8 and PostgreSQL: How can I use the result of an expression inside a WHERE clause?

Hi there!

I construct a database query using Drupal's query interface. With that, I also successfully add an expression that generates a conditional value. The contents of that conditional field is a ISO formatted datestring.

I now would like to use that computed field inside my WHERE clause. In another project using MySQL I was able to just use the name carrying the expression result inside the where-clause, but that does not seem to work in PostgreSQL. I always get the "column does not exist"-error, wheter if I prefix the expression name with the database table alias or not.

The (raw and shortened) query looks like the following (still contains the value placeholders):

SELECT
  base.nid AS nid,
  base.type AS type,
  date.field_date_value AS start,
  date.field_date_end_value AS "end",
  CASE WHEN COALESCE(LENGTH(date.field_date_end_value), 0) > 0 THEN date.field_date_end_value ELSE date.field_date_value END AS datefilter
FROM {node} base
LEFT JOIN {node__field_date} date ON base.type = date.bundle AND base.nid = date.entity_id AND base.vid = date.revision_id AND attr.langcode = date.langcode AND date.deleted = 0
WHERE (base.type = :db_condition_placeholder_0) AND (datefilter > :db_condition_placeholder_2)
ORDER BY field_date_value ASC NULLS FIRST

As already stated, it does not make any difference if I apply the filter using "date.datefilter" or just "datefilter". The field/expression turns out just fine on the result list if I do not attempt to use it in the WHERE part. So, how can I use the result of the expression to filter the query result?

Any hint is appreciated! Thanks in advance!

Upvotes: 0

Views: 150

Answers (1)

pifor
pifor

Reputation: 7882

You can try to create a derived table that defines the new column "datefilter".

For example instead of coding:

select case when c1 > 0 then c1 else -c1 end as c, c2, c3 
from t 
where c = 0;
ERROR:  column "c" does not exist
LINE 1: ...c1 > 0 then c1 else -c1 end as c, c2, c3 from t where c = 0;

Define a derived table that defines the new column and used it in a outer SELECT:

select c, c2, c3 
from 
 (select case when c1 > 0 then c1 else -c1 end as c, c2, c3 from t) as t2 
where c=0;

Upvotes: 0

Related Questions