how to select distinct with an include?

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

Hi --

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)

ok thanx a lot, it worked that way.

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

Hi --

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

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)

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 :slight_smile: On the SQL side:

   > 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);

Patrick :

no wait I still get double entries :frowning:
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.

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

Hi --

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

You could write that as:

   def getbrands(cat)
     @brands = Category.find(cat.id).brands
     @brands.map {|b| b.name }.uniq
   end

and if you don't need the instance variable you can factor it out (or
make it local). However...

not perfect but it does the trick

I can't help wondering whether there's something engineered wrongly
that is going to show itself later. I'd still be interested in trying
to track it down, unless you've moved on to other things :slight_smile:

David

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.

t = 4.weeks.ago

is a start. what you consider the monday of that week depends in part
on how you define when a week starts and ends.

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,

thank you!

4.weeks.ago is exactly what i was looking for! thank you!

is it possible to create a date from a week number,
and then call .monday on that week/date?

I got it!

date = Time.at(50.weeks).monday

cheers.

actually it's still not quite what I need.

that gives me a year of 1970.

how do i get week 50 of the current year?

Time.mktime(2006,01,01) + 50.weeks

answers my last post.

cheers.

4.weeks.ago.beginning_of_week ?

For more methods, open a console (./scripts/comsole) and try:
t=Time.now
t.methods.sort

This works for any everything in Ruby.

irb(main):028:0> (Time.now + 50.weeks).monday
=> Mon Dec 03 00:00:00 -0500 2007
irb(main):029:0> (Time.now - 4.weeks).monday
=> Mon Nov 20 00:00:00 -0500 2006