PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to filter a query with a parameter only if it's passed?

I dont want to write two statements like this

if(filter_by_company) {
  query..
} else {
  query...
}

So select users and they are in companies and I the admin can filter like by company id. How can I say when filter not exists then dont make the condition ?

SELECT * FROM users u
LEFT JOIN clients c
ON c.id = u.company_id
WHERE c.company_id = 'NAME' IF EXISTS ?? ELSE DONT ASK

how can I do it ?

Tags:

3

Answers


  1. 0

    try this

    SELECT * FROM users u
LEFT JOIN clients c ON c.id = u.company_id
WHERE (:filter_by_company = 0 OR c.company_id = :company_id)
    Login or Signup to reply.
  2. 0

    Assuming you are passing company_id as a positional parameter, pass it twice in succession (pass null in your programming language when you don’t filter_by_company):

    SELECT * FROM users u
LEFT JOIN clients c
ON c.id = u.company_id
WHERE (? IS NULL OR c.company_id = ?);

    If your client allows named parameters, you can use:

    SELECT * FROM users u
LEFT JOIN clients c
ON c.id = u.company_id
WHERE (:company_id IS NULL OR c.company_id = :company_id);

    or some other equivalent parameter syntax.

    Login or Signup to reply.
  3. 0

    What I think you’re asking is how do you write one query which can handle both filtering by company, and not filtering by company.

    SELECT * 
FROM users u
LEFT JOIN clients c
ON c.id = u.company_id
WHERE c.company_id = 'NAME'

    and

    SELECT * 
FROM users u
LEFT JOIN clients c
ON c.id = u.company_id

    There’s two approaches. First is to try to construct this as one query. The second is to use a more flexible approach to building queries.

    To do it in one query, you’d pass in a boolean flag to determine whether or not to filter by company.

    SELECT * 
FROM users u
LEFT JOIN clients c
ON c.id = u.company_id
WHERE (:any_company or c.id = :company_id)

    Demonstration.

    This is acceptable for something simple, but this technique is very limited and leads to unnecessarily complex queries.

    Far better is to use a SQL query builder to build up your queries. Then you can write a generic query and add clauses onto it. What you use depends on your choice of programming languages, for these examples we’ll use Knex.js.

    First, build the basic query.

    const user_client_query = knex
  .select('*')
  .from('users')
  .leftJoin('clients', 'users.company_id', 'clients.id');

    And add to it as needed.

    const filter_user_by_client = user_client_query.where('clients.company_id', 'ConHuge Corp')

    filter_user_by_client is a new object, user_client_query is unaffected.

    The beauty of this technique is you can add limits, orders, more where clauses, more joins, and so on.

    user_client_query.orderBy('user.last_login').limit(10)

    And it will adapt to SQL dialects for you.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search