Generating a mysql call across table

Rails
1

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

2

On 1 Nov 2007, at 12:07, anne001 wrote:

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

3

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.

4

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”

5

On 1 Nov 2007, at 13:07, anne001 wrote:

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

6

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

7

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

8

On 1 Nov 2007, at 13:31, anne001 wrote:

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

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

9

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;
±-----------±---------±------+
| student_id | event_id | score |
±-----------±---------±------+
| 1 | 1 | 20 |
±-----------±---------±------+

10

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”)

anne001 [email protected] wrote:
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

Why delete messages? Unlimited storage is just a click away.

11

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

12

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 [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!