Forum: Ruby on Rails 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.

On 15 February 2013 10:05, Lorenz Blackbird <lists@ruby-forum.com> 
wrote:
> 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 :)

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

On Feb 15, 2013, at 10:12 AM, Colin Law wrote:

>
>
> Colin

ActiveRecord actually gives you a method for exactly this:

@course.paragraph_ids

-Rob

You probably want #pluck:

http://api.rubyonrails.org/classes/ActiveRecord/Ca...

-a.

@p_ids = @course.paragraph_ids

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

Pluck gave me the same MySQL error.

Thanks at all for the help.