CODEWITHSUNDEEP
ruby-on-railsactiverecord
jpw
jpw

Reputation: 19247

rails 3, how to do this easy query across two tables?

I think this is a trivial query but it eludes me...

person has_many items
item has_many keywords

Item has fields id, person_id, name

Keyword has fields id, item_id, txt

In my Person controller:

def find_all_items_matching_keyword(aword)
   ????
end

is there a single query that returns all the items where (item.person_id = self.id) and the associated (keyword.txt = aword)

Upvotes: 0

Views: 435

Answers (3)

Pan Thomakos
Pan Thomakos

Reputation: 34340

This query is simple enough that you should build it up with a single scope like this:

class Item
  scope :by_keyword, lambda{ |keyword| joins(:keywords).where('keywords.txt = ?', keyword) }
end

You can then query all items, in your controller, for a particular person like this:

class PersonsController
  def action
    @person = Person.find(params[:id])
    @items_by_keyword = @person.items.by_keyword(params[:aword])
  end
end

You can then loop over that list in your view, for example:

<% @items.by_keyword.each do |item| %>
  <%= item.name %>
<% end %>

Upvotes: 2

Zimbabao
Zimbabao

Reputation: 8240

You code should look something like

def find_all_items_matching_keyword(aword)
   return Item.joins(:keyword).where("keywords.txt=':keyword'",{:keyword => aword })
end

and it should go in helpers (thats its correct place).

Upvotes: 2

Joseph Le Brech
Joseph Le Brech

Reputation: 6653

you can use find_by_sql http://guides.rubyonrails.org/active_record_querying.html#finding-by-sql

if you want to be database agnostic, you should be able to do 

items.keywords.each do |keyword| {
   #do stuff
}

activerecord should lazy load associated models for you.

Upvotes: 1

Related Questions