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