SQL Query using two select values

App Building
1

I am trying to build a PostgreSQL query. I basically have two drop downs that would allow a user to select if they want to see shipments by dealer or shipments by month. Select1 is the dealer, select2 is the month. Individually, the queries work fine.

SELECT
  id, ship_date, dealer, delivery_add, model, add_ons, color, serial_num, sales_order, customer_name
FROM
  shipping
WHERE
  ({{ !select1.value }} OR Dealer = {{ select1.value }}) AND
  ship_date > {{ moment(new Date()) }}
ORDER BY
  ship_date, dealer

That query works, but when I try to add select2, like this:

SELECT
  id, ship_date, dealer, delivery_add, model, add_ons, color, serial_num, sales_order, customer_name
FROM
  shipping
WHERE
  (({{ !select1.value }} OR Dealer = {{ select1.value }}) OR ({{ !select2.value }} OR EXTRACT(MONTH FROM ship_date::date) = {{ select2.value }})) AND
  ship_date > {{ moment(new Date()) }}
ORDER BY
  ship_date, dealer

I get all the records that are greater than today, regardless of the month. Can anyone tell me what I'm doing wrong?

2

Your query as written will always have the condition that ship_date > {{ moment(new Date()) }} . It seems that maybe you want that only if select2 isn’t populated? Something like:

WHERE
(
    ( {{ !select1.value }} OR Dealer = {{ select1.value }} ) 
OR 
    (
      ( {{ !select2.value }} AND ship_date > {{ moment(new Date()) }} )
     OR 
      ( EXTRACT(MONTH FROM ship_date::date) = {{ select2.value }} )
    )
)
3

Thanks @jg80. Actually I do want ship_date > {{ moment(new Date()) }} to always be a condition. These are for upcoming shipments, so if they select a dealer name, I want to show the upcoming shipments for that dealer. If they select a month, I want to show the upcoming shipments for that month. Ideally, I would like it to show upcoming shipments for both dealer and month if they select both. I'm thinking I would have to set variables to do that.

4

Ok, then in that case i think you want

WHERE
  /* no dealer selected or Dealer is selected value */
  ({{ !select1.value }} OR Dealer = {{ select1.value }}) 
AND
  /* no month selected or ship date in selected month */
  ({{ !select2.value }} OR EXTRACT(MONTH FROM ship_date::date) = {{ select2.value }}) 
AND
  /* ship date is always in the future */
  ship_date > {{ moment(new Date()) }}
5

I thought that looked like exactly what I had put in, but yours worked, so I must've missed something in the syntax. Any way, thank you.

6

Glad it worked - I think it came down to AND instead of OR because the OR was already within the condition…in any case, happy building :slight_smile: