CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecordruby-on-rails-3.1
Mellon
Mellon

Reputation: 38902

Newbie: Rails' way to query database in my case

I am using Ruby v1.8 and Rails v2.3.

I have a two model objects: Cars and Customers,

Model Cars:

class car < ActiveRecord::Base

  #car has attribute :town_code

  has_many :customers

end

Model Customers:

class customer < ActiveRecord::Base
  # customer has attribute :first_name, :last_name
  belongs_to :car
end

Now in my controller, I got a string from VIEW, and the received string has the format firstname.lastname@town_code, for example a string like "John.smith@ac01" which can be parsed as first_name="John", last_name="smith" and town_code="ac01"

Now I would like use the Rails's way to query the database to find all the customer objects (match the above conditions) from Customers table which has :

first_name="John",

last_name="smith"

and owned a car(by car_id) with car's town_code="ac01".

what is Rails' syntax to query this?

I know it should be something like (if I wanna count the nr of matched customer):

Customer.count :consitions =>{:first_name => "John", :last_name=>"smith"...}

But, I am not sure how to refer to a customer that has a referenced car with car's town_code= "ac01" ?

------------------ My question --------------------

I want to have two queries:

-one is used to count the number of matching customers,

-the other query returns the customers objects like find_by_ query.

What is the syntax in Ruby on Rails for the two queries?

Upvotes: 0

Views: 1953

Answers (3)

HectorMalot
HectorMalot

Reputation: 1120

I believe you could do something like this: source

Customer.find(:all, :include => :car, :conditions => "customers.first_name = 'John' AND customers.last_name = 'Smith' AND cars.town_code = 'ac01'")

Counting all customers with a specification can be achieved by this command: source

Customer.count(:all, :include => :car, :conditions => "customers.first_name = 'John' AND customers.last_name = 'Smith' AND cars.town_code = 'ac01'")

By the way, if you are in the position to choose what you work with, I would advise you to go for Rails 3. The chaining methods described by Joseph would make this kind of query a lot easier and it'll save you upgrading issues down the road. (And you tagged the question for Rails 3)

Upvotes: 0

Pedro Cori
Pedro Cori

Reputation: 2066

You could have something like:

@customers = car.where(:town_code => town_code).customers.where(:first_name => first_name, :last_name => last_name)

And then just count the results:

@customer_count = @customers.count

This assuming you parsed your string into the variables town_code, first_name, and last_name, like you said.

Edit

I don't think Rails v2.3 supports these chains of Active Record queries because I believe it lacks lazy loading from DB. I'm not completely sure. Also, I realize my first suggestion would't work because there could be many cars with the same town_code. I guess you could solve it using the map function like so (not tested):

@customers = car.all(:conditions => {:town_code => town_code}).map{ |c| c.customers.where(:first_name => first_name, :last_name => last_name) }

And then count them like before:

@customer_count = @customers.count

Upvotes: 1

Joseph Le Brech
Joseph Le Brech

Reputation: 6653

It should be something similar to

Customer.where(:firstname => "John", :last_name => "Smith").count

If you have many Customers of Car, you can do something like

Car.where(...).customers.where(...)

You should really be firing rails c to test your queries in (I might be slightly off)

Upvotes: 1

Related Questions