Joining a "has many through" association using ActiveRecord

Question

Working with Exported Data from API

I'm building a leaderboard that displays the Team.name of each team as well as the users who have picked that particular team as their favorites. I'm also populating another attribute Favorite.points; to display the users with the most points accumulated for that respective team.

Here are the models I'm working with:

Favorite.rb

class Favorite < ActiveRecord::Base
  belongs_to :users
  belongs_to :teams
end

Team.rb

class Team < ActiveRecord::Base
  has_many :favorites
  has_many :users, :through => :favorites 
end

User.rb

class User < ActiveRecord::Base
  has_many :favorites
  has_many :teams, :through => :favorites 
end

To start this process, I'm trying to match up the id's that are common between Team.external_id and Favorite.team_id (the same is the case for User.external_id => Favorites.user_id). I can use Team.find_all_by_external_id(3333) to get the IDs of all Team objects that have an external_id of '3333'and the same goes for Favorite.find_all_by_team_id.

What's the next best step for me to obtain/show the data I'm looking for? Is a SQL join clause best? Or is it better to write if statements matching up values and iterating through the JSON arrays?

Any help is appreciated, thanks!

To anybody answering this question: you should look at this for reference: http://stackoverflow.com/questions/14374695/saving-external-json-to-db-with-rails — Chris Salzberg, Jan 19 '13 at 11:02
"To start this process, I'm trying to match up the id's that are common between Team.external_id and Favorite.id" <- I'm not exactly clear what you're trying to do here. `external_id` is the id from the API, whereas the `id` of `Favorite` records is created by your rails app, correct? — Chris Salzberg, Jan 19 '13 at 11:04
Ah wait, I think I understand. But a clarification would still help. — Chris Salzberg, Jan 19 '13 at 11:07
Hey @shioyama, to further elaborate -- Team.external_id = the unique id given to each team (from the API). This Team.external_id is the exact same id as Favorite.team_id per each team (as the model above describes their relationship). In context of the leaderboard I'm trying to build, that means each team will have it's own leaderboard with the top users (not worried about this model at the moment) who've accumulated the most points for that particular team. — asing, Jan 19 '13 at 18:47
But the `Favorite` model is specific to your rails app, correct? If so, then I don't think it really makes sense to assign the team's `external_id` to the `team_id` of `Favorite`. By default rails uses `id` associations, so `team_id` would correspond to the `id` of the `Team` model. You can always get the external id if you need it, but internally I see no need to use it for associations within your app. Does that make sense? — Chris Salzberg, Jan 20 '13 at 00:56
I posted an answer, let's move any further discussion there. — Chris Salzberg, Jan 20 '13 at 01:43

Chris Salzberg · Accepted Answer · 2013-01-20T08:47:40.343

2

This will get you all the favorites whose team_id matches the external_id attribute of a row in the teams table, for a specific team (here, the team with id 3333):

Favorite.joins("left outer join teams on teams.external_id = favorites.team_id")\
  .where('team_id' => 3333)

The tricky thing here, as I mentioned in my comments, is that you are going entirely against the grain of rails associations when you match the external id on the Team model (an attribute which you have created) to the team_id on the Favorite model (which is used throughout rails to get and assign associations).

You will see the problem as soon as you try to actually get the team for the favorite you find in the above join:

f = Favorite.joins("left outer join teams on teams.external_id = favorites.team_id")\
     .where('team_id' => 3333).first
=> #<Favorite id: 1, user_id: nil, team_id: 3333, points: nil, created_at: ... >
f.team
  Team Load (0.3ms)  SELECT "teams".* FROM "teams" WHERE "teams"."id" = 3333 LIMIT 1
=> nil

What's going on here? If you look closely at the query, you'll see that rails is selecting teams whose id is 3333. Note that it is not looking for teams whose external id is 3333, which is what you want.

The fundamental problem is that you are trying to use external ids (ids specific to your API) for associations, which won't work. And indeed, there is no reason to do it this way.

Instead, try this:

Favorite.joins(:team).where('teams.external_id = 3333')

This will get you all favorites whose teams have the external id 3333. Note that Rails will do this by joining on teams.id = favorites.team_id, then filtering by teams.external_id:

SELECT "favorites".* FROM "favorites" INNER JOIN "teams"
  ON "teams"."id" = "favorites"."team_id" WHERE (teams.external_id = 3333)

You can do the same thing the other way around:

Team.joins(:favorites).where('teams.external_id = 3333')

which will generate the SQL:

SELECT "teams".* FROM "teams" INNER JOIN "favorites"
  ON "favorites"."team_id" = "teams"."id" WHERE (teams.external_id = 3333)

Note again that it is the id that is being used in the join, not the external id. This is the right way to do this: use the conventional id for your associations, and then just filter wherever necessary by your (custom-defined, API-specific) external id.

Hope that helps!

UPDATE:

From the comments, it seems that the team_id on your Favorite model is being defined from the API data, which means that the id corresponds to the external_id of your Team model. This is a bad idea: in rails, the foreign key <model name>_id (team_id, user_id, etc.) has a specific meaning: the id is understood to map to the id field of the corresponding associated model (Team).

To get your associations to work, you need to use ids (not external ids) for associations everywhere (with your User model as well). To do this, you need to translate associations defined in the API to ids in the rails app. When you add a favorite from the API, find the Team id corresponding to the API team id.

external_team_id = ... # get external team id from API JSON data
favorite.team_id = Team.find_by_external_id(external_team_id).id

So you are assigning the id of the team with a given external id. You need to query the DB for each favorite you load from the API, which is potentially costly performance-wise, but since you only do it once it's not a big deal.

edited Jan 20 '13 at 08:47

answered Jan 20 '13 at 01:42

Chris Salzberg

27,099
4
75
82

@shoyama strikes again...thanks so much for the elaborate answer. Still getting use to the Rails/associations conventions. I have tried your example where Team does an inner join with favorites but it's giving me an empty array? – asing Jan 20 '13 at 01:55
Also, the alternative approach where Favorite joins teams gives me an "NameError uninitialized constant Favorite::Teams" – asing Jan 20 '13 at 02:17
Oh yes, forgot to mention but your `belongs_to` calls in your `Favorite` model should take singular forms, not plural forms, so `belongs_to :team` not `belongs_to :teams`, etc. Makes sense since each favorite "belongs to" just one team. – Chris Salzberg Jan 20 '13 at 02:27
Might seem like a trivial question...but are the arrays supposed to be empty after evaluating the join statements? – asing Jan 20 '13 at 03:23
`Team.joins(:favorites)` should return all teams, regardless of whether the team is associated with any favorites. Any of those teams that does have favorites associated with it should also have those favorites, so e.g. if the first team has favorites, then `Team.joins(:favorites).first.favorites` should return them. – Chris Salzberg Jan 20 '13 at 04:03
I assume the other errors disappeared when you fixed the `belongs_to` issue? It helps to mention that errors disappear before jumping to the next problem, just to make sure that we're moving in the right direction. – Chris Salzberg Jan 20 '13 at 04:05
The initial NameError was fixed when I changed 'teams' to 'team'. Now the issue is Team.joins(:favorite) is returning a blank array. Team.joins(:favorite).first returns 'nil'. – asing Jan 20 '13 at 05:34
That should be `Team.joins(:favorites)`, because one team has many favorites. – Chris Salzberg Jan 20 '13 at 05:53
Note the singular/plural thing is confusing, because while rails talks in singular/plural terms, the DB/SQL uses plural only for table names. Watch out for that. – Chris Salzberg Jan 20 '13 at 05:55
I apologize, I meant to say Team.joins(:favorites) in my original comment. Which evaluates to an empty array or Teams.joins(:favorites).empty? is equal to true. – asing Jan 20 '13 at 05:58
Well, in that case it means that you don't have any favorites with a `team_id` that matches one of the teams ids. Are you 100% sure this is not the case? I've tested all this in an app and it works for me. Remember that none of this has anything to do with `external_id` at this point, we're just dealing with `id` (column in the `teams` table) and `team_id` (column in the `favorites` table). If there are none that match, then you will get back an empty array. – Chris Salzberg Jan 20 '13 at 06:31
I apologize but I'm still confused because team_id from Favorites is also generated by the external API (same api as Team data). 'team_id' is the unique identifier from that API which is the same identifier that is generated by the api for Team.external_id and not the Team.id (this just indexes the teams per json object). Wouldn't that mean I need to somehow join Favorite.team_id and Team.external_id (the same unique identifier)? – asing Jan 20 '13 at 07:28
Ah, so you mean the associations are also defined in the API? I thought the favorites were something that you were creating in your app, on top of the API data. That complicates things. – Chris Salzberg Jan 20 '13 at 08:31
I still think though that you should stick to the rails-side id for associations. When you save the favorites to the DB, you should map the `team_id` in the API to the corresponding id of the team. – Chris Salzberg Jan 20 '13 at 08:33
Added an update. Hope this helps! I don't think I can do much more for you, you'll have to work through the rest. – Chris Salzberg Jan 20 '13 at 08:48
Again, thanks for the thorough descriptions -- you've helped tremendously. – asing Jan 20 '13 at 08:50

Joining a "has many through" association using ActiveRecord

1 Answers1