Topic: find with has_many :through

I have a model Person and one Organisation. A Person can belong to any number of Organisations and an Organisation can have any number of Persons attributed to it. As below:

class Person < ActiveRecord::Base   
  has_many :person_organisations, :dependent => true
  has_many :organisations, :through => :person_organisations
end

class Organisation < ActiveRecord::Base
  has_many :person_organisations, :dependent => true
  has_many :persons, :through => :person_organisations   
end


I'm writing an action on my Person controller list_by_organisation which will return all the contacts from a specified organisation. How do I achieve this?

Do I do something like the following? But what goes in the condition?

  def list_by_organisation
    @person_pages, @persons = paginate(:persons, :per_page => 20, :order => 'id', :conditions => [???])
    render_action 'list'
  end

If I'm doing this in completely the wrong way then please do let me know.

Thanks for any suggestions.

Last edited by mip (2006-11-24 08:42:48)

Re: find with has_many :through

Try this:

paginate(:persons, :per_page => 20, :order => 'persons.id', :include => :organisations, :conditions => ['organisations.id=?', params[:organisation_id]])

Last edited by ryanb (2006-11-24 11:51:35)

Railscasts - Free Ruby on Rails Screencasts

Re: find with has_many :through

ryanb wrote:

Try this:

paginate(:persons, :per_page => 20, :order => 'persons.id', :include => :organisations, :conditions => ['organisations.id=?', params[:organisation_id]])

Cheers ryan. I though I'd be able to do something like that.

Unfortunately I'm getting this error:

Execute
    OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
      Incorrect syntax near the keyword 'DISTINCT'.
    HRESULT error code:0x80020009
      Exception occurred.

When I try the SQL which is being generated directly on the database it works without error.

Last edited by mip (2006-11-24 12:04:57)

Re: find with has_many :through

Hmm, that's weird. No clue what could be the problem if the same line works outside of Rails.

If nothing else, you can try separating the find from the pagination. There are various tutorials on this throughout the net (check Rails wiki). I can probably make up some code if you need it.

Railscasts - Free Ruby on Rails Screencasts

Re: find with has_many :through

ryanb wrote:

If nothing else, you can try separating the find from the pagination. There are various tutorials on this throughout the net (check Rails wiki). I can probably make up some code if you need it.

Using this method:

  def list
    @person_pages = Paginator.new self, Person.count, 10, params[:page]
    @people = Person.find :all, :order => 'last_name, first_name',
                          :limit  =>  @person_pages.items_per_page,
                          :offset =>  @person_pages.current.offset
  end

?

I'll give it a go.

Re: find with has_many :through

Hmm. If I try:

     
@person_pages = Paginator.new self, Person.count, 20, params[:page]
@persons = Person.find :all, :include => 'organisations', :conditions => ['organisations.id=?', params[:id]],
  :order => 'persons.id',
  :limit  =>  @person_pages.items_per_page,
  :offset =>  @person_pages.current.offset

I get the same error:

Execute
    OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
      Incorrect syntax near the keyword 'DISTINCT'.
    HRESULT error code:0x80020009
      Exception occurred.

Re: find with has_many :through

Try this maybe:

@persons = Organisation.find(params[:id]).persons.find(:all, :limit => @person_pages.items_per_page, :offset => @person_pages.current.offset)

That might cause the same problem though. I don't really know the solution because I think it's specific to MS SQL Server.

Railscasts - Free Ruby on Rails Screencasts

Re: find with has_many :through

This seems to work:

    @person_pages = Paginator.new self, Person.count, 20, params[:page]
    @people = Organisation.find(params[:id]).people

Last edited by mip (2006-11-27 06:01:51)

Re: find with has_many :through

mip wrote:

This seems to work:

    @person_pages = Paginator.new self, Person.count, 20, params[:page]
    @people = Organisation.find(params[:id]).people

Except of course that Person.count isn't the correct number of entries in @people smile

Last edited by mip (2006-11-27 10:23:42)