Is there a way to write active record which actually calls the following SQL command (without actually entering the SQL command myself)
SELECT student_id, score, type FROM event, score WHERE event.event_id = score.event_id;
where the score table has fields student_id, event_id, score, and the event table has fields event_id, date, type
Well if type was called something else you could do Score.find :all, :select => 'student_id, score, foo', :joins => 'inner join event using(event_id)
However columns called type are special in rails. You're probably going to have to set the column name used for STI to something other than type for the score table.
Fred
Also is this an association? Is event_id the primary key or the Event table? It looks to me like it probably is, which again, you might run into some issues fighting with rails as rails generally prefers a primary key of just 'id'.
Anyway, if it is I think you should be able to preload the score table using eager loading.
I think the syntax would just be
Event.find(:all, :include => :score)
and so long as you have your association defined correctly in your models that should do it. Oh I guess you'd have to also add a :select in there if you only wanted those three columns. anne001 wrote:
thank you. I replaced the variable type with the variable date and ran your suggestion.
Score.find :all, :select => 'student_id, score, date', :joins => 'inner join event using(event_id)' and it does generate something close to what I had asked with the following SQL SELECT student_id, score, date FROM score inner join event using(event_id)
I was able to extend it to get fields from 3 tables Score.find :all, :select => 'student_id, name, score, date', :joins => ['inner join event using(event_id)', 'inner join student using(student_id)'] which generates the SQL SELECT student_id, name, score, date FROM score inner join event using(event_id)inner join student using(student_id)
but if I extend it with a where clause on date, I get an error Score.find :all, :select => 'student_id, score, date', :joins => 'inner join event using(event_id)', :conditions => {:date => "1999-09-23"} Error: Unknown column 'score.date' in 'where clause' How do I add where conditions based on joined table field date?
Also what if the score table name is no longer event_id but test_id. is there a way of generating this type of SQL command SELECT student_id, score, type FROM event, score WHERE event.event_id = score.test_id and date = "1999-09-23"
but if I extend it with a where clause on date, I get an error Score.find :all, :select => 'student_id, score, date', :joins => 'inner join event using(event_id)', :conditions => {:date => "1999-09-23"} Error: Unknown column 'score.date' in 'where clause' How do I add where conditions based on joined table field date?
I think you need to use the slightly more verbose form of conditions : :conditions => ['date = ?', '1999-09-23']
Also what if the score table name is no longer event_id but test_id. is there a way of generating this type of SQL command SELECT student_id, score, type FROM event, score WHERE event.event_id = score.test_id and date = "1999-09-23"
instead of 'inner join event using(event_id)' do inner join event on event.something = score.somethind
Fred
Event.find(:all, :select => 'student_id, name, score, date', :include => :score) gives me an error Association named 'score' was not found; perhaps you misspelled it
Here are my model and associations class Student < ActiveRecord::Base set_table_name "student" set_primary_key 'student_id' has_many :scores has_many :events, :through => :scores end
class Event < ActiveRecord::Base set_table_name "event" set_primary_key "event_id" has_many :scores has_many :students, :through => :scores end
class Score < ActiveRecord::Base set_table_name "score" belongs_to :student belongs_to :event end
thank you
Score.find :all, :select => 'student_id, score, date', :joins => 'inner join event using(event_id)', :conditions => ['date = ?', '2004-09-03'] this worked SELECT student_id, score, date FROM score inner join event using(event_id) WHERE (date = '2004-09-03')
and this Score.find :all, :select => 'student_id, name, score, date', :joins => ['inner join event on event.event_id = score.event_id ', 'inner join student using(student_id)'] worked too SELECT student_id, name, score, date FROM score inner join event on event.event_id = score.event_id inner join student using(student_id)
thank you
You association is called scores, so you need include :scores. I believe this is changing in edge, but in the current version of rails :include overwrites any changes you make to select.
Fred
OK, then I have a problem I think because I am not using a database designed for rails the score table does not have a key, only two foreign keys student_id and event_id (like in AWDR page 337 but without the id)
Unknown column 'score.id' in 'field list': SELECT event.`event_id` AS t0_r0, event.`date` AS t0_r1, event.`category` AS t0_r2, score.`id` AS t1_r0, score.`student_id` AS t1_r1, score.`event_id` AS t1_r2, score.`score` AS t1_r3 FROM event LEFT OUTER JOIN score ON score.event_id = event.event_id
select * from score limit 1;
is this thing is not wrkng 4 u … object = Event.find_by_sql(“SELECT student_id, score, type FROM event, score WHERE event.event_id = score.event_id”)
Yes, I know you can find_by_sql
but I am learning about rails and active record and sql, using a tutorial by Dubois on mysql.
As an exercise, I was trying to figure out if there was a one to one relation between mysql and active record, ie there is an active record way of asking things that generate various basic mysql commands, but I could not find how to talk active record in a way that would generate this particular mysql code.
anne
I created a new score table that has a ROR primary key id and checked
Event.find(:all, :select => 'student_id, name, score, date', :include => :scores)
SELECT event.`event_id` AS t0_r0, event.`date` AS t0_r1, event.`category` AS t0_r2, rscore.`id` AS t1_r0, rscore.`student_id` AS t1_r1, rscore.`event_id` AS t1_r2, rscore.`score` AS t1_r3 FROM event LEFT OUTER JOIN rscore ON rscore.event_id = event.event_id e[0m
so far Rails lists multiple tables in FROM with a Join, but now => since all Active record does is parse text and put together mysql bits and pieces I can write
Score.find :all, :select => 'student_id, score, date', :joins => [ ', event'], :conditions => 'event.event_id = rscore.event_id' to get SELECT student_id, score, date FROM rscore , event WHERE (event.event_id = rscore.event_id)
this generalizes to three tables Score.find :all, :select => 'student.student_id, student.name, rscore.score, event.date', :joins => [', event , student'], :conditions => 'event.event_id = rscore.event_id and student.student_id = rscore.student_id' ie SELECT student.student_id, student.name, rscore.score, event.date FROM rscore , event , student WHERE (event.event_id = rscore.event_id and student.student_id = rscore.student_id)
My conclusion is most of the time I might as well write Mysql directly indeed!