I need to select 1 record from each client. This record has the charge
with the most severe charge type (lowest charge_type_id). How do I go
about this?
I have been using a find_by_sql query out of the client.rb that looks
like:
SELECT c.f_name AS f_name, c.l_name AS l_name, c.gender AS gender,
c.race AS race, c.dob AS dob, c.address AS address, c.city AS
city,
c.state AS state, c.zipcode AS zipcode, m.date_of_arrest AS
date_of_arrest,
ch.name AS charge_name, t.classification AS charge_type,
t.classification_level AS charge_level, t.max_fine AS max_fine,
t.min_jail_time AS min_jail, t.max_jail_time AS max_jail
FROM clients AS c
LEFT JOIN matters AS m ON c.id = m.client_id
LEFT JOIN charges AS ch ON m.charge_id = ch.id
LEFT JOIN charge_types AS t ON ch.charge_type_id = t.id
This query retrieves all records for each client. I only want 1 record
per client.
My models:
class Client < ActiveRecord::Base
has_and_belongs_to_many :charges, :join_table => ‘matters’
…
end
class Charge < ActiveRecord::Base
belongs_to :charge_type
has_and_belongs_to_many :clients, :join_table => ‘matters’
end
class ChargeType < ActiveRecord::Base
has_many :charges
end
My schema:
create_table “charge_types”, :force => true do |t|
t.column “classification_level”, :string, :limit => 40, :default =>
“”, :null => false
t.column “classification”, :string, :limit => 15, :default => “”,
:null => false
t.column “max_fine”, :string, :limit => 10
t.column “min_jail_time”, :string, :limit => 10
t.column “max_jail_time”, :string, :limit => 10
t.column “jail_type”, :string, :limit => 20
end
create_table “charges”, :force => true do |t|
t.column “charge_type_id”, :integer, :limit => 10, :default => 0,
:null => false
t.column “name”, :string, :limit => 100, :default => “”, :null =>
false
t.column “charge_id_number”, :string, :limit => 15, :default => “”,
:null => false
end
add_index “charges”, [“charge_type_id”], :name => “charges_FKIndex2”
create_table “clients”, :force => true do |t|
t.column “person_id”, :string, :limit => 7
t.column “f_name”, :string, :limit => 20
t.column “l_name”, :string, :limit => 20, :default => “”, :null =>
false
t.column “gender”, :text
t.column “race”, :text
t.column “dob”, :date
t.column “address”, :text
t.column “city”, :text
t.column “state”, :text
t.column “zipcode”, :text
t.column “active”, :integer, :limit => 3
end
add_index “clients”, [“person_id”], :name => “person_id”, :unique =>
true
create_table “matters”, :id => false, :force => true do |t|
t.column “charge_id”, :integer, :limit => 10, :default => 0, :null
=> false
t.column “client_id”, :integer, :limit => 10, :default => 0, :null
=> false
t.column “cause_number”, :string, :limit => 20, :default => “”,
:null => false
t.column “date_of_arrest”, :date, :null => false
t.column “imported_at”, :datetime
end
add_index “matters”, [“client_id”], :name => “matters_FKIndex1”
add_index “matters”, [“charge_id”], :name => “matters_FKIndex2”