How to select distinct with an include?

patcito · December 15, 2006, 4:13am

Hey all,
I’m doing this query:

@brands=Brand.find(:all,:include=>:machines,:conditions=>[‘category_id=?’,cat.id])

I need to get all the brands name for each category, I get it through
my machines which belong to a category and to a brand. The problem is
that I get double entries in my @brands hash. I tried @brands.uniq but
this is obviously not working as there are other values than name in
my @brands hash. Any idea how I could remove all the doubles from my
@brands?

thanx in advance

Pat

patcito · December 15, 2006, 4:58am

Hi –

On Fri, 15 Dec 2006, Patrick A. wrote:

this is obviously not working as there are other values than name in
my @brands hash. Any idea how I could remove all the doubles from my
@brands?

What does the generated SQL look like? I’m not quite picturing where
the doubles are coming from.

David

–
Q. What’s a good holiday present for the serious Rails developer?
A. RUBY FOR RAILS by David A. Black (http://www.manning.com/black)
aka The Ruby book for Rails developers!
Q. Where can I get Ruby/Rails on-site training, consulting, coaching?
A. Ruby Power and Light, LLC (http://www.rubypal.com)

patcito · December 15, 2006, 7:55am

Patrick A. wrote:

Hey all,
I’m doing this query:

@brands=Brand.find(:all,:include=>:machines,:conditions=>[‘category_id=?’,cat.id])

I need to get all the brands name for each category, I get it through
my machines which belong to a category and to a brand. The problem is
that I get double entries in my @brands hash. I tried @brands.uniq but
this is obviously not working as there are other values than name in
my @brands hash. Any idea how I could remove all the doubles from my
@brands?

thanx in advance

Pat

Brand.rb
has_many :machines
has_many :categories, :through => machines

Category.rb
has_many :machines
has_many :brands, :through => machines

Machine.rb
belongs_to :brand
belongs_to :category

then you can write :
@all_brand_names = Category.find(:all).brands.name
@category_names = Brand.find(:all).categories.name
… and much more

see my cheat sheet… no, seems that posting attachment is not well
accepted
contact --> kerforn56 at wanadoo.fr , I’ll send it to u

patcito · December 15, 2006, 3:22pm

What does the generated SQL look like? I’m not quite picturing where
the doubles are coming from.

David

ok for each category I want to get all the brands it has, here is what
the query is for Category.id=50:

SELECT brands.id AS t0_r0, brands.name AS t0_r1, machines.id AS
t1_r0, machines.category_id AS t1_r1, machines.brand_id AS t1_r2,
machines.model AS t1_r3, machines.serial AS t1_r4, machines.type
AS t1_r5, machines.year AS t1_r6, machines.hours AS t1_r7,
machines.miles AS t1_r8, machines.capacity AS t1_r9,
machines.price AS t1_r10, machines.description_fr AS t1_r11,
machines.description_en AS t1_r12, machines.description_es AS
t1_r13, machines.description_ar AS t1_r14, machines.added_date AS
t1_r15, machines.update_date AS t1_r16 FROM brands LEFT OUTER JOIN
machines ON machines.brand_id = brands.id WHERE (category_id=50)

patcito · December 15, 2006, 3:36pm

On 12/15/06, Patrick A. [email protected] wrote:

ok thanx a lot, it worked that way.

no wait I still get double entries
this what my query looks like:
SELECT brands.* FROM brands INNER JOIN machines ON brands.id =
machines.brand_id WHERE (machines.category_id = 20)

patcito · December 15, 2006, 3:32pm

ok thanx a lot, it worked that way.

patcito · December 15, 2006, 4:21pm

Patrick :

no wait I still get double entries
this what my query looks like:
SELECT brands.* FROM brands INNER JOIN machines ON brands.id =
machines.brand_id WHERE (machines.category_id = 20)

Have you tried this ?

Brand.find(:all, :select => ‘DISTINCT brands.*’, :include => “machines”,
:conditions => “category_id = 1”)

With has_many :through in Rails 1.2RC1, you could use :uniq :

class Category < AR::B
has_many :machines
has_many :brands, :through => machines, :uniq => true
end

– Jean-FranÃ§ois.

–
Ã€ la renverse.

patcito · December 15, 2006, 3:40pm

Hi –

On Fri, 15 Dec 2006, Patrick A. wrote:

machines.model AS t1_r3, machines.serial AS t1_r4, machines.type
AS t1_r5, machines.year AS t1_r6, machines.hours AS t1_r7,
machines.miles AS t1_r8, machines.capacity AS t1_r9,
machines.price AS t1_r10, machines.description_fr AS t1_r11,
machines.description_en AS t1_r12, machines.description_es AS
t1_r13, machines.description_ar AS t1_r14, machines.added_date AS
t1_r15, machines.update_date AS t1_r16 FROM brands LEFT OUTER JOIN
machines ON machines.brand_id = brands.id WHERE (category_id=50)

Doesn’t AR squish that down into one brand with multiple
eagerly-loaded machines? I’ve tried to do a mini-replica of your
domain model (using household appliances, which may or may not be your
actual domain On the SQL side:

mysql> SELECT brands.id AS t0_r0, brands.name AS t0_r1,
machines.id AS t1_r0, machines.name AS t1_r1, machines.brand_id
AS t1_r2, machines.category_id AS t1_r3 FROM brands LEFT OUTER JOIN
machines ON machines.brand_id = brands.id WHERE (category_id = 1);
±------±------±------±------------±------±------+
| t0_r0 | t0_r1 | t1_r0 | t1_r1 | t1_r2 | t1_r3 |
±------±------±------±------------±------±------+
| 2 | GE | 3 | GE Washer 1 | 2 | 1 |
| 2 | GE | 4 | GE Washer 2 | 2 | 1 |
±------±------±------±------------±------±------+
2 rows in set (0.00 sec)

but on the AR side (this is what generated that SQL):

Brand.find(:all, :include => “machines”,
:conditions => “category_id = 1”)
=> [#<Brand:0xb75f8aa4 @attributes={“name”=>“GE”, “id”=>“2”},
@machines=[#<Machine:0xb75f87ac @attributes={“name”=>“GE Washer 1”,
“brand_id”=>“2”, “id”=>“3”, “category_id”=>“1”}>,
#<Machine:0xb75f84f0
@attributes={“name”=>“GE Washer 2”, “brand_id”=>“2”, “id”=>“4”,
“category_id”=>“1”}>]>]

there’s just one Brand in the results. So I guess something is
different in my replica, but I’m not sure what.

(Ultimately, by the way, I imagine you could wrap this in a has_many
:through construct; but of course one wants to know why it’s not
working.)

David

–
Q. What’s a good holiday present for the serious Rails developer?
A. RUBY FOR RAILS by David A. Black (http://www.manning.com/black)
aka The Ruby book for Rails developers!
Q. Where can I get Ruby/Rails on-site training, consulting, coaching?
A. Ruby Power and Light, LLC (http://www.rubypal.com)

patcito · December 15, 2006, 4:53pm

ok I did a quick hack:
def getbrands(cat)
@brands = Category.find(cat.id).brands
ar=[]
for b in @brands
ar.push b.name
end
return ar.uniq
end

not perfect but it does the trick

patcito · December 20, 2006, 12:32am

Hi,

I’m having some trouble with dates in ruby. I’d like to create a date
4 weeks ago from the current date, and find the date of the monday for
that week.

What is the simplest way to do this?

Thanks in advance,
Chris.