CODEWITHSUNDEEP
ruby-on-rails
Syntactic Fructose
Syntactic Fructose

Reputation: 20144

Rails joins doesn't return values from joined table

It must be something pretty simple i'm doing wrong. i'm trying to join 3 tables together, where group contains a location_id which references a row in location and a info_id which does the same for info.

Here's my code:

@groups = Group.joins(
    'INNER JOIN location on location.id = "group".id', 
    'INNER JOIN info on info.id = "group".id'
)

This seems to work without errors, but all i'm getting back is the column from my group table. What am I doing wrong here?

P.S. my associations are location and info belong_to group. and group has_one of location and info

Upvotes: 4

Views: 6324

Answers (4)

Sikandar Tariq
Sikandar Tariq

Reputation: 1326

Just add a select tag to select the desired columns.

Group.joins(:location, :info).select("location.*, info.*")

You may also add a where clause if needed.

Group.joins(:location, :info).select("location.*, info.*").where("locations.id = conditions.id")

Upvotes: 4

Ahmad Shoaib Joya
Ahmad Shoaib Joya

Reputation: 11

If you used this:

  Group.joins(:location, :info).select("location.*, info.*").where("locations.id = 
  conditions.id")

and nothing changed

It is because the column names of the two tables are the same.

Upvotes: 0

Ziyan Junaideen
Ziyan Junaideen

Reputation: 3310

In Rails / AR we have includes and joins.

You are using joins. As the SQL shows, it only selects Group. You use joins when you need Group results but also want to query through Location and Info.

If you would use all information, which I suspect is your case, like to display details in a table, you should also use includes.

# @groups = Groups.includes(:locations, :info) results an left outer join
@groups = Groups.joins(:locations, :info).includes(:locations, :info) # results inner join

Now when you do some thing like this it will not make additional db calls. But if you use joins it use multiple queries (N+1).

- @groups.each do |group|
  tr
    td = group.id
    td = group.location.lat
    td = group.info.description

Use the bullet gem to find if you have such N+1 queries to optimize your project.

If you Google Rails includes vs joins you will find more information on the topic.

Upvotes: 1

kiddorails
kiddorails

Reputation: 13014

When you say that you have location_id and info_id in groups table, this means that you intend to have belongs_to association in Group.

Pre-requisite: Group model should have:

belongs_to :location
belongs_to :info

Location and Info should have :

has_one :group

For your question, this is expected. In ActiveRecord, Relation will return the object of the invoker, in this case Group.

For your use case, I think you will need this approach to get the right join working:

Query:

@groups = Group.joins(:location, :info)
# "SELECT `groups`.* FROM `groups` INNER JOIN `locations` ON `locations`.`id` = `groups`.`location_id` INNER JOIN `infos` ON `infos`.`id` = `groups`.`info_id`"

After this, you can iterate on each group to get info and location as something like: @groups.map { |group| [group.location, group.info] } This will give correct location and info.

Optimization: [group.location, group.info] will make queries again to get location and info. You can optimize this by changing the original query to include location and info data:

@groups = Group.joins(:location, :info).includes(:location, :info)

Upvotes: 2

Related Questions