Hi, Working with legacy DB here... 2 tables: Projects Workers A worker record has many fields in it to hold project codes as in: project1, project2, project3, ... project30 (great, isn''t it?...) Normalizing the DB is out of the question. I''ve been thinking about this for a while and I can''t find a way to associate both tables as in: "Project has_many :workers" "Worker.project1 belongs_to :project" "Worker.project2 belongs_to :project" etc. Any ideas? Pepe -- 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.
you need has_and_belongs_to_many http://railsbrain.com/api/rails-2.3.2/doc/index.html?a=M001888&name=has_and_belongs_to_many On Tue, Feb 23, 2010 at 11:32 AM, pepe <Pepe-gUAqH5+0sKL6V6G2DxALlg@public.gmane.org> wrote:> Hi, > > Working with legacy DB here... > > 2 tables: > Projects > Workers > > A worker record has many fields in it to hold project codes as in: > project1, project2, project3, ... project30 (great, isn''t it?...) > > Normalizing the DB is out of the question. > > I''ve been thinking about this for a while and I can''t find a way to > associate both tables as in: > > "Project has_many :workers" > "Worker.project1 belongs_to :project" > "Worker.project2 belongs_to :project" > etc. > > Any ideas? > > Pepe > > -- > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > >-- 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.
Thanks Ben, but I don''t believe so. As I said in the OP, the table is ''de-normalized''. I don''t have 1 record per worker and project to be able to associate the tables the usual way. I have many fields holding project code values in the same record, obviously all of them with different names. In other words, I have many fields to "hook into" for the association to happen, not just one neat field I can associate with. On Feb 23, 2:40 pm, ben wiseley <wisel...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> you need has_and_belongs_to_manyhttp://railsbrain.com/api/rails-2.3.2/doc/index.html?a=M001888&name=h... > > On Tue, Feb 23, 2010 at 11:32 AM, pepe <P...-gUAqH5+0sKL6V6G2DxALlg@public.gmane.org> wrote: > > Hi, > > > Working with legacy DB here... > > > 2 tables: > > Projects > > Workers > > > A worker record has many fields in it to hold project codes as in: > > project1, project2, project3, ... project30 (great, isn''t it?...) > > > Normalizing the DB is out of the question. > > > I''ve been thinking about this for a while and I can''t find a way to > > associate both tables as in: > > > "Project has_many :workers" > > "Worker.project1 belongs_to :project" > > "Worker.project2 belongs_to :project" > > etc. > > > Any ideas? > > > Pepe > > > -- > > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > > . > > For more options, visit this group at > >http://groups.google.com/group/rubyonrails-talk?hl=en.-- 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.
On 23 February 2010 19:40, ben wiseley <wiseleyb-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> you need > has_and_belongs_to_many http://railsbrain.com/api/rails-2.3.2/doc/index.html?a=M001888&name=has_and_belongs_to_manyHow, precisely will a join-table help, when he says that "Normalizing the DB is out of the question"? :-/ On 23 February 2010 19:32, pepe <Pepe-gUAqH5+0sKL6V6G2DxALlg@public.gmane.org> wrote:> Hi, > > Working with legacy DB here... > > 2 tables: > Projects > Workers > > A worker record has many fields in it to hold project codes as in: > project1, project2, project3, ... project30 (great, isn''t it?...) > > Normalizing the DB is out of the question. > > I''ve been thinking about this for a while and I can''t find a way to > associate both tables as in: > > "Project has_many :workers" > "Worker.project1 belongs_to :project" > "Worker.project2 belongs_to :project" > etc. >You are highly limited given your inability to re-structure the database, and I wonder whether you want to reconsider negotiation with whatever powers that be to refactor the database a little (or a lot! ;-) But working with the tables you have, you can at least use AR as much as possible... # worker model belongs_to :project1, :class_name => "Project", :foreign_key => "project1_foreign_key_name_in_workers_table" belongs_to :project2, :class_name => "Project", :foreign_key => "project2_foreign_key_name_in_workers_table" belongs_to :project3, :class_name => "Project", :foreign_key => "project3_foreign_key_name_in_workers_table" The project model is going to be harder, and you''re not going to be able to intuitively assign workers to projects, but you can at least fudge-together some helper methods to make it look like you''re leveraging the power of Rails! :-) #project model has_many :project1_keyed_workers, :class_name => "Worker", :foreign_key => "project1_foreign_key_name_in_workers_table" has_many :project2_keyed_workers, :class_name => "Worker", :foreign_key => "project2_foreign_key_name_in_workers_table" has_many :project3_keyed_workers, :class_name => "Worker", :foreign_key => "project3_foreign_key_name_in_workers_table" def workers # build and array of all the arrays of workers, flatten it and get rid of nil values ([] << project1_keyed_workers << project2_keyed_workers << project3_keyed_workers).flatten.compact end Hope this helps.... Michael -- 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.
On 23 February 2010 19:52, Michael Pavling <pavling-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> has_many :project1_keyed_workers, :class_name => "Worker", > :foreign_key => "project1_foreign_key_name_in_workers_table" > has_many :project2_keyed_workers, :class_name => "Worker", > :foreign_key => "project2_foreign_key_name_in_workers_table" > has_many :project3_keyed_workers, :class_name => "Worker", > :foreign_key => "project3_foreign_key_name_in_workers_table"whoa! that doesn''t look very legible once posted. If you can''t make sense of my code snippet, let me know and I''ll post a Gist link. -- 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.
On 23 February 2010 19:54, Michael Pavling <pavling-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> whoa! that doesn''t look very legible once posted. > If you can''t make sense of my code snippet, let me know and I''ll post > a Gist link.In fact, I might as well do that anyway: http://gist.github.com/312634 -- 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.
Thanks a lot Michael for your explanation and efforts. :)
I had thought about going the way you propose but it seemed to me like
too much work (I think the total amount of columns I would need to
write code for is 32) for what I actually need. If there is an easy
way of making the association work both ways I surely would like to
know, however, while you were trying to help me I think I found a neat
solution for my current need, which is not having the association work
both ways but accessing the project information for a given worker.
The problem I''m trying to solve is a little bit more complicated than
I originally posted. I am not really dealing with ID values. The
Projects table has an ID and also a project code, which is also
supposed to be unique "when it has a value" (this is enforced at
application level). The project ID is not what is stored in the
''project'' columns in the Worker record, it is the project code
instead.
The solution I have so far works quite well and uses virtual
attirbutes (below). The first virtual attribute is a list of all
project codes actually stored in the record. If a ''project*''
column is
empty it does not provide a value to the list. The second one is the
projects information based on those project codes the first virtual
attribute provides.
class Worker < ActiveRecord::Base
attr_reader :project_codes, :projects
def project_codes
self.attributes.delete_if {|key, value| key.downcase !~ /project/
|| value.blank? }.values.map{|v| v.strip}.uniq
end
def projects
Project.find_all_by_code self.project_codes
end
end
On Feb 23, 2:56 pm, Michael Pavling
<pavl...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> On 23 February 2010 19:54, Michael Pavling
<pavl...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > whoa! that doesn''t look very legible once posted.
> > If you can''t make sense of my code snippet, let me know and
I''ll post
> > a Gist link.
>
> In fact, I might as well do that anyway:http://gist.github.com/312634
--
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.
On 23 February 2010 20:20, pepe <Pepe-gUAqH5+0sKL6V6G2DxALlg@public.gmane.org> wrote:> Thanks a lot Michael for your explanation and efforts. :) > > I had thought about going the way you propose but it seemed to me like > too much work (I think the total amount of columns I would need to > write code for is 32) for what I actually need. > The > Projects table has an ID and also a project code, which is also > supposed to be unique "when it has a value" (this is enforced at > application level). The project ID is not what is stored in the > ''project'' columns in the Worker record, it is the project code > instead.Well, the first thing you have to do is creep up behind the original developer of that table with your Louisville Slugger in hand... But seriously, whoever produced that table should be fired. If my plumber connected the taps that badly I''d get wet every time I change channel on the TV. [1]> If there is an easy > way of making the association work both ways I surely would like to > knowGiven your table structure, I think you''ve done well to get as much use out of it as you have; your "projects" method is another little fudge I might have considered. You might get something else working (both ways with a little hassle) using named scopes rather than associations; defining them (all 32+!) in a loop to be DRY, and concatenating their results like I suggested with the associations. But it''s a lot of hard work, and not really what Rails is best at. Honestly, I think you''d be better off spending the time scouring the job sites for a new employer; somewhere where databases are free to enjoy the benefits of friendly ORMs :-) Good luck, Michael [1] If it was *you* that created the current tables, but "years ago, before you knew what you were doing".... well, it''s fair enough that you''re trying to sort it out now... as long as you''re suitably apologetic ;-) -- 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, If it were me who had created that monstrosity I would have apologized in advance, believe me. :) Funny enough, I might very probably be on the phone talking to the "owner of the beast" in a few minutes and believe it or not, instead of starting to keep things ''sane'' and add additional information in new tables he has already suggested adding a brand new set of 32 columns for the related values we need to store now! :D The sad thing is that the way things are now we might just need to put up with it! This guy is obviously on his way out and the application will be rewritten and the DB redesigned at some point in the near future. The owner of the application is trying to find the "path of least resistance" right now to make things work so we can meet the deadlines we have and start working on the replacement software asap so we don''t need have this other person involved any longer. I''ll remember to bring my Louisville Slugger if I ever meet him in person. ;) On Feb 23, 3:38 pm, Michael Pavling <pavl...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 23 February 2010 20:20, pepe <P...-gUAqH5+0sKL6V6G2DxALlg@public.gmane.org> wrote: > > > Thanks a lot Michael for your explanation and efforts. :) > > > I had thought about going the way you propose but it seemed to me like > > too much work (I think the total amount of columns I would need to > > write code for is 32) for what I actually need. > > The > > Projects table has an ID and also a project code, which is also > > supposed to be unique "when it has a value" (this is enforced at > > application level). The project ID is not what is stored in the > > ''project'' columns in the Worker record, it is the project code > > instead. > > Well, the first thing you have to do is creep up behind the original > developer of that table with your Louisville Slugger in hand... > > But seriously, whoever produced that table should be fired. If my > plumber connected the taps that badly I''d get wet every time I change > channel on the TV. [1] > > > If there is an easy > > way of making the association work both ways I surely would like to > > know > > Given your table structure, I think you''ve done well to get as much > use out of it as you have; your "projects" method is another little > fudge I might have considered. > You might get something else working (both ways with a little hassle) > using named scopes rather than associations; defining them (all 32+!) > in a loop to be DRY, and concatenating their results like I suggested > with the associations. But it''s a lot of hard work, and not really > what Rails is best at. > > Honestly, I think you''d be better off spending the time scouring the > job sites for a new employer; somewhere where databases are free to > enjoy the benefits of friendly ORMs :-) > > Good luck, > Michael > > [1] If it was *you* that created the current tables, but "years ago, > before you knew what you were doing".... well, it''s fair enough that > you''re trying to sort it out now... as long as you''re suitably > apologetic ;-)-- 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.