Rails Query based on hasManyBelongsToMany relation

Question

I have the following models

venues(id, name, ....)
categories(id, name, ....)
categories_venues(id, venue_id, category_id)

I want to query venues based which have the relation of Categories ID = (1)/(2) or (1 and 2)

How can i do a where query for the relation of a HABTM?

EDIT

Using the following query it solved the problem.

venues = Venue.joins(:categories_venues).where(:categories_venues => {:category_id => values})

I want to exclude repetitive records from the query and a particular ID from the results?

---
- !ruby/object:Venue
  attributes:
    id: 2
    name: Ooty
    slug: ooty
    created_at: 2014-06-21 22:10:52.000000000 Z
    updated_at: 2014-07-05 17:33:26.000000000 Z
- !ruby/object:Venue
  attributes:
    id: 4
    name: Munnar
    slug: munnar
    created_at: 2014-08-25 03:42:13.000000000 Z
    updated_at: 2014-08-25 03:42:13.000000000 Z
- !ruby/object:Venue
  attributes:
    id: 2
    name: Ooty
    slug: ooty
    created_at: 2014-06-21 22:10:52.000000000 Z
    updated_at: 2014-07-05 17:33:26.000000000 Z

dre-hh · Answer 1 · 2014-08-25T12:00:50.163

2

Not sure if understanding you right. Plz provide sql code if you think this answer does not provide the right query But i think, it should be just

 Venue.joins(:categories).where(category_id: [1,2])

edited Aug 25 '14 at 12:00

answered Aug 25 '14 at 06:43

dre-hh

7,840
2
33
44

there is a join table in between which is a HABTM – Harsha M V Aug 25 '14 at 06:45
yeah, the over solution ist better actually as it save an extra join – dre-hh Aug 25 '14 at 12:01

score 1 · Accepted Answer · answered Aug 25 '14 at 06:37

1

You can use below query to get venue details based on category

venues = Venue.joins(:categories_venues).where(:categories_venues => {:category_id => values})

answered Aug 25 '14 at 06:37

Icicle

1,174
9
13

can i pass the values as an array? – Harsha M V Aug 25 '14 at 06:43
Yes you can pass array as well venues = Venue.joins(:categories_venues).where(:categories_venues => {:category_id => [1,2]}) – Icicle Aug 25 '14 at 07:28
is there a way to exclude one of the IDs from the query result. since i am trying to find similar results based on category – Harsha M V Aug 25 '14 at 08:48
is there a way to removed repetive records like the Distinct result set. have edited the question – Harsha M V Aug 25 '14 at 08:52
You can do by using uniq method Venue.joins(:categories_venues).where(:categories_venues => {:category_id => [1,2]}).uniq – Icicle Aug 25 '14 at 09:29
Uniq is method in Array class which returns distinct element from your ActiveRecord result array – Icicle Aug 25 '14 at 10:44

Rails Query based on hasManyBelongsToMany relation

2 Answers2