Filtering default date ranges

App Building
1

I have a table query where it filters a date range and works fine once dates are entered.
My dateRange1 Start date and End date are set to now().

When the page loads the state looks like this:
chrome_Ttof9AUThB

As soon as I select one of the dates the state shows a date and the filter works.
chrome_6eHfhUEL35

Provided I have something in start and end date the filter works but if either are left blank no records show.

I suppose there are 2 questions:

  1. How do I get the filter to take into account the default?
  2. How do I properly allow for blank entries?
SELECT 
    sa.id AS application_id, 
    sa.visit_date, 
    sa.product_type, 
    sa.operator_id, 
    sa.bags_applied, 
    sa.scheduled_dispatch_id, 
    sa.route_id, 
    sa.bags_estimated, 
    sa.status, 
    sa.request_date, 
    sa.completion_date,
    op.id AS operator_id, 
    CONCAT(op.first_name, ' ', op.last_name) AS fullName
FROM 
    salt_applications sa
LEFT JOIN 
    operators op
ON 
    sa.operator_id = op.id
WHERE
    (
        {{ dateRange1.value.start }} = '' 
        OR {{ dateRange1.value.end }} = '' 
        OR (
            sa.visit_date::date >= {{ dateRange1.value.start }}::date
            AND sa.visit_date::date <= {{ dateRange1.value.end }}::date
        )
    )
ORDER BY
    sa.visit_date DESC, sa.status DESC, fullName, sa.product_type;
2

I found a way. Possibly overkill but it works. Here it is if anyone is in a similar predicament.

SELECT 
    sa.id AS application_id, 
    sa.visit_date, 
    sa.product_type, 
    sa.operator_id, 
    sa.bags_applied, 
    sa.scheduled_dispatch_id, 
    sa.route_id, 
    sa.bags_estimated, 
    sa.status, 
    sa.request_date, 
    sa.completion_date,
    op.id AS operator_id, 
    CONCAT(op.first_name, ' ', op.last_name) AS fullName
FROM 
    salt_applications sa
LEFT JOIN 
    operators op
ON 
    sa.operator_id = op.id
WHERE
    (
        {{ dateRange1.value.start }} = '' 
        OR {{ dateRange1.value.end }} = '' 
        OR (
            sa.visit_date::date >= COALESCE(NULLIF({{ dateRange1.value.start }}, '')::date, '1900-01-01'::date)
            AND sa.visit_date::date <= COALESCE(NULLIF({{ dateRange1.value.end }}, '')::date, '2100-12-31'::date)
        )
    )
ORDER BY
    sa.visit_date DESC, sa.status DESC, fullName, sa.product_type;
1 Like