Distinctively difficult to do what I want

Rails
1

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 it matters, rails 3.2

Name Belongs To User
User Has Many Names

My original question is still important but I’d like to expand it… How
might I answer some of these questions in code (returning arrays of name
objects of course)

  • ten most popular boys names (unique and sorted by popularity)
  • ten most popular girls names (unique and sorted by popularity)
2

On 16 March 2012 15:41, bingo bob [email protected] wrote:

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.

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

3

On 16 March 2012 16:59, Colin L. [email protected] wrote:

code know which of the records with given == “fred” that you want?
The other attributes may be different between the fred records.

+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.

4

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.

5

On 23 March 2012 09:24, bingo bob [email protected] wrote:

“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?

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.

6

Colin L. wrote in post #1052938:

On 23 March 2012 09:24, bingo bob [email protected] wrote:

“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.

7

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.

8

On 23 March 2012 16:48, bingo bob [email protected] wrote:

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

To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

–
gplus.to/clanlaw

9

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.

10

On 23 March 2012 16:48, bingo bob [email protected] wrote:

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

11

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.

12

On 26 March 2012 11:19, bingo bob [email protected] wrote:

both M and F and rightly so, actually current schema attached in case it
belongs_to :name
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.

  1. 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

13

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?

14

On 29 March 2012 17:17, bingo bob [email protected] wrote:

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

15

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?
16

On 1 April 2012 09:34, bingo bob [email protected] wrote:

Colin

In the User model I have…
default_scope order(‘lower(username) ASC’)

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

17

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.

18

Colin L. wrote in post #1054363:

On 29 March 2012 17:17, bingo bob [email protected] wrote:

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 `'ruby-1.9.2-p290 :009 >

19

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.