Unable to access column alias in WHERE clause-CodePudding

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
  )

CodePudding user response：

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%';

CodePudding user response：

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
  )