Includes from a table that does not have an id field

Rails
1

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

2

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 1

File.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 usernames

Then 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

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 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 B.
[email protected] http://AgileConsultingLLC.com/
[email protected] http://GaslightSoftware.com/

3

On 30 September 2011 05:49, Rob B. [email protected]
wrote:

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

4

Rob B. 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: