Multiple queries

[gvp16]

Hi, I have 4 queries I want to run on the same table which are fairly similar, is there away I can make them into 1 query using joins or sub queries?

 

so for example :

 

I would have :

SELECT * FROM products WHERE ProductID != 56305 AND (Cat1 = 'Helmets' OR Cat2 = 'Helmets' OR Cat3 = 'Helmets') AND price_tier = 1 AND (Gender LIKE '%Mens or Unisex%' OR Gender = '') GROUP BY ProductID ORDER BY RAND() LIMIT 1

SELECT * FROM products WHERE ProductID != 56305 AND (Cat1 = 'Forks' OR Cat2 = 'Forks' OR Cat3 = 'Forks') AND price_tier = 1 AND Brand = 'Affix' AND (Gender LIKE '%Mens or Unisex%' OR Gender = '') GROUP BY ProductID ORDER BY RAND() LIMIT 1

and so on for the other 2.

 

This is being using in a php application which will return 4 products, I could easily just run each query separate on the page and get the results, but 4 would mean maintaining 4 separate blocks of code which are pretty much the same, so I was wondering if it could be done in a single query and have just one block of the (the loop for the html).

 

Thanks.

 

 

 

[Psycho]

First off:

 

Your database is not normalized. I can tell because of this:

 

AND (Cat1 = 'Forks' OR Cat2 = 'Forks' OR Cat3 = 'Forks')

 

When you have a many-to-one relationship you need to have a separate table to define those relationships. You should not add a fixed number of columns to the parent record. As it is now you are limited to having only three categories and you have to do something such as what you have above, in order to get the appropriate data. If you are not going to fix the DB design, then you can at least make that more efficient by using the IN() condition

 

AND 'Forks' IN (Cat1, Cat2, Cat3)

 

Second, ORDER BY RAND() has terrible scalability and will cause performance issues. There are better solutions that you should research.

 

Third, you are using a GROUP_BY on the product_id on records from the product table. Do you really have products with the same product ID?

 

As to your specific issue, the problem is that you only want one record from each query. If your database was properly normalized this would be super-simple and could probably be done with a single query. As you have it now, it is more difficult. The best solution I can think of is to use all four queries and use UNION. Here is an example with the two queries you provided

 

SELECT * FROM products
WHERE ProductID != 56305
  AND 'Helmets' IN (Cat1, Cat2, Cat3)
  AND price_tier = 1
  AND (Gender LIKE '%Mens or Unisex%' OR Gender = '')
GROUP BY ProductID
ORDER BY RAND()
LIMIT 1

UNION

SELECT * FROM products
WHERE ProductID != 56305
  AND 'Forks' IN (Cat1, Cat2, Cat3)
  AND price_tier = 1
  AND Brand = 'Affix'
  AND (Gender LIKE '%Mens or Unisex%' OR Gender = '')
GROUP BY ProductID
ORDER BY RAND()
LIMIT 1

[gvp16]

Hi, thanks for the advice, given the choice that database would have a much more dynamic structure, having only 3 category's is not an issue (this is just a secondary database to a poorly designed system).

 

The basic idea behind this is when I am looking at product 56305, display 4 random products from the database, but with some basic logic applied, eg product 1 is from Helmets, product 2 is of a certain brand and so on... 

 

I tried doing what you suggested before posting here, and just tried it again (incase I got it wrong) and I get the following error 

 

1221 - Incorrect usage of UNION and ORDER BY

 

If I remove the order by, I only get 1 result.

 

Thanks.

Edited August 27, 2013 by gvp16

[Psycho]

Ah, if memory serves, that is because you can use ORDER BY on the entire result set from UNIONed queries. Try putting the queries in parens so the ORDER BY would only apply to the individual queries

(SELECT * FROM products
WHERE ProductID != 56305
  AND 'Helmets' IN (Cat1, Cat2, Cat3)
  AND price_tier = 1
  AND (Gender LIKE '%Mens or Unisex%' OR Gender = '')
GROUP BY ProductID
ORDER BY RAND()
LIMIT 1)

UNION

(SELECT * FROM products
WHERE ProductID != 56305
  AND 'Forks' IN (Cat1, Cat2, Cat3)
  AND price_tier = 1
  AND Brand = 'Affix'
  AND (Gender LIKE '%Mens or Unisex%' OR Gender = '')
GROUP BY ProductID
ORDER BY RAND()
LIMIT 1)

Also, as I stated, the structure not only limits you to three categories - it creates real problem in working with the data as is evident in the problem you are facing now. You could get all the data you need with a simple query if the structure was correct.

Edited August 27, 2013 by Psycho

[gvp16]

Thanks, the query runs now but I only get one result that seems to match query 1.

 

Can please you give me an example of how you would write the query (if the structure was correct)? Always looking to improve!

[Psycho]

 

SELECT *
FROM (
       SELECT *
       FROM products
       JOIN categories USING (product_id)
       WHERE category_name IN ('Helmets', 'Forks', 'Foo', 'Bar')
         AND price_tier = 1
         AND (Gender LIKE '%Mens or Unisex%' OR Gender = '')
       ORDER BY RAND()
     ) AS t
GROUP BY category_name

[gvp16]

Thanks very much, your solution worked for me. The reason I only have one result was because there was only 1 product that matched in the query.

 

Also thanks for the sample query, I would have implemented something similar if the structure was available.

 

Thanks.