how to fetch category name from category table by one of 2 category ids in customer table

Question

Having customer and category table. There are category1_id and category2_id in customer table. If having @customer, how to fetch the category name by given @customer and linking the category1_id and category2_id with the id in category table?

customer schema:

  create_table "customers", :force => true do |t|
    t.string   "name"
    t.string   "short_name"
    t.string   "contact"
    t.string   "address"
    t.string   "country"
    t.string   "phone"
    t.string   "fax"
    t.string   "email"
    t.string   "cell"
    t.integer  "sales_id"
    t.string   "web"
    t.integer  "category1_id"
    t.integer  "category2_id"
    t.boolean  "active",         :default => true
    t.string   "biz_status"
    t.integer  "input_by_id"
    t.string   "quality_system"
    t.string   "employee_num"
    t.string   "revenue"
    t.text     "note"
    t.integer  "user_id"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

category schema:

  create_table "categories", :force => true do |t|
    t.string   "name"
    t.string   "description"
    t.boolean  "active",      :default => true
    t.datetime "created_at"
    t.datetime "updated_at"
  end

In routes.rb file

  resources :customers do
    resources :categories
  end

Given @customer, how to fetch category name, like @cusotmer(category1_id).category.name?

Thanks.

rdvdijk · Accepted Answer · 2011-09-23T17:00:05.300

1

You have two single belongs_to associations in your model. Like this:

    class Customer < ActiveRecord::Base
      belongs_to :category1, :class_name => "Category", :foreign_key => "category1_id"
      belongs_to :category2, :class_name => "Category", :foreign_key => "category2_id"
    end

    class Category < ActiveRecord::Base
    end

You can now use @customer.category1.name.

(edited: belongs_to, not has_one) (edited: added :foreign_key)

However, I think you are modeling a "many-to-many" relationship between customers and categories, right? Customers have multiple categories, and categories can be assigned to multiple customers. Take a look at has_and_belongs_to_many in ActiveRecord (see the guide: http://guides.rubyonrails.org/association_basics.html#the-has_and_belongs_to_many-association).

edited Sep 23 '11 at 17:00

answered Sep 22 '11 at 19:21

rdvdijk

4,400
26
30

Yes, it is the Customer has_and_belongs_to_many Category as declared in model customer.rb. I would like to put 2 categories into one customer record. thanks. – user938363 Sep 22 '11 at 21:03
Got error after changing the association: SQLite3::SQLException: no such column: categories.customer_id: SELECT "categories".* FROM "categories" WHERE "categories"."customer_id" = 1 LIMIT 1 – user938363 Sep 22 '11 at 21:08
Ah, my bad. Those should be `belongs_to` associations. However, to have `has_many_and_belongs_to` between your models, you need an extra table. See the link I provided in the answer for a good explanation. – rdvdijk Sep 23 '11 at 06:35
It works after adding :foreign_key => 'id' to both belongs_to: belongs_to :category1, :class_name ='Category', :foreign_key => 'id'. I found the following post helps: http://stackoverflow.com/questions/3393561/has-one-with-two-foreign-keys. Thanks. – user938363 Sep 23 '11 at 08:48
Found another problem: if a customer has both category1_id and categry2_id entered, then the show page will display the same category which is not the correct one (seems randomly pick up one from the category table). Here is the code in show.html.erb: <%= (at-sign)customer.category1.name %> and <%= (at-sign)customer.category2.name %>. Both line display the same category which is not correct. Is it because of the same foreign_key (id) in both has_one association? Any suggestion to solve it? Thanks. – user938363 Sep 23 '11 at 09:13
Shouldn't the `foreign_key` values be `category1_id` and `category2_id`? – rdvdijk Sep 23 '11 at 09:57
It did not work with category1_id and category2_id as foreign key. The log shows app was select categories.category1_id which does not exist in category table. Using id as foreign key works only with one category filled and does not work with both category fields are filled. – user938363 Sep 23 '11 at 15:14
I've edited my answer, this works fine for me in an isolated example. – rdvdijk Sep 23 '11 at 17:00
Yes, the answer is correct. Has to be belongs_to, instead of has_one. Thanks. – user938363 Sep 25 '11 at 03:00

how to fetch category name from category table by one of 2 category ids in customer table

1 Answers1