CODEWITHSUNDEEP
mysqlsqlruby-on-rails
Sacha
Sacha

Reputation: 75

SQL query on a JOINED table with multiple conditions

I have two sql tables: The wall table and the tag table. Each of them is linked with has_and_belongs_to_many relationship. Also the tag table has unique names.

Here are the tables in sql

mysql> describe tags;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | NO   | UNI | NULL    |                |
| count      | int(11)      | NO   |     | NULL    |                |
| created_at | datetime     | NO   |     | NULL    |                |
| updated_at | datetime     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

mysql> describe tags_walls;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| tag_id  | bigint(20) | NO   | MUL | NULL    |       |
| wall_id | bigint(20) | NO   |     | NULL    |       |
+---------+------------+------+-----+---------+-------+

mysql> describe walls;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | NO   |     | NULL    |                |
| created_at | datetime     | NO   |     | NULL    |                |
| updated_at | datetime     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

I am in rails 5 and i want to query a wall that has multiple tags.

I'm trying to do

result = Wall.all.includes(:tags).where(tags: {name: 'TAG1'})
result = result.where(tags: {name: 'TAG2'})

and the query that is constructed by rails is 

SELECT  DISTINCT `walls`.`id`
FROM `walls`
LEFT OUTER JOIN `tags_walls` ON `tags_walls`.`wall_id` = `walls`.`id`
LEFT OUTER JOIN `tags` ON `tags`.`id` = `tags_walls`.`tag_id`
WHERE `tags`.`name` = 'TAG1' AND `tags`.`name` = 'TAG2'

It should give me multiple walls as a results but the return is #<ActiveRecord::Relation []>

I want to build a custom sql query and just do a

Wall.includes(:tags).where query

How can i do a WHERE query on a joined table with multiple conditions linked by an AND ?

Upvotes: 1

Views: 66

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I would write this as:

SELECT tw.id
FROM tags_walls tw JOIN
     tags t
     ON t.id = tw.tag_id
WHERE t.name IN ('TAG1', 'TAG2')
GROUP BY tw.id
HAVING COUNT(*) = 2;

This assumes that tags are not duplicated on a wall. If that is possible, then use COUNT(DISTINCT t.name) = 2.

Notes:

  • walls is not needed, so that JOIN is removed.
  • You are looking for matches, so INNER JOIN is more appropriate than LEFT JOIN.
  • Table aliases make the query easier to write and to read.
  • Unnecessary backticks make the query harder to write and to read.

Upvotes: 1

Foxi Maxi
Foxi Maxi

Reputation: 1

WHERE tags.name = 'TAG1' OR tags.name = 'TAG2'

or

WHERE tags.name IN ('TAG1','TAG2')

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

SELECT w.id
  FROM walls w
  JOIN tags_walls tw
    ON tw.wall_id = w.id
  JOIN tags t
    ON t.id = tw.tag_id
   AND t.name IN('TAG1','TAG2')
 GROUP 
    BY w.id 
HAVING COUNT(DISTINCT t.name) = 2 -- where '2' equals the number of arguments in IN()

Upvotes: 0

Related Questions