generating a mysql call across table

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!