Select and inner join

I have a many-to-many relationship in my course model in this way:

has_many :course_paragraphs has_many :paragraphs, :through => :course_paragraphs

I use an inner join to select the paragraphs of my course:

Paragraph.joins(:course_paragraphs => :course)          .where('course_paragraphs.course_id' => @course.id)

This works, but I want to select an array containing only the ids of paragraphs.

By this solution I get an error:

@p_ids = Paragraph.select(:id)                   .joins(:course_paragraphs => :course)                   .where('course_paragraphs.course_id' => @course.id)

Mysql2::Error: Column 'id' in field list is ambiguous: SELECT id FROM `paragraphs` INNER JOIN `course_paragraphs` ON `course_paragraphs`.`paragraph_id` = `paragraphs`.`id` INNER JOIN `courses` ON `courses`.`id` = `course_paragraphs`.`course_id` WHERE `course_paragraphs`.`course_id` = 1

By this solution, the array contain hexadecimal values:

@p_ids = Paragraph.select('paragrahs.id')                   .joins(:course_paragraphs => :course)                   .where('course_paragraphs.course_id' => @course.id)

Where in my problem?

Thanks to all.

You probably want #pluck:

http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i-pluck

-a.

I have a many-to-many relationship in my course model in this way:

has_many :course_paragraphs has_many :paragraphs, :through => :course_paragraphs

I use an inner join to select the paragraphs of my course:

Paragraph.joins(:course_paragraphs => :course)          .where('course_paragraphs.course_id' => @course.id)

You don't need to do that. If you have a course in @course then its paragraphs are just @course.paragraphs. It is rare to have to use joins when working with Rails if you have got the associations right.

This works, but I want to select an array containing only the ids of paragraphs.

I think this should work. @course.paragraphs.select( :id )

Colin

Colin Law wrote in post #1097096:

Paragraph.joins(:course_paragraphs => :course)          .where('course_paragraphs.course_id' => @course.id)

You don't need to do that. If you have a course in @course then its paragraphs are just @course.paragraphs. It is rare to have to use joins when working with Rails if you have got the associations right.

You're right. I tried to resolve my problem in a different way and I'm returned unintentionaly at the classic method with a long solution :slight_smile:

I think this should work. @course.paragraphs.select( :id )

Unfortunately it gave the same problem. (:id) -> MySQL error (:paragraphs => :id) -> hexadecimal values

I took the decision to keep the "hexadecimal" solution and adapt another bit of my code to have same values.

In any case, thanks for the answer.

I have a many-to-many relationship in my course model in this way:

has_many :course_paragraphs has_many :paragraphs, :through => :course_paragraphs

I use an inner join to select the paragraphs of my course:

Paragraph.joins(:course_paragraphs => :course)         .where('course_paragraphs.course_id' => @course.id)

You don't need to do that. If you have a course in @course then its paragraphs are just @course.paragraphs. It is rare to have to use joins when working with Rails if you have got the associations right.

This works, but I want to select an array containing only the ids of paragraphs.

I think this should work. @course.paragraphs.select( :id )

Colin

ActiveRecord actually gives you a method for exactly this:

@course.paragraph_ids

-Rob

@p_ids = @course.paragraph_ids

This solution works fine! Too simple for a C programmer like me :slight_smile:

Pluck gave me the same MySQL error.

Thanks at all for the help.