I have the following tables create table files(id int ai pk, name varchar(255) create table admins (file_id int, user_id int) create table users (id int ai pk, name varchar(16),email varchar(255)) I want to get all the admin user names and emails of a given file. Say for file 1 Admin.where(:file_id=>1).includes(:user). it works very good. Now I want to sort the admins on the usernames Admin.where(:file_id=>1).include(:user).order(''users.name''). this shows an sql query with an empty column and so bails out. here''s the query I got on the console SELECT `admins`.`` AS t0_r0, `admins`.`file_id` AS t0_r1, `admins`.`user_id` AS t0_r2, `users`.`id` AS t1_r0, `users`.`name` AS t1_r1 FROM `admins` LEFT OUTER JOIN `users` ON `users`.`id` `admins`.`user_id` WHERE `admins`.`file_id` = 1 ORDER BY users.name The first column name is being taken as empty (immediately after the select). Is this a bug? I don''t want to have id field in the admins table as that''s not useful. How do I write a AR query to sort out this issue thanks Kiran -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Rob Biedenharn
2011-Sep-30 04:49 UTC
Re: includes from a table that does not have an id field
On Sep 29, 2011, at 8:56 PM, maskiran wrote:> I have the following tables > > create table files(id int ai pk, name varchar(255) > create table admins (file_id int, user_id int) > create table users (id int ai pk, name varchar(16),email varchar(255))And presumably classes like: class File has_many :admins has_many :users, :through => :admins end class Admin belongs_to :file belongs_to :user end class User end> > I want to get all the admin user names and emails of a given file. Say > for file 1File.find(1).users or File.where(:id => 1).users> > Admin.where(:file_id=>1).includes(:user). it works very good. Now I > want to sort the admins on the usernamesThen add and .order(''users.name'') to the query. Alternatively, you could always get the users in that order: class File has_many :admins has_many :users, :through => :admins, :order => ''users.name'' end> > Admin.where(:file_id=>1).include(:user).order(''users.name''). this > shows an sql query with an empty column and so bails out. here''s the > query I got on the console > > SELECT `admins`.`` AS t0_r0, `admins`.`file_id` AS t0_r1, > `admins`.`user_id` AS t0_r2, `users`.`id` AS t1_r0, `users`.`name` AS > t1_r1 FROM `admins` LEFT OUTER JOIN `users` ON `users`.`id` > `admins`.`user_id` WHERE `admins`.`file_id` = 1 ORDER BY users.name > > The first column name is being taken as empty (immediately after the > select). > > Is this a bug? I don''t want to have id field in the admins table as > that''s not useful. How do I write a AR query to sort out this issue > > thanks > KiranYou should not need an `id` on the admins table, but you almost certainly want to have an index on each of the `file_id` and `user_id` columns. -Rob Rob Biedenharn Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org http://AgileConsultingLLC.com/ rab-/VpnD74mH8+00s0LW7PaslaTQe2KTcn/@public.gmane.org http://GaslightSoftware.com/ -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Michael Pavling
2011-Sep-30 05:33 UTC
Re: includes from a table that does not have an id field
On 30 September 2011 05:49, Rob Biedenharn <Rob-GBZH0y1GwQfnZcttdmLDtcI/UQi/AW5J@public.gmane.org> wrote:> class File > has_many :admins > has_many :users, :through => :admins > end > class Admin > belongs_to :file > belongs_to :user > end > class User > end > You should not need an `id` on the admins table, but you almost certainly > want to have an index on each of the `file_id` and `user_id` columns.Are you sure? I''ve not got time to test it right now, but I was under the impression you would need an id for the admins table unless the model described it as the join table in a habtm? class File has_and_belongs_to_many :users, :join_table => "admins" end class User has_and_belongs_to_many :files, :join_table => "admins" end File.first.users etc -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Robert Walker
2011-Sep-30 13:48 UTC
Re: includes from a table that does not have an id field
Rob Biedenharn wrote in post #1024309:> You should not need an `id` on the admins table, but you almost > certainly want to have an index on each of the `file_id` and `user_id` > columns.I assume you meant an unique index across both file_id and user_id. In essence using the two foreign keys as the primary key of the join table. It would not be sufficient to prevent duplicate records in the join table with separate indexes on each foreign key. file_id (PK1, FK1) user_id (PK2, FK2) However, IMO it is still best to let ActiveRecord have its unique surrogate primary key. But, that does not preclude the need for a unique index spanning the two foreign key columns. Which ends up looking something like this: add_index(:admin, [:file_id, :user_id], :unique => true) In many cases it also does matter which column is listed first in the index. Try to guess which of the two keys will be the most "selective" once the database is filled with data. Will users tend to have a lot of files associated them them, or will files have lots of users? That depends on how your tables are actually going to be used. List first the column that will tend to produce the fewest number of rows in the result, for the majority of your queries. Here are two examples that I think illustrate join tables well: This first example is the typical style of join tables used by Rails. There is actually a good reason for this. In an Object Relational Mapping (ORM) environment it''s really not a bad idea to let the model classes maintain record identity. That allows ActiveRecord to use its own built-in mechanism for mapping object instances to database rows. Note: In order to conform to AR default naming replace relation_id with id. CREATE TABLE posts_tags ( relation_id int UNSIGNED NOT NULL AUTO_INCREMENT, post_id int UNSIGNED NOT NULL, tag_id int UNSIGNED NOT NULL, PRIMARY KEY(relation_id), UNIQUE INDEX(post_id, tag_id) ); Here is a more traditional table definition. This is what I choose when not working within an ORM environment (rarely these days): CREATE TABLE posts_tags ( post_id int UNSIGNED NOT NULL, tag_id int UNSIGNED NOT NULL, PRIMARY KEY(post_id, tag_id) ); These examples were take from: http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/ -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.