distinctively difficult to do what I want

I'm going around in circles with this one can't solve it.

I have a database with a lot of Name objects in it, some have the same
given name - ok fairly normal.

Name
  given (string)
  gender (string) # it's either m or f, not really relevant
  position (integer) # not really relevant
  user_id (integer) # it's either m or f, not really relevant

I simply wish to return all the Names with a unique given name.
*important* I an array of objects returned not just the given names so I
can access all the attributes of the name when they're returned, i.e. in
my view I need the full object to get at the user and gender etc.

I think the secret lies in distinct / uniq / find_by_sql somehow but try
as I might I just can't find the secret sauce. For a bonus burrito I'd
like the results ordered by given name.

Be very grateful for a solution.

If I understand correctly, then in the case where there are several
records with given == "fred" for example, then you want to get a Name
object back so you can access the other attributes. But how will the
code know which of the records with given == "fred" that you want?
The other attributes may be different between the fred records.

Colin

+1
You might be satisfied with ".group_by(&:given)" on a collection of
all the Name objects, which would give you a list of unique names,
while still giving access to all the individual records. But this
could be slow if you have a large collection.

If I understand correctly, then in the case where there are several
records with given == "fred" for example, then you want to get a Name
object back so you can access the other attributes. But how will the
code know which of the records with given == "fred" that you want?
The other attributes may be different between the fred records.

Ah - yes, that's right you've understood.
Hmmm - you've got me thinking differently about this now.
I think the thing I was trying to do is not what I actually should do!

Thanks for the reply, I'm thinking now I'll return just the unique names
and the gender of, I think that'll serve my purpose.

OK

Thanks - I've had another think about it but I'd still appreciate any
insight if possible. I had wondered if my data model is wrong but I
don't think so, it's rather simple.

Users have names and Names belong to users. It has to be like this as
Users can individually sort their names (so they have a position
attribute), therefore while the name Fred might be in the database
several times, it's a different record each time and needs to be as it's
"owned" by different users who may have it as a different position in
their respective lists - I think it's ok to have it as I have it, but it
seems to make collecting my stats a little more difficult.

Here's another way of looking at it, the output/result I need.

Given the schema above I need the result of the query/scope/find_all_by
to look something like this.

If we've got records like

Freddy
Freddy
Mary
Rod
Rod
Rod
Jane

I need a method to return all this so I have access to it in my
controller and then view.

Rod (3, male)
Freddy (2, male)
Jane (1, female)
Mary (1, female)

So that's unique and with this order..

1. By occurences (most common first)
2. By name in alpha order

I need access to the name and gender attributes as well as the count
(which clearly is not an attribute).

What would be the best way for this to be returned even (an array? a
hash?)

The end game is so that I can easily query my data sets and work out the
10 most popular names across the users.

At a later date I'd also love to be able to move on from this and find
stuff that's "trending", for example what's changing position the most
in a given time period or what's most popular this week, last week, last
month, last year. All that stuff and variants thereof.

Thanks for your help so far and most grateful for any further insight.

OK

Thanks - I've had another think about it but I'd still appreciate any
insight if possible. I had wondered if my data model is wrong but I
don't think so, it's rather simple.

Users have names and Names belong to users. It has to be like this as
Users can individually sort their names (so they have a position
attribute), therefore while the name Fred might be in the database
several times, it's a different record each time and needs to be as it's
"owned" by different users who may have it as a different position in
their respective lists - I think it's ok to have it as I have it, but it
seems to make collecting my stats a little more difficult.

Here's another way of looking at it, the output/result I need.

Given the schema above I need the result of the query/scope/find_all_by
to look something like this.

If we've got records like

Freddy
Freddy
Mary
Rod
Rod
Rod
Jane

I need a method to return all this so I have access to it in my
controller and then view.

Rod (3, male)

A couple of questions
Are all the Rod records identical except for which user they belong to?

Freddy (2, male)

What if one of the Freddys is female? Maybe not likely with this name
but there are names that can be both.

Colin Law wrote in post #1052938:

"owned" by different users who may have it as a different position in
Freddy
Rod (3, male)

A couple of questions
Are all the Rod records identical except for which user they belong to?

No they're not - for example one user might have rod as position 3 and
another user might love the name and have it as the number 1 choice.

Freddy (2, male)

What if one of the Freddys is female? Maybe not likely with this name
but there are names that can be both.

True - in my system the user simply designates a name as either M or F,
gender is an attribute of name...quite possible to have the same name as
both M and F and rightly so, actually current schema attached in case it
helps.

Attachments:
http://www.ruby-forum.com/attachment/7181/schema.rb

Colin Law wrote in post #1052938:

"owned" by different users who may have it as a different position in
Freddy
Rod (3, male)

A couple of questions
Are all the Rod records identical except for which user they belong to?

No they're not - for example one user might have rod as position 3 and
another user might love the name and have it as the number 1 choice.

Freddy (2, male)

What if one of the Freddys is female? Maybe not likely with this name
but there are names that can be both.

True - in my system the user simply designates a name as either M or F,
gender is an attribute of name...quite possible to have the same name as
both M and F and rightly so, actually current schema attached in case it
helps.

In that case your problem is more complex as you need to differentiate
between the m and f Freddys, presumably. So in your example you would
need Freddy(1, female) and Freddy(1,male)

Colin

I think you would be better to have a join table, user_names maybe. Then have
User
has_many :user_names
has_many :names, :through => :user_names

UserName
belongs_to :user
belongs_to :name

Name
has_many :user_names
has_many :users, :through => :user_names

Have given and gender in names and the position in user_names

I think that might help a lot. For example to find how many users
have a name you can then just use @name.users.count.

Colin

Colin Law wrote in post #1052973:

Freddy (2, male)

What if one of the Freddys is female? Maybe not likely with this name
but there are names that can be both.

True - in my system the user simply designates a name as either M or F,
gender is an attribute of name...quite possible to have the same name as
both M and F and rightly so, actually current schema attached in case it
helps.

I think you would be better to have a join table, user_names maybe.
Then have
User
has_many :user_names
has_many :names, :through => :user_names

UserName
belongs_to :user
belongs_to :name

Name
has_many :user_names
has_many :users, :through => :user_names

Have given and gender in names and the position in user_names

I think that might help a lot. For example to find how many users
have a name you can then just use @name.users.count.

Colin

Thanks for taking the time Colin, that's thought provoking in several
ways.

1) You're right the ability to do simply @name.users.count would be
excellent.

2) I've got an existing schema in place and am wondering if I can
migrate to this new schema (perhaps with some additional migrations and
rake tasks) or maybe that's a very tricky task.

3) Linked to two, think carefully about the data model before starting
out.

I don't think what I've got is wrong it's just that @name.users.count
and the like seems like the most elegant way to handle the queries I
need to be doing.

The idea of a join table scares me a bit but I get where you're coming
from.

Colin Law wrote in post #1052973:

Freddy (2, male)

What if one of the Freddys is female? Maybe not likely with this name
but there are names that can be both.

True - in my system the user simply designates a name as either M or F,
gender is an attribute of name...quite possible to have the same name as
both M and F and rightly so, actually current schema attached in case it
helps.

I think you would be better to have a join table, user_names maybe.
Then have
User
has_many :user_names
has_many :names, :through => :user_names

UserName
belongs_to :user
belongs_to :name

Name
has_many :user_names
has_many :users, :through => :user_names

Have given and gender in names and the position in user_names

I think that might help a lot. For example to find how many users
have a name you can then just use @name.users.count.

Colin

Thanks for taking the time Colin, that's thought provoking in several
ways.

1) You're right the ability to do simply @name.users.count would be
excellent.

2) I've got an existing schema in place and am wondering if I can
migrate to this new schema (perhaps with some additional migrations and
rake tasks) or maybe that's a very tricky task.

Never be afraid to refactor if you find a better way to do something.
You should always have good automated test coverage so you can be
reasonably confident that you have not broken anything. I hope you
are using a version control system (such as git) so that if you decide
it is all a disaster then you can easily go back to where you were.

3) Linked to two, think carefully about the data model before starting
out.

Don't spend /too/ much time thinking about it beforehand, you can
always refactor it later.

I don't think what I've got is wrong it's just that @name.users.count
and the like seems like the most elegant way to handle the queries I
need to be doing.

In that case what you have got is wrong in the sense that something
else would be better.

The idea of a join table scares me a bit but I get where you're coming
from.

If you are going to get anywhere with Rails then you will need to know
how to use join tables so take this opportunity to learn.

Colin

Thanks Colin,

I'm giving this a go but running into problems.

Is there a problem perhaps with using the join table name of UsersNames
or UserNames as it might clash with the User.username attribute.

I'll post my code later - just wondering does the name of the join table
matter at all ?

I wrote a data migration script, I'll add that also later.

Schema attached..

model summaries...

class Name < ActiveRecord::Base

  attr_accessible :given, :gender, :position

  has_many :user_names
  has_many :users, :through => :user_names

class UserNames < ActiveRecord::Base
  belongs_to :user
  belongs_to :name

class User < ActiveRecord::Base
  # Include default devise modules. Others available are:
  # :token_authenticatable, :encryptable, :confirmable, :lockable,
:timeoutable and :omniauthable
  devise :database_authenticatable, :registerable,
         :recoverable, :rememberable, :trackable, :validatable

  has_many :user_names
  has_many :names, :through => :user_names

DATA MIGRATION..

class CreateUserNames < ActiveRecord::Migration
  def change
    create_table :user_names do |t|
      t.integer :name_id
      t.integer :user_id
      t.integer :position

      t.timestamps
    end
  end
end

then

class RemoveUserIdFromName < ActiveRecord::Migration
  def up
    remove_column :names, :user_id
  end

  def down
    add_column :names, :user_id, :integer
  end
end

DATA MOVER SCRIPT...

require 'rubygems'

User.all.each do |u|

  names = Name.find_all_by_user_id(u.id)

  names.each do |n|
    un = UserNames.new( :user_id => u.id, :name_id => n.id, :position =>
n.position )
    un.save
  end

end

So with all that in place and data mover run console gives me errors...

ruby-1.9.2-p290 :006 > User.first.names
  User Load (1.4ms) SELECT "users".* FROM "users" ORDER BY
lower(username) ASC LIMIT 1
NameError: uninitialized constant User::UserName

(other error out cut)

What am I doing wrong ?
Do my model relationships and data mover script look ok?

Attachments:
http://www.ruby-forum.com/attachment/7202/schema.rb

can anyone shed any light on this...

two questions.

1) do the new relationships via the join table look wrong (hence the
console error)
2) does my data_mover script look OK?

What is that ORDER BY lower(username) doing? I don't see where that
is coming from in your code.

What happens if you do User.first

Colin

Colin Law wrote in post #1054363:

devise :database_authenticatable, :registerable,
NameError: uninitialized constant User::UserName

What is that ORDER BY lower(username) doing? I don't see where that
is coming from in your code.

What happens if you do User.first

Colin

In the User model I have...
default_scope order('lower(username) ASC')

however commenting it out didn't help much.

User.first is/was fine...

  User Load (0.6ms) SELECT "users".* FROM "users" LIMIT 1
=> #<User id: 34, email: "s.e.... bla bla

User.first.names

ruby-1.9.2-p290 :008 > User.first.names
  User Load (0.6ms) SELECT "users".* FROM "users" LIMIT 1
NameError: uninitialized constant User::UserName
  from
/Users/rupert/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.2/lib/active_record/inheritance.rb:119:in
`compute_type'
  from
/Users/rupert/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.2/lib/active_record/reflection.rb:172:in
`klass'
  from
/Users/rupert/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.2/lib/active_record/reflection.rb:385:in
`block in source_reflection'
  from
/Users/rupert/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.2/lib/active_record/reflection.rb:385:in
`collect'
  from
/Users/rupert/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.2/lib/active_record/reflection.rb:385:in
`source_reflection'
  from
/Users/rupert/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.2/lib/active_record/reflection.rb:508:in
`check_validity!'
  from
/Users/rupert/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.2/lib/active_record/associations/association.rb:26:in
`initialize'
  from
/Users/rupert/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.2/lib/active_record/associations/collection_association.rb:24:in
`initialize'
  from
/Users/rupert/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.2/lib/active_record/associations/has_many_through_association.rb:10:in
`initialize'
  from
/Users/rupert/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.2/lib/active_record/associations.rb:157:in
`new'
  from
/Users/rupert/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.2/lib/active_record/associations.rb:157:in
`association'
  from
/Users/rupert/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.2/lib/active_record/associations/builder/association.rb:44:in
`block in define_readers'
  from (irb):8
  from
/Users/rupert/.rvm/gems/ruby-1.9.2-p290/gems/railties-3.2.2/lib/rails/commands/console.rb:47:in
`start'
  from
/Users/rupert/.rvm/gems/ruby-1.9.2-p290/gems/railties-3.2.2/lib/rails/commands/console.rb:8:in
`start'
  from
/Users/rupert/.rvm/gems/ruby-1.9.2-p290/gems/railties-3.2.2/lib/rails/commands.rb:41:in
`<top (required)>'
  from script/rails:6:in `require'
  from script/rails:6:in `<main>'ruby-1.9.2-p290 :009 >

I see looking back at your code that you have
class UserNames < ActiveRecord::Base
That should be UserName (singular) in user_name.rb with table name user_names

Colin

Looking after the children but I think you got it - thanks :slight_smile:

two changes...

model filename from user_names.rb to user_name.rb

and as you say

class UserName < ActiveRecord::Base

gives...

ruby-1.9.2-p290 :005 > User.last.names
  User Load (0.8ms) SELECT "users".* FROM "users" ORDER BY "users"."id"
DESC LIMIT 1
  Name Load (1.0ms) SELECT "names".* FROM "names" INNER JOIN
"user_names" ON "names"."id" = "user_names"."name_id" WHERE
"user_names"."user_id" = 85
=> [#<Name id: 519, given: "Jeremiah", position: 1, gender: "m",
created_at: "2012-03-25 17:21:52", updated_at: "2012-03-25 17:21:52">]
ruby-1.9.2-p290 :006 >

(first user had no names)

lovely thanks.

drat another road block...

So now I wish to create Names and I can't fiond the secret sauce.

my old create action was simply a variation on the normal..something
like this..

def create
  @name = Name.new(params[:name])

  respond_to do |format|
    if @name.save
    else
    end
  end

end

How do I do it so it takes account of the new joins model...

Is it something like...

if @name.save && user_name.create(some attribs in here, e.g.
current_user.id)

Stuck. Again.