I have one table called customers and one orders. In orders i have
customer_id. How do I write a find that can search for a customer email.
This is my own trial that fails:
@orders = Order.find(:all, :joins => “INNER JOIN customers AS c ON
customers.id=customer_id”, :conditions => [“id LIKE ? OR c.email LIKE ?
OR items LIKE ?”,searchstring,searchstring,searchstring], :order => ‘id
DESC’)
How should it be?
Pål Bergström wrote:
I have one table called customers and one orders. In orders i have
customer_id. How do I write a find that can search for a customer email.
This is my own trial that fails:
@orders = Order.find(:all, :joins => “INNER JOIN customers AS c ON
customers.id=customer_id”, :conditions => [“id LIKE ? OR c.email LIKE ?
OR items LIKE ?”,searchstring,searchstring,searchstring], :order => ‘id
DESC’)
How should it be?
Can you explain the requirements a bit more? eg
“given an email address, find all customer records whose email matches
that and then get their associated orders”
If the above is the case then i would do
@customers = Customer.find(:all, :conditions => [“email like ?”,
searchstring], :include => [:orders])
Now you can iterate through the @customers, listing each customer’s
orders. If you wanted just orders and nothing else you could do
@customers = Customer.find(:all, :conditions => [“email like ?”,
searchstring], :include => [:orders]).collect(&:orders).flatten
Can you explain the requirements a bit more? eg
“given an email address, find all customer records whose email matches
that and then get their associated orders”
If the above is the case then i would do
@customers = Customer.find(:all, :conditions => [“email like ?”,
searchstring], :include => [:orders])
Now you can iterate through the @customers, listing each customer’s
orders. If you wanted just orders and nothing else you could do
@customers = Customer.find(:all, :conditions => [“email like ?”,
searchstring], :include => [:orders]).collect(&:orders).flatten
I don’t fully understand. But what about searching in the table orders
using join, with the customer_id column in orders, and being able to
search the related customers table for the customer email?
Max W. wrote:
actually i forgot to put the wildcards in the like term, (i always do
that)
@customers = Customer.find(:all, :conditions => [“email like ?”,
“%#{searchstring}%”], :include => [:orders])
But this will not let me search for id or other info in orders, right?
Don’t I need to do a join?
actually i forgot to put the wildcards in the like term, (i always do
that)
@customers = Customer.find(:all, :conditions => [“email like ?”,
“%#{searchstring}%”], :include => [:orders])
Max W. wrote:
Well, this is why i said
“Can you explain the requirements a bit more?”
Well, the table orders have a relationship with customers through the
customer_id in orders. The search is for finding orders. So how do I
write a Order.find() that can find things in order e.g. the id, but also
the email or first_name of the customer in order?
Well, this is why i said
“Can you explain the requirements a bit more?”
@customers = Customer.find(:all, :include => [:orders], :conditions =>
[“customers.email like ? or orders.id = ?”, “%#{searchstring}%”,
searchstring])
So, we do a ‘like’ wildcard search for emails but i personally don’t
think it makes sense to do a wildcard search for ids. If someone’s
putting an id in you should match only that exact id.
Max W. wrote:
@customers = Customer.find(:all, :include => [:orders], :conditions =>
[“customers.email like ? or orders.id = ?”, “%#{searchstring}%”,
searchstring])
So, we do a ‘like’ wildcard search for emails but i personally don’t
think it makes sense to do a wildcard search for ids. If someone’s
putting an id in you should match only that exact id.
I’ll try this. Thanks for your help 
Max W. wrote:
@customers = Customer.find(:all, :include => [:orders], :conditions =>
[“customers.email like ? or orders.id = ?”, “%#{searchstring}%”,
searchstring])
So, we do a ‘like’ wildcard search for emails but i personally don’t
think it makes sense to do a wildcard search for ids. If someone’s
putting an id in you should match only that exact id.
Can I do the other way around, Order.find(:all, :include =>
[:customer]…)? When I try I get “Mysql::Error: Column ‘id’ in order
clause is ambiguous”.
Pål Bergström wrote:
Max W. wrote:
@customers = Customer.find(:all, :include => [:orders], :conditions =>
[“customers.email like ? or orders.id = ?”, “%#{searchstring}%”,
searchstring])
So, we do a ‘like’ wildcard search for emails but i personally don’t
think it makes sense to do a wildcard search for ids. If someone’s
putting an id in you should match only that exact id.
Can I do the other way around, Order.find(:all, :include =>
[:customer]…)? When I try I get “Mysql::Error: Column ‘id’ in order
clause is ambiguous”.
Actually yeah, i forgot we were still starting with customers, this
would be better
@orders = Order.find(:all, :include => [:customers], :conditions =>
[“customers.email like ? or orders.id = ?”, “%#{searchstring}%”,
searchstring])
which is pretty much what you started out with, but with include instead
of join.
Pål Bergström wrote:
Max W. wrote:
Actually yeah, i forgot we were still starting with customers, this
would be better
@orders = Order.find(:all, :include => [:customers], :conditions =>
[“customers.email like ? or orders.id = ?”, “%#{searchstring}%”,
searchstring])
which is pretty much what you started out with, but with include instead
of join.
Perfect. Thanks.
actually it should be :include => [:customer] not customers (since and
order belongs_to customer)