CODEWITHSUNDEEP
mysqlruby-on-railsdatabaseinner-join
pablomarti
pablomarti

Reputation: 2107

Doubts about queries in MySQL

I wanted to make a query with Rails, something like this:

filters = Filter.joins(:category_filters).where("category_id IN (?)", params[:categories]).group("filters.id")

And the MySQL statement that is making is this:

SELECT `filters`.* FROM `filters` INNER JOIN `category_filters` ON `category_filters`.`filter_id` = `filters`.`id` WHERE (category_id IN ('9,4')) GROUP BY filters.id

At first sight, this query is ok, but when I look the results, its wrong. Let me explain you.

First, this is a query to the table filters:

select * from filters;

+----+----------+-------+----------+------------+------------+
| id | name     | other | optional | created_at | updated_at |
+----+----------+-------+----------+------------+------------+
|  1 | material |     1 |        1 | NULL       | NULL       |
|  2 | abc      |     1 |        0 | NULL       | NULL       |
|  3 | xyz      |     0 |        0 | NULL       | NULL       |
|  4 | 123a     |     0 |        0 | NULL       | NULL       |
+----+----------+-------+----------+------------+------------+

Second, this is a query to the table category_filters:

select * from category_filters;

+----+-----------+-------------+------------+------------+
| id | filter_id | category_id | created_at | updated_at |
+----+-----------+-------------+------------+------------+
|  1 |         1 |           1 | NULL       | NULL       |
|  2 |         2 |           1 | NULL       | NULL       |
|  3 |         1 |           9 | NULL       | NULL       |
|  4 |         2 |           9 | NULL       | NULL       |
|  5 |         1 |           4 | NULL       | NULL       |
|  6 |         3 |           4 | NULL       | NULL       |
+----+-----------+-------------+------------+------------+

And now, the query generated by Rails (the first query):

SELECT `filters`.* FROM `filters` INNER JOIN `category_filters` ON `category_filters`.`filter_id` = `filters`.`id` WHERE (category_id IN ('9,4')) GROUP BY filters.id;

+----+----------+-------+----------+------------+------------+
| id | name     | other | optional | created_at | updated_at |
+----+----------+-------+----------+------------+------------+
|  1 | material |     1 |        1 | NULL       | NULL       |
|  2 | abc      |     1 |        0 | NULL       | NULL       |
+----+----------+-------+----------+------------+------------+

Why is this happening?

But now, this is similar query, instead of using IN I used OR, like this:

SELECT `filters`.* FROM `filters` INNER JOIN `category_filters` ON `category_filters`.`filter_id` = `filters`.`id` WHERE (category_filters.category_id=9 or category_filters.category_id=4) GROUP BY filters.id;

+----+----------+-------+----------+------------+------------+
| id | name     | other | optional | created_at | updated_at |
+----+----------+-------+----------+------------+------------+
|  1 | material |     1 |        1 | NULL       | NULL       |
|  2 | abc      |     1 |        0 | NULL       | NULL       |
|  3 | xyz      |     0 |        0 | NULL       | NULL       |
+----+----------+-------+----------+------------+------------+

What is happening?

Upvotes: 1

Views: 63

Answers (2)

Josh
Josh

Reputation: 945

If you just do 

where(:category_id => params[:categories]

Rails will create the proper SQL syntax for you

Upvotes: 2

Aditya Naidu
Aditya Naidu

Reputation: 1380

In your WHERE clause, try this (assuming category_id is a number):

category_id IN (9,4)

else this (assuming category_id is a string)

category_id IN ('9','4')

instead of this (in your original query)

category_id IN ('9,4')

Upvotes: 2

Related Questions