Table query doesn't show results until I toggle filters

App Building
, ,
1

  • Goal: I want the query to show all the data automatically especially when I have it set to run when inputs change. Right now, it's only showing after I toggle the filters.

  • Steps: I've removed most of the filters, I found that the issue occurs when I have select component as filters. Even if I set a default value for the filters, it doesn't run correctly. I almost have to "wake it up" but toggling the filter settings. This seems to only happen on some of my queries, other ones with similar mirrored queries work fine.

  • Details: I am on Cloud, using the select, multiselect, and daterange components as my filters with a table connected to a RDS SQL query. Here's my query

SELECT
w.id,
w.mtn,
w.line_num,
w.name,
w.container_no,
w.container_size,
w.total_q,
w.unit,
p.id as product_id,
w.notes,
w.description,
m.job_id,
m.status,
m.date_pickup,
m.date_delivered,
m.facility_name,
m.generator_name,
CASE
WHEN m.haz_status = 'Non Hazardous' THEN m.date_pickup + INTERVAL '365 days'
WHEN m.haz_status = 'Hazardous' THEN m.date_pickup + INTERVAL '10 days'
ELSE NULL -- Handle other cases as needed
END AS max_disp_date
FROM
wastes w
LEFT JOIN
products p ON w.product_id = p.id
LEFT JOIN
manifests m ON m.mtn = w.mtn
WHERE
m.status IN ('Delivered', 'Invoiced') and
(m.date_delivered BETWEEN {{daterange_filter2.value.start}} AND {{daterange_filter2.value.end}})
AND (w.product_id = ANY({{product_filter2.value}}) OR {{product_filter2.value?.length}} = 0)
AND (m.facility_id = {{facility_filter2.value}} OR {{!facility_filter2.value}})

ORDER BY
m.date_delivered DESC;

This similar query works fine:

SELECT
w.id,
w.mtn,
w.line_num,
w.name,
w.container_no,
w.container_size,
w.total_q,
w.unit,
p.id as product_id,
w.notes,
w.description,
m.job_id,
m.status,
m.date_pickup,
m.date_delivered,
m.facility_name,
m.generator_name,
CASE
WHEN m.haz_status = 'Non Hazardous' THEN m.date_pickup + INTERVAL '365 days'
WHEN m.haz_status = 'Hazardous' THEN m.date_pickup + INTERVAL '10 days'
ELSE NULL -- Handle other cases as needed
END AS max_disp_date
FROM
wastes w
LEFT JOIN
products p ON w.product_id = p.id
LEFT JOIN
manifests m ON m.mtn = w.mtn
WHERE
m.status IN ('Picked up', 'Shipped', 'Delivered')
AND (m.date_pickup BETWEEN {{daterange_filter.value.start}} AND {{daterange_filter.value.end}} OR {{!daterange_filter.value.start}})
AND (w.product_id = ANY({{product_filter.value}}) OR {{product_filter.value.length}} = 0)
AND (m.facility_id = {{facility_filter.value}} OR {{!facility_filter.value}})
AND (m.status = ANY({{status_filter.value}}) OR {{status_filter.value.length}} = 0)
ORDER BY
m.date_pickup DESC;

  • Screenshots:
    CleanShot 2024-04-05 at 12.10.44

I can DM my export to whoever is helping, I don't want to post my app live on forum.

2

You need to update the query to handle the situations in which your select components are not defined. In your first query, the filters are not defined, so the criteria

becomes (m.date_delivered BETWEEN undefined AND undefined) which evaluates to false and you get no records.

In your second query, you have additional criteria (sort of a fail safe)

so by adding OR {{!daterange_filter.value.start}} the full statement becomes (m.date_pickup BETWEEN undefined AND undefined OR !undefined) which is (false OR true) which is true.

You may wish to modify slightly to OR ({{!daterange_filter.value.start}} AND {{!daterange_filter.value.end}}) to only show all records only if both the start and end of the range are blank, depending on your use case. If you want to exhaustively control all possibilities, you may want to modify to something like:

AND (   -- When both filters applied, records with date between them
        m.date_pickup BETWEEN {{daterange_filter.value.start}} AND {{daterange_filter.value.end}} 
        -- Or when neither filter applied, all records 
     OR ({{!daterange_filter.value.start}} AND {{!daterange_filter.value.end}})
        -- Or when only end filter applied, all records prior to that date
     OR (    ({{!daterange_filter.value.start}} AND {{!!daterange_filter.value.end}})
         AND m.date_pickup < {{daterange_filter.value.end}})
        -- Or when only start filter applied, all records 
     OR (    ({{!!daterange_filter.value.start}} AND {{!daterange_filter.value.end}})
         AND m.date_pickup > {{daterange_filter.value.start}})
    )
1 Like
3

Doh! Thank you!