I'm still learning all the cool things about ActiveRecord. I'm
currently stuck on figuring out how how to make this SQL query into
something that uses some rails magic.
# Current situation is I have two tables
Entries
Prizes
class Entry < ActiveRecord::Base
belongs_to :prize
end
class Prize < ActiveRecord::Base
belongs_to :entry
end
My logic is that each entry ID has a matching prize ID. If you were to
look up by prize.id you could find the winning entry using the same
id. So if you were to do this in MYSQL it would look like this and you
would get both table rows returned.
# Current MySQL
SELECT * FROM prizes LEFT JOIN entries ON prizes.id=entries.id WHERE
prizes.id="2"
What I'm looking to do is I want the equivalent of the above but
returning active-record objects, so I could do some edits and save to
both tables.
I'm still learning all the cool things about ActiveRecord. I'm
currently stuck on figuring out how how to make this SQL query into
something that uses some rails magic.
# Current situation is I have two tables
Entries
Prizes
class Entry < ActiveRecord::Base
belongs_to :prize
end
class Prize < ActiveRecord::Base
belongs_to :entry
end
My logic is that each entry ID has a matching prize ID. If you were to
look up by prize.id you could find the winning entry using the same
id. So if you were to do this in MYSQL it would look like this and you
would get both table rows returned.
This isn't what activerecord is expecting. belongs_to :prize tells
active record that you entries table has a prize_id column containing
the id of something in the prizes table (and similarly for entry). You
probably could get this to sort of work like this with a :foreign_key
=> 'id' option, but expect things to go very wonky if you try to
delete the associated object, assign to the association etc... More
generally, if id is an autoincrement column (which rails rather
assumes) isn't this going to be a source of endless pain?
Okay. I see your point. So I'm guessing maybe I should create a tables
called "entries_prizes" to keep track of which entries have a prize
associated with them? In the end I just want to be able to query which
entries have a prize associated with them BUT by looking it up using
the entry ID. I hope I'm making sense, I think I'm making this harder
then it needs to be.
I think you are. You haven't made clear the relationship between entries and prizes. You only really need a join table if entries can have several prizes and prizes can have multiple entries.
The way it works is as a Entry.new gets created, an ID gets created
with the Entry, let's say ID="2". When the entry is about to be saved
it does a lookup on the prizes tables (which are prepopulated) for the
same corresponding ID="2". If it finds the same ID in the prizes table
we have a winner. It's easy to return the row using MySQL.
#MySQL example
SELECT * FROM prizes LEFT JOIN entries ON prizes.id=entries.id WHERE
prizes.id="2"
But I'm trying to do the same thing using Active record and I thought
this example (see below) would work.
The way it works is as a Entry.new gets created, an ID gets created
with the Entry, let's say ID="2". When the entry is about to be saved
it does a lookup on the prizes tables (which are prepopulated) for the
same corresponding ID="2". If it finds the same ID in the prizes table
we have a winner. It's easy to return the row using MySQL.
#MySQL example
SELECT * FROM prizes LEFT JOIN entries ON prizes.id=entries.id WHERE
prizes.id="2"
But I'm trying to do the same thing using Active record and I thought
this example (see below) would work.
Like i said, that's really not what rails is expecting
belongs_to :entry, :foreign_key => 'id'
might work but it probably opens up the door for a lot of trouble. The
rails way is to have a prize_id column.