thr3ads.net - Rails - generating a mysql call across table [Nov 2007]

If this information is useful, please help other people find it:
Share via:

anne001

2007-Nov-01 12:07 UTC

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


--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Frederick Cheung

2007-Nov-01 12:23 UTC

head link

Re: generating a mysql call across table

On 1 Nov 2007, at 12:07, anne001 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

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

--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

wentwj-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org

2007-Nov-01 12:59 UTC

head link

Re: generating a mysql call across table

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:> 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

--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

anne001

2007-Nov-01 13:07 UTC

head link

Re: generating a mysql call across table

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"


--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Frederick Cheung

2007-Nov-01 13:23 UTC

head link

Re: generating a mysql call across table

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

--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

anne001

2007-Nov-01 13:31 UTC

head link

Re: generating a mysql call across table

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


--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

anne001

2007-Nov-01 13:45 UTC

head link

Re: generating a mysql call across table

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 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Frederick Cheung

2007-Nov-01 13:47 UTC

head link

Re: generating a mysql call across table

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

--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

anne001

2007-Nov-01 14:06 UTC

head link

Re: generating a mysql call across table

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 |
+------------+----------+-------+


--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

joe jeet

2007-Nov-01 14:20 UTC

head link

Re: generating a mysql call across table

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 <anne-qSp0wFDrGuS+fmr0zi+kZQ@public.gmane.org> 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.
--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

anne001

2007-Nov-01 14:35 UTC

head link

Re: generating a mysql call across table

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


--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

anne001

2007-Nov-02 12:53 UTC

head link

Re: generating a mysql call across table

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!


--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@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
-~----------~----~----~----~------~----~------~--~---

Rails - Nov 2007 - generating a mysql call across table

generating a mysql call across table

Re: generating a mysql call across table

Re: generating a mysql call across table

Re: generating a mysql call across table

Re: generating a mysql call across table

Re: generating a mysql call across table

Re: generating a mysql call across table

Re: generating a mysql call across table

Re: generating a mysql call across table

Re: generating a mysql call across table

Re: generating a mysql call across table

Re: generating a mysql call across table