• Registered: 2012-01-16
  • Posts: 2

Topic: Find all objects that have children that are 'ready'

I've been trying to figure this out for a couple days now, and have no idea what to search for anymore; either I am the first person to have this problem, or it's so dead simple I'm just not seeing it.

I am using Rails 3.2.8, Ruby 1.9.3, and Postgresql.

I have two models:

class Order < ActiveRecord::Base
  has_many :items
  ...
end

class Item < ActiveRecord::Base
  belongs_to :order
  ...
end

Items also have a string (using enums) for status, and a boolean for acquired.

What I want to do is find all Orders with items that are:

At least one item has status = in_progress AND acquired = true (And any other orders have that) OR the other orders have status IN (canceled, completed).

Everything I've tried so far returns orders if any of the items match on those fields, and I need it to be an all or nothing situation.

So, I need to find orders that have items that are ready to close out, and not return any others that are not ready. Technically, if I could get the others that aren't ready to close sorted after the ones that are, it would be better, but I already am out of my depth here and figured I would do the other query separately.

I'd also really like this to be in one query, so I can paginate it as well (well, two queries and two paginated results; one set for ready to close orders, and the other set for not ready to close, unless I CAN get them all into one nice query.) If I end up doing sorting and stuff after getting the results I don't think that will work. But I feel like this sort of thing should be relatively easy, I am just not experienced enough to do it yet.

Any help would be great!

Last edited by d3vkit (2013-01-08 20:55:01)

  • Registered: 2010-10-01
  • Posts: 20

Re: Find all objects that have children that are 'ready'

in your Item model, create two methods

def self.status(value)
     where(:status => value)
end

def self.acquired
     where(:acquired => true)
end

Now you can do something like

@items = Item.status("in_progress").acquired.group("order_id")

I BELIEVE this should work and allow you to output by Orders.

Last edited by downtrodden (2013-01-10 14:56:49)

  • Registered: 2012-01-16
  • Posts: 2

Re: Find all objects that have children that are 'ready'

downtrodden wrote:

in your Item model, create two methods

def self.status(value)
     where(:status => value)
end

def self.acquired
     where(:acquired => true)
end

Now you can do something like

@items = Item.status("in_progress").acquired.group("order_id")

I BELIEVE this should work and allow you to output by Orders.

Thanks for the help, but, Postgres doesn't like this.

ActiveRecord::StatementInvalid: PG::Error: ERROR:  column "items.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "items".* FROM "items"  WHERE "items"."st...

I am starting to regret using Postgresql for this project. I believe this problem is because Postgres is following SQL standards more strictly than MySQL, but I don't really know how to make this work.

Thanks for the help, but I will have to keep working on this. I guess I need to just learn the SQL I need for this, and write a straight SQL query.

Last edited by d3vkit (2013-01-10 18:40:19)