Unable to access column alias in WHERE clause

Question

I am trying to filter rows from my Postgres database with below query. Everything works fine but when I try to check if latesttask column is null or has some value then it shows error:

error: column "latesttask" does not exist

SELECT *, 
  (
    SELECT 
      JSON_BUILD_OBJECT(
        'id', taskhistories.id, 'task', taskhistories.task, 
        'taskname', t.name, 'project', taskhistories.project, 
        'projectname', p.name, 'started_at', 
        taskhistories.started_at, 'stopped_at', 
        taskhistories.stopped_at
      ) 
    FROM 
      tasks AS t, 
      projects AS p, 
      latesttasks, 
      taskhistories 
    WHERE 
      taskhistories.user = users.id 
      AND latesttasks.task = t.id 
      AND latesttasks.project = p.id 
      AND taskhistories.id = latesttasks.taskhistory 
      AND (
        LOWER(t.name) LIKE '%we%' 
        OR LOWER(p.name) LIKE '%we%'
      )
  ) as latestttask 
FROM 
  users 
WHERE 
  (
    latesttask IS NULL 
    AND (
      LOWER(name) LIKE '%we%' 
      OR LOWER(email) LIKE '%we%'
    ) 
    OR latesttask IS NOT NULL
  )

Erwin Brandstetter · Answer 1 · 2023-01-02T09:39:47.860

2

One "t" to many in your column alias latestttask.

But, more importantly, you cannot reference output column names in the WHERE clause anyway. There would have to be a column users.latesttask to make the WHERE clause work.

This would be a working equivalent with a LEFT JOIN to a LATERAL subquery:

SELECT *
FROM   users u
LEFT   JOIN LATERAL (
   SELECT json_build_object(
              'id', h.id, 'task', h.task, 
              'taskname', t.name, 'project', h.project, 
              'projectname', p.name, 'started_at', h.started_at,
              'stopped_at', h.stopped_at) AS latesttask 
   FROM   tasks         t
   JOIN   latesttasks   l ON l.task = t.id 
   JOIN   projects      p ON p.id = l.project
   JOIN   taskhistories h ON h.id = l.taskhistory 
   WHERE  h.user = u.id 
   AND   (lower(t.name) LIKE '%we%' 
       OR lower(p.name) LIKE '%we%')
   ) l ON true
WHERE  l.latesttask IS NOT NULL
   OR  lower(u.name) LIKE '%we%'
   OR  lower(u.email) LIKE '%we%';

edited Jan 02 '23 at 09:39

answered Jan 02 '23 at 09:19

Erwin Brandstetter

605,456
145
1,078
1,228

Hi @Erwin, Thank you for your time. I have tried with `users.latesttask` but then it returns same error for `users.latesttask` – Drashti Kheni Jan 02 '23 at 09:20
Because there is no such column, I assume. – Erwin Brandstetter Jan 02 '23 at 09:22
yes, this is what I am confused about accessing **latesttask** column which is alias column in WHERE clause – Drashti Kheni Jan 02 '23 at 09:22
It is not like I am not getting any value in latesttask column. I am getting proper json object in that field but when I add it in WHERE clause condition then it gives me error. – Drashti Kheni Jan 02 '23 at 09:23
Try reading the second sentence of my answer. – Erwin Brandstetter Jan 02 '23 at 09:25
ohh, okay. got it. Is there any other workaround to achieve this? – Drashti Kheni Jan 02 '23 at 09:26
@DrashtiKheni: Sure, various ways. I added an alternative query. – Erwin Brandstetter Jan 02 '23 at 09:40
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/250784/discussion-between-drashti-kheni-and-erwin-brandstetter). – Drashti Kheni Jan 02 '23 at 09:57
@ErwinBrandstetter I am interested in this question (https://stackoverflow.com/questions/74980692/optimize-query-contains-join-and-subquery?noredirect=1#comment132320943_74980692), can you spare some time, look at it. – jian Jan 02 '23 at 13:33
@jian: [This](https://stackoverflow.com/questions/74980692/optimize-query-contains-join-and-subquery) is a lazy question with most useful context missing, and the OP seems unresponsive. So no, not touching it. – Erwin Brandstetter Jan 02 '23 at 16:22

score 1 · Answer 2 · answered Jan 02 '23 at 09:29

Please try with...

Select * from (SELECT 
  *, 
  (
    SELECT 
      JSON_BUILD_OBJECT(
        'id', taskhistories.id, 'task', taskhistories.task, 
        'taskname', t.name, 'project', taskhistories.project, 
        'projectname', p.name, 'started_at', 
        taskhistories.started_at, 'stopped_at', 
        taskhistories.stopped_at
      ) 
    FROM 
      tasks AS t, 
      projects AS p, 
      latesttasks, 
      taskhistories 
    WHERE 
      taskhistories.user = users.id 
      AND latesttasks.task = t.id 
      AND latesttasks.project = p.id 
      AND taskhistories.id = latesttasks.taskhistory 
      AND (
        LOWER(t.name) LIKE '%we%' 
        OR LOWER(p.name) LIKE '%we%'
      )
  ) as latesttask 
FROM 
  users ) a 
WHERE 
  (
    latesttask IS NULL 
    AND (
      LOWER(name) LIKE '%we%' 
      OR LOWER(email) LIKE '%we%'
    ) 
    OR latesttask IS NOT NULL
  )

Though this is not exact answer I was looking for but helped me to reach to required answer. Thank you so much for your time. — Drashti Kheni, Jan 02 '23 at 10:50

Unable to access column alias in WHERE clause

2 Answers2