How to match two kind of records on a third table

Question

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.

Is there a reason you've split `Areas` into three models? Seems to me that you'd only want one `Area` model and then have Area belong_to both `:realtor` and `:user`. — settheline, Jan 14 '14 at 16:38
@settheline : Realtors and Users models are Devise models. Areas is another model used on few fonctionnalities — guilb, Jan 14 '14 at 16:43
That's fair, but I'm wondering about the these two models: `AreasRealtor`, `AreasUser`. Those seem to be adding unneeded complexity to your associations. Are they necessary or could you do what I suggested above and only have one `Area` model? You'd still have your separate Devise models for `Realtor` and `User`. — settheline, Jan 14 '14 at 16:48
@settheline i've the 2 models because each realtor and users have many areas — guilb, Jan 14 '14 at 16:52
Right, but are they the same areas? From your associations it looks like they are....If that's the case, you don't need those two models. You only need one `Area` model. — settheline, Jan 14 '14 at 17:23
but how associate many areas to many users and many realtors ? — guilb, Jan 14 '14 at 17:32
`User has_many :areas`, `Realtor has_many :areas`, `Area belongs_to :user` and `Area belongs_to :realtor` — settheline, Jan 14 '14 at 17:46

score 1 · Answer 1 · answered Jan 14 '14 at 17:03

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)

No,it want works. I need "links" on Realtor to Areas – guilb Jan 14 '14 at 17:05 — guilb, Jan 14 '14 at 17:05

score 1 · Accepted Answer · answered Jan 14 '14 at 17:07

1

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.

answered Jan 14 '14 at 17:07

davidb

8,884
4
36
72

I like this solution it seems work but it doesnt work with will_paginate (.paginate(:page => params[:page])... Do you have an idea of solution ? – guilb Jan 14 '14 at 17:32
it works now ! i found the solution on this page : http://stackoverflow.com/questions/4352895/ruby-on-rails-will-paginate-an-array – guilb Jan 14 '14 at 17:43

score 0 · Answer 3 · answered Jan 14 '14 at 17:06

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...

How to match two kind of records on a third table

3 Answers3