Conditional filters in query

Queries and Resources
1

Similar to How to send null values to query from text box

I have written this query

query MyQuery(
  $limit: Int, 
  $offset: Int, 
  $first_name: String,
  $last_name: String,
  $email: String,
  $gateway: String,
  $start_date: timestamp, 
  $end_date: timestamp) {
  payment_transactions(
    where: {
      _and: [
        { 
          created_at: { 
            _gte: $start_date, 
            _lte: $end_date
          } 
        },
        { 
          transaction_id : { _eq: $gateway }
        },
        { 
          nft_buys: { 
            user: {
              _or: [
                { 
                  first_name: { _ilike: $first_name }
                }, 
                { 
                  last_name: { _ilike: $last_name }
                },
                { 
                  email: { _ilike: $email }
                }
                
              ]
            },
            
          }
        }
      ]
    },
    limit: $limit,
    offset: $offset
  ) {
    id
    amount
    status
    ip
    created_at
    service
    transaction_id
    order_number
    platform_fee
    blockchain_fee
    nft_buys {
      contract_address
      slug
      token_id
      status
      tx_hash
      transaction_id
      nft_data
      invoice_link
      user {
        email
        crypto_address
        first_name
        last_name
        affiliate {
          email
        }
      }
    }
  }
}

I want to get the data in specific range using some filters. So it becomes and operation. But when I hit the query it returns nothing bcz the variables first_name, last_name, email have no values.

So can u guide me how to fix the problem.

The Goal is:

  • Get data in specific range
  • If filters are defined then get the data in specific range with the defined filters
2

Hello @Bilal_Anjum ,

Thank you for your question - here are some thoughts.

The issue you are encountering may have to do with GraphQL queries and optional filters. When you pass null or undefined to _ilike operation, it won't return anything as it attempts to match against 'null'.

Below are the recommended solutions summarized from the 3 sources:

  1. Check for null values: Verify on the client side that the entered values (first_name, last_name, and email) are not empty or null. If they are, exclude them from your GraphQL query.
  2. Use wildcard symbol: An easier solution would be to replace null values with the wildcard symbol %. This would return all records, ignoring the filter, as shown below:
const firstNameFilter = first_name ? first_name : '%';
const lastNameFilter = last_name ? last_name : '%';
const emailFilter = email ? email : '%';

You can then pass these variables in your where settings.

Bear in mind, these solutions are based on the assumption that not having a first_name, last_name, or email means you want all result records.

To simplify further, you can also dynamically add filters. This ensures that if a filter isn't defined, that field will not be used to filter data.

Make sure to replace timestamp with suitable GraphQL type based on your database configuration for your start_date and end_date parameters.

Lastly, be aware that if the variables are strings, you should use '%' rather than null, as like or _ilike with null will be false, but column like '%' is true if the column is not null. Assign default values with % and replace it with the desired filter when needed:

{
  "limit": 10,
  "offset": 0,
  "first_name": "%",
  "last_name": "%",
  "email": "%"
}

This way, if a filter isn't set, you get every data record. However, if a filter is set, the data is filtered accordingly.

Hopefully this helps a bit.

-Brett