Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Nov 2011
    Location
    Sunshine Coast; Australia
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question SQL Giving where clause dominance?

    Hi Guys;
    I am having some trouble with a category filter I am making for VM2.0; the issue is. When I run the following query;

    Code:
    "SELECT * FROM jos_virtuemart_products INNER JOIN jos_virtuemart_product_categories ON jos_virtuemart_products.virtuemart_product_id=jos_virtuemart_product_categories.virtuemart_product_id INNER JOIN jos_virtuemart_product_customfields ON jos_virtuemart_products.virtuemart_product_id=jos_virtuemart_product_customfields.virtuemart_product_id WHERE virtuemart_category_id = '27' AND custom_value BETWEEN '0' AND '.3' OR custom_value BETWEEN '.3' AND '.6' OR custom_value BETWEEN '.6' AND '.9'"
    The results are not refined by the category ID, it is still resulting in products form other categories.

    My first assumption is that it has something to do with the succeeding clauses. (category_id = 27 AND custom_value BETWEEN 0 AND 3 OR...).

    Is there any way to give the virtuemart_category_id = '27' clause dominance over the others? Or could I run a query preceding the other clauses so that they are only entries from the category of 27? Like creating a temporary table including all products from category with id of 27 and filter down from here?

    This is fairly urgent; any assistance will be much appreciated.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,857
    Thanks
    78
    Thanked 4,417 Times in 4,382 Posts
    Code:
    SELECT * 
    FROM jos_virtuemart_products INNER JOIN jos_virtuemart_product_categories 
    ON jos_virtuemart_products.virtuemart_product_id=jos_virtuemart_product_categories.virtuemart_product_id 
    INNER JOIN jos_virtuemart_product_customfields 
    ON jos_virtuemart_products.virtuemart_product_id=jos_virtuemart_product_customfields.virtuemart_product_id 
    WHERE virtuemart_category_id = '27' 
    AND custom_value BETWEEN '0' AND '.3' 
    OR custom_value BETWEEN '.3' AND '.6' 
    OR custom_value BETWEEN '.6' AND '.9'
    Harken back to your 5th or 6th grade math class.

    What is the value of
    Code:
         3 + 4 * 5
    ???

    If you answered 35, go back to school.

    Multiplication has higher priority than addition, so the correct answer is 23. You multiply first, then add.

    If you wanted the 35 answers, you would have written
    Code:
       ( 3 + 4 ) * 5
    Same thing with AND and OR operators, in all computer languages.

    AND has a higher precedence than OR.

    So when you do
    Code:
    WHERE virtuemart_category_id = '27' 
    AND custom_value BETWEEN '0' AND '.3' 
    OR custom_value BETWEEN '.3' AND '.6' 
    OR custom_value BETWEEN '.6' AND '.9'
    you are *REALLY* writing
    Code:
    WHERE ( virtuemart_category_id = '27' AND custom_value BETWEEN '0' AND '.3' )
    OR custom_value BETWEEN '.3' AND '.6' 
    OR custom_value BETWEEN '.6' AND '.9'
    so if custom_value matches either of those LAST TWO conditions, the first AND condition is ignored.

    Now try
    Code:
    WHERE virtuemart_category_id = '27' 
    AND ( custom_value BETWEEN '0' AND '.3' 
          OR custom_value BETWEEN '.3' AND '.6' 
          OR custom_value BETWEEN '.6' AND '.9' )
    Not to ask a dumb question, but what is the point of the three BETWEEN tests???

    BETWEEN is *inclusive* so you could have accomplished the same thing with
    Code:
    WHERE virtuemart_category_id = '27' 
    AND custom_value BETWEEN '0' AND '.9'
    and never had to worry about the OR conditions.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Dxmxgxd (11-29-2011)

  • #3
    New to the CF scene
    Join Date
    Nov 2011
    Location
    Sunshine Coast; Australia
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you very much; I hadn't realized that the addition of brackets within a query would isolate them from the other clauses.

    The reason I have used three where clauses for the custom_value is because users can select any of four ranges (0-.3, .3-.6, .6-.9, .9<).

    And because they can select any of the four ranges there are 4! > 24 possible permutations; so that's either one really long switch statement or numerous where clauses. I'm more than happy to take suggestions on the range thing; just a way to dynamically create a range without causing issues. Though one initial problem will be if a user selects 0-.3 .3-.6 and .9<;

    Simplifying down to 0-.6 and .9<;
    Multiple where clauses again.

    Thankyou very much for your assistance with my syntax errors though!

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,857
    Thanks
    78
    Thanked 4,417 Times in 4,382 Posts
    Naw, leave it alone. The OR conditions will be fast enough. No real point insimplifying given what you said.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •