CODEWITHSUNDEEP
mysql
TMH
TMH

Reputation: 6246

Is something like this possible using just MySql?

I have this query that I'm having trouble with.

SELECT 
    f.*
FROM 
    siv_forms f
WHERE 
    f.urlname = 'test_form_custom_url' 
    AND f.active = 1 
    AND find_in_set(50, f.siteIds)
    AND f.endDate > 1404307448
    IF (f.max_responses > 0) THEN
        AND (SELECT COUNT(fr.id) as count FROM siv_forms_responses fr WHERE formId = f.id) > f.max_responses
    END IF

Basically I only want the last AND condition to be included if f.max_responses is bigger than 0.

I could do this with a few separate queries and PHP but I'm trying to reduce the amount of queries I have to do.

Upvotes: 3

Views: 49

Answers (2)

DigiLive
DigiLive

Reputation: 1103

Another way of thinking...
You could include the data from siv_forms_responses anyway and just don't use or delete the records in php when needed.

Or because you have one or more relations between these tables, create a VIEW and make a SELECT statement on that VIEW.

That way the SQL statements remain simpler (clearer) and perhaps you php code too.

Upvotes: 0

Ronald Wildenberg
Ronald Wildenberg

Reputation: 32094

A CASE expression should do the trick. I'm not able to test this at the moment but in its simplest form:

SELECT 
  f.*
FROM 
  siv_forms f
WHERE 
      f.urlname = 'test_form_custom_url' 
  AND f.active = 1 
  AND find_in_set(50, f.siteIds)
  AND f.endDate > 1404307448
  AND (SELECT COUNT(fr.id) as count
       FROM siv_forms_responses fr
       WHERE formId = f.id) > CASE
                                WHEN f.max_responses > 0 THEN f.max_responses
                                ELSE -1
                              END CASE

You have to be a bit creative in the ELSE part. I used -1 here but I'm not sure what you want to happen when f.max_responses <= 0. What you essentially do here is say: x must be larger than y if y > 0, else x must be larger than -1.

Maybe you can wrap the entire condition in the CASE statement. Not sure if that will work (since I can't test it at the moment):

  AND CASE
        WHEN f.max_responses > 0 THEN
          (SELECT COUNT(fr.id) as count
           FROM siv_forms_responses fr
           WHERE formId = f.id) > f.max_responses
        ELSE TRUE
      END CASE

Upvotes: 2

Related Questions