{"id":1237,"date":"2022-08-30T15:14:40","date_gmt":"2022-08-30T15:14:40","guid":{"rendered":"https:\/\/unknownerror.org\/index.php\/2013\/11\/10\/rais-has_many-through-query-collection-of-common-programming-errors\/"},"modified":"2022-08-30T15:14:40","modified_gmt":"2022-08-30T15:14:40","slug":"rais-has_many-through-query-collection-of-common-programming-errors","status":"publish","type":"post","link":"https:\/\/unknownerror.org\/index.php\/2022\/08\/30\/rais-has_many-through-query-collection-of-common-programming-errors\/","title":{"rendered":"Rais has_many & through query-Collection of common programming errors"},"content":{"rendered":"

I’m using Rails 2.3 and I have 3 tables, which manages user permissions to buildings. The mygroups table is the list of groups users and buildings belong to. The mygroupsbuildings is the list of buildings with their associated group. The mygroupsusers is the list of users with their associated group.<\/p>\n

create_table \"mygroups\", :force => true do |t|\n  t.string   \"name\"\n  t.integer  \"user_id\"\n  t.string   \"user_name\"\n  t.integer  \"permissions\"\nend\n\ncreate_table \"mygroupbuildings\", :force => true do |t|\n  t.integer  \"mygroup_id\"\n  t.integer  \"building_id\"\nend\n\ncreate_table \"mygroupusers\", :force => true do |t|\n  t.integer  \"mygroup_id\"\n  t.integer  \"user_id\"\n  t.string   \"user_name\"\nend\n<\/code><\/pre>\n
I would like to create a query that finds out if a particular user_id is a member of the same group as a particular building_id.<\/p>\n
I have setup the following associations:<\/p>\n
class Mygroup < ActiveRecord::Base  \n  has_many   :mygroupusers\n  has_many   :mygroupbuildings\nend\n\nclass Mygroubuilding < ActiveRecord::Base  \n  belongs_to :mygroup\n  has_many   :mygroupusers, :through => :mygroup\nend\n\nclass Mygroupuser < ActiveRecord::Base\n  belongs_to :mygroup\n  has_many   :mygroupbuildings, :through => :mygroup\nend\n<\/code><\/pre>\n
I wrote the following query:<\/p>\n
Mygroupuser.find(:all,\n                 :include => :mygroupbuildings,\n                 :select => \"mygroupbuildings.building_id, mygroupusers.user_id\",\n                 :conditions => [\"mygroupbuildings.building_id = '#{building.id}' AND mygroupusers.user_id = '#{current_user.user_id}'\"])      \n<\/code><\/pre>\n
This give me the following error:<\/p>\n
ActiveRecord::StatementInvalid (Mysql::Error: Unknown column ‘mygroups.mygroup_id’ in ‘on clause’: SELECT mygroupusers<\/code>.id<\/code> AS t0_r0, mygroupusers<\/code>.mygroup_id<\/code> AS t0_r1, mygroupusers<\/code>.user_id<\/code> AS t0_r2, mygroupusers<\/code>.user_name<\/code> AS t0_r3, mygroupusers<\/code>.created_at<\/code> AS t0_r4, mygroupusers<\/code>.updated_at<\/code> AS t0_r5, mygroupbuildings<\/code>.id<\/code> AS t1_r0, mygroupbuildings<\/code>.mygroup_id<\/code> AS t1_r1, mygroupbuildings<\/code>.coursewalk_id<\/code> AS t1_r2, mygroupbuildings<\/code>.created_at<\/code> AS t1_r3, mygroupbuildings<\/code>.updated_at<\/code> AS t1_r4 FROM mygroupusers<\/code> LEFT OUTER JOIN mygroups<\/code> ON (mygroupusers<\/code>.id<\/code> = mygroups<\/code>.mygroup_id<\/code>) LEFT OUTER JOIN mygroupbuildings<\/code> ON (mygroupbuildings<\/code>.mygroup_id<\/code> = mygroups<\/code>.id<\/code>) WHERE (mygroupbuildings.building_id = ‘2’ AND mygroupusers.user_id = ’55’) ):<\/p>\n
Thanks,<\/p>\n
Nick,<\/p>\n
    \n
  1. \n
    This is how the query needs to be setup to return the group that contains the queried user_id and building_id:<\/p>\n
    Mygroup.find(:all,\n             :include => [:mygroupbuildings, :mygroupusers],\n             :conditions => [\"mygroupbuildings.building_id = #{building.id} AND mygroupusers.user_id = #{current_user.user_id}\"])      \n<\/code><\/pre>\n<\/li>\n<\/ol>\n
    Originally posted 2013-11-10 00:11:29. <\/small><\/p>","protected":false},"excerpt":{"rendered":"
    I’m using Rails 2.3 and I have 3 tables, which manages user permissions to buildings. The mygroups table is the list of groups users and buildings belong to. The mygroupsbuildings is the list of buildings with their associated group. The mygroupsusers is the list of users with their associated group. create_table “mygroups”, :force => true […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1237","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/1237","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=1237"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/1237\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=1237"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=1237"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=1237"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}