distinctively difficult to do what I want

rubyonrails-talk
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.

2

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

+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

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.

6

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.

7

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

8

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

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

10

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.

11

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

12

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.

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?

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

14

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?

15

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

16

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 >

17

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

18

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.

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.