CODEWITHSUNDEEP
sqlpostgresqlconditional-statements
Yudi Yohanes Septian Gotama
Yudi Yohanes Septian Gotama

Reputation: 599

PostgreSQL Using Alias at Where Condition

How to set condition to last_order field where last order is custom field created by max sql function and group by user's id :

SELECT "users"."id", "users"."name", "users"."email", "users"."type", "users"."created_at", MAX(TO_CHAR(order_details.created_at, 'YYYY-MM-dd HH24:MM')) AS last_order
FROM "users"
LEFT JOIN "order_details" on "order_details"."user_id" = "users"."id"
WHERE EXISTS (
    SELECT *
    FROM "roles"
    LEFT JOIN "role_users" on "roles"."id" = "role_users"."role_id"
    WHERE "role_users"."user_id" = "users"."id" and "slug" = 'member'
) AND "users"."status" in (0)
AND (LOWER(CAST("users"."id" as TEXT)) LIKE '%%'
OR LOWER(CAST("email" as TEXT)) LIKE '%%'
OR LOWER(CAST("last_order" as TEXT)) LIKE '%%'
OR LOWER(CAST("name" as TEXT)) LIKE '%%')
GROUP BY "users"."id"
ORDER BY "created_at" desc

I always get this error:

column "last_order" does not exist

How to use last_order?? i already assing it at right alias.

That query generated by datatables, the error cause is i used the alias name for datatables's search function, my datatables code just like below:

columns: [
    { data: 'id', name: 'users.id', class: 'text-center', orderable: false },
    { data: 'email', name: 'email' },
    { data: 'last_order', name: 'last_order', class: 'text-center', orderable: true },
    { data: 'name', name: 'name' },
]

Upvotes: 2

Views: 4215

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Two problems:

  • You cannot use column aliases defined in the select in the where clause.
  • You cannot use aggregation functions in the where clause.

Move the condition to a having clause:

. . .
GROUP BY "users"."id"
HAVING LOWER(CAST(last_order as TEXT)) LIKE '%%'
ORDER BY "created_at" desc

I assume your like patterns are actually more interesting than '%%'. And, you shouldn't be using like for date/time columns. Just use appropriate equality/range comparisons or comparisons on the date parts.

Upvotes: 3

Related Questions