CODEWITHSUNDEEP
mysqlruby-on-railsruby-on-rails-3activerecord
guilb
guilb

Reputation: 123

How to match two kind of records on a third table

I've 5 tables :

**areas**
id
otherfields

**realtors**
id
otherfields

**users**
id
otherfields

**areas_realtors**
area_id
realtor_id

**areas_users**
area_id
user_id

My relation in models are :

class Area < ActiveRecord::Base
  has_and_belongs_to_many :users
  has_and_belongs_to_many :realtors
end

class AreasRealtor < ActiveRecord::Base
  belongs_to :realtor
  belongs_to :area
end

class AreasUser < ActiveRecord::Base
  belongs_to :user
  belongs_to :area
end

class Realtor < ActiveRecord::Base
  has_and_belongs_to_many :areas
end

class User < ActiveRecord::Base
  has_and_belongs_to_many :areas
end

In my "realtor controller", i need to select all the users with common areas with the realtor.

I don't found the solution with Active Record or with "simple MySQL query"...

Thanks for advance,

F.

Upvotes: 1

Views: 51

Answers (3)

davidb
davidb

Reputation: 8954

This should do it for you,... Of cause "just" ruby solution but working.

@realtor.areas.collect(&:users).inject(&:+).uniq

The collect method returns an array including the return values of the called method users which always returns an array with the associated users. The inject method calls the + method on all the collected arrays to add them up to one array. The uniq methods removes double User objects from the array. So it returns an array including all User objects that are related to the areas associated with @relator.

Upvotes: 1

guilb
guilb

Reputation: 123

I've found an answer in SQL query : 

select * from users where id IN (select distinct user_id from areas_users where area_id IN (select `area_id` from areas_realtors WHERE realtor_id='86'))

If it's possible i would like use an activerecord syntax...

Upvotes: 0

Catfish
Catfish

Reputation: 19294

This isn't 100% complete, but I think it gets you on the right track..

It eliminates some unnecessary complexity. Basically realtor is just an extension of a user record in the user's table.

I would do something like this:

**areas**
id
otherfields

**users**
id
otherfields

**realtors**
id
user_id
otherfields

**user_areas**
area_id
user_id

My relation in models are :

class Area < ActiveRecord::Base
  has_and_belongs_to_many :users
end

class UserArea < ActiveRecord::Base
  has_many :user
  has_many :area
end

class Realtor < ActiveRecord::Base
  belongs_to :user
end

class User < ActiveRecord::Base
  has_and_belongs_to_many :areas
  has_one :realtor
end

By going with this structure, it allows you to do things like

User.areas Realtor.user.areas or if you use delegate, you can do Realtor.areas.

Then to ultimately get what you're asking for, you could just do Realtor.joins(:users).where(:area_id => 1)

Upvotes: 1

Related Questions