Hi,
I have an MySQL statement as,
SELECT s.name,s.sex,p.city,s.state FROM students as s,states as p WHERE
s.state=p.state and s.id=4;
And I have ActiveRecord statements that produce the same content as,
student=Student.find(4)
state=State.find(:first, :conditions=>['state=?',student[:state]])
As you can see I have two Ruby statements for a single MySQL statement.
I know there are other ways to format the Ruby script to produce the same
results.
My question is what is the common and standard way to do this? Is there
a better ActiveRecord-way to express this ? Will it be better with
foreign-keys ? Is there a single-statement-way to express this ?
I have enclosed the database, tables, and the procedures that I used.
TIA.
O Plameras
++++++++++++++++++++++++++++++++++++++++++Shell
Script+++++++++++++++++++++++++++++++++++++++++
#!/bin/bash
mysql -u student -ptrust2Me <<MYSQL
USE students;
DROP TABLE IF EXISTS students;
CREATE TABLE students(
id INT NOT NULL AUTO_INCREMENT,
name CHAR(25),
birthday DATE,
sex CHAR(1),
state CHAR(3),
PRIMARY KEY (id)
);
INSERT INTO students(id, name,birthday,sex,state)
VALUES
(NULL,'Garbo','1975-01-24','m','NSW'),
(NULL,'Barbara','1985-04-24','f','NSW'),
(NULL,'Jose','1983-09-24','m','NSW'),
(NULL,'Zusuki','1987-12-24','m','QLD'),
(NULL,'Maria','1981-09-11','f','NSW'),
(NULL,'Dory','1980-02-24','f','VIC'),
(NULL,'Macy','2000-01-13','f','NSW'),
(NULL,'Banjo','1997-08-08','m','NSW')
;
DROP TABLE IF EXISTS states;
CREATE TABLE states(
id INT NOT NULL AUTO_INCREMENT,
state CHAR(3),
city CHAR(25),
PRIMARY KEY (id)
);
INSERT INTO states(id,state,city)
VALUES
(NULL,'NSW','Sydney'),
(NULL,'VIC','Melbourne'),
(NULL,'QLD','Brisbane'),
(NULL,'WA','Perth'),
(NULL,'SA','Adelaide'),
(NULL,'TAS','Hobart'),
(NULL,'NT','Darwin')
;
SELECT s.name,s.sex,p.city,s.state FROM students as s,states as p WHERE
s.state=p.state and s.id=4;
#
MYSQL
+++++++++++++++++++++++++++++++++++++++++Ruby
Script+++++++++++++++++++++++++++++++++++++++++++++++
#/usr/bin/env ruby
#
#
require "i"
require "c"
class Student < ActiveRecord::Base;end
class State < ActiveRecord::Base;end
#
student=Student.find(4)
state=State.find(:first,:conditions=>['state=?',student[:state]])
puts "name sex city state"
print student[:name]+" "+student[:sex]+" "+state[:city]+" "+student[:state]
puts
#
_______________________________________________
coders mailing list
[email protected]
http://lists.slug.org.au/listinfo/coders
