CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
Julio G Medina
Julio G Medina

Reputation: 692

Rails 3.2 and getting AR to properly order this query

I currently have a scope where I am attempting to find last record created in an association and select it if a particular boolean value is false

IE Foo has_many Bar's and Bar's has a boolean column named bazzed

scope :no_baz,   joins(:bars).order("bars.id DESC").limit(1).where("bars.bazzed = 'f'")

The problem with this is that rails turns this query into something like this

SELECT "foos".* FROM "foos" INNER JOIN "bars" ON "bars"."foo_id" = "foos"."id" WHERE (bars.bazzed = 'f') ORDER BY bars.id DESC LIMIT 1

the problem lies that rails is calling the order and limit after the where clause, what i'm looking for is to do the order and limit first to try and find the last bar that has bazzed set to false.

Is there a native AR way to perform the query I am attempting to accomplish?

EDIT

I am trying to grab the foo's that have a bar where the last bar they have has bazzed set to false and only if the last bar that that foo has has a false bazzed.

Upvotes: 0

Views: 971

Answers (2)

Julio G Medina
Julio G Medina

Reputation: 692

Using a class method for now but the problem with that lies that it returns an array object and not an active record relation which is what i'm trying to return. Still attempting to get the query correctly done.

Upvotes: 0

Kulgar
Kulgar

Reputation: 1875

Ok, I would suggest this for the query on the "foo" model:

Foo.bars.where("bars.bazzed = ?", 'f').all( :order => "created_at DESC").first

Note: 'f' can be replaced by false, depending on the value you use in your "bazzed" column, of course.

[Edit]

Ok, as I think I better understand the problem, here is a suggestion, but for a public method and not a scoped query.

def no_baz
  all_no_baz_foos = Array.new
  Foo.all.each do |foo|
      last_bar = foo.bars.all.order("bars.id DESC").first
      if last_bar.bazzed == 'f'
        all_no_baz_foos << foo
      end
  end
  return all_no_baz_foos
end

This method will return an Array with all the no_baz_foos record in it. As I did not test my code, you may have to change few things for it to work, but I think you get the idea.

For the "scope" method, I just can't find a way to chain correctly the queries to have the desired result. If anyone else knows how to achieve that using a scope, I'll be glad to hear the solution too.

Upvotes: 1

Related Questions