CODEWITHSUNDEEP
sqlruby-on-railspostgresqljoin
TechnoA
TechnoA

Reputation: 75

Rails SQL Join two tables, One table has two columns with ids of other table, I need to get names of these ids

This is my model with associations:

class ProjectLineThreshold < ActiveRecord::Base
  belongs_to :project_line
  belongs_to :source, class_name: 'Language'
  belongs_to :target, class_name: 'Language'
end

ProjectLineThreshold table has these columns (:id, :source_id, :target_id, :score). I need to add names of the languages by source_id and target_id from languages table.

I came up with this statement :

thresholds = self.project_line_thresholds.joins(:source, :target)
    .select('project_line_thresholds.id, project_line_thresholds.source_id, project_line_thresholds.target_id,
      project_line_thresholds.score, languages.name as source_name, languages.name as target_name')

But I get the same names for target and source. What would be the proper join statement, or I am doing it wrong?

Upvotes: 0

Views: 1142

Answers (3)

ilan berci
ilan berci

Reputation: 3881

The following query will only hit the db once:

self.project_line_thresholds
  .joins(:source, :target)
  .includes(:source, :target)
  .map {|plt| [plt.id, plt.source_id, plt.target_id, plt.score, source.name, target.name]}

Upvotes: 1

Ruby Racer
Ruby Racer

Reputation: 5740

You need a statement only if you want to eager load your data, ie run one sql query for everything. Answers for this have already been provided.

Otherwise, you may just use the association methods provided by active record, for example:

@project_line_threshold.source.name
@project_line_threshold.target.name

Upvotes: 1

infused
infused

Reputation: 24337

You don't need the select statement, just get the names through the associations:

ProjectLineThreshold.includes(:source, :target).each do |plt|
  puts "Source name: #{plt.source.name}"
  puts "Target name: #{plt.target.name}"
end

Note that includes just makes sure to preload the associated records, otherwise it would run separate queries to retrieve source and target during each iteration of the loop.

Upvotes: 2

Related Questions