3

I am trying to retrieve a table from the following Action table:

Columns :ID, Name, Status, Product_ID, User_ID and several other non relevant column for this issue.

Every time a user want a product I create a record like this : Name = Want, Status = True, Product_ID = ID of the product wanted and User_ID is the ID of the user.

Then every time a user unwant a product I create a record like this : Name = Want, Status = False, Product_ID = ID of the product unwanted and User_ID is the ID of the user.

I did that because I have other action's name in my table.

Now I'd like to retrieve all the product wanted, so I should retrieve all the last want actions grouped by product_id for a particular user ordered by descending created_at, and then only retrieve the action where the status = true.

So to get all the last want actions grouped by product_id for a User I did that:

Action.select("DISTINCT ON (product_id) product_id,created_at, status, * ").where{(user_id   == id) & (name == 'want')}.group("product_id, id, status").order(' product_id,created_at DESC')

That retrieve the last actions per product and user but retrieve both true and false statuses The only issue is that I don't know how to filter this table to only get the action if it's true.

I tried to do it like this:

Action.select("DISTINCT ON (product_id) product_id,created_at, status, * ").where{(user_id == id) & (name == 'want')}.group("product_id, id, status").having("status = 'true'").order(' product_id,created_at DESC')

But that would give me the last actions where want = true. If the last action is status = false it will retrieve the one before that when status = true.

Here is an idea of what I want but I have no idea how to achieve that with rails: http://sqlfiddle.com/#!9/e4117/3

Cœur
  • 37,241
  • 25
  • 195
  • 267
Jeremy B
  • 911
  • 7
  • 20

1 Answers1

1

You could try adding a subselect to the conditions and remove the group by:

Action.
  where( user_id: id, name: "want", status: "true").
  where( ["id IN (SELECT max(id) FROM actions WHERE user_id = ?
              AND name = 'want' GROUP BY product_id)", id]).
  order( "product_id")

You would need to rely on the order of the id column to be the last action for this to work correctly. If you can't do that you could use DISTINCT ON in the subselect:

Action.
  where( user_id: id, name: "want", status: "true").
  where( ["id IN (SELECT DISTINCT ON (product_id) id FROM actions
              WHERE user_id = ? AND name = 'want'
              ORDER BY product_id, created_at DESC)", id]).
  order( "product_id")
lassej
  • 6,256
  • 6
  • 26
  • 34
  • If I do that, that will give me all the status true, but If i have that : last item is status false and item before the last is true, it will give me the item before the last. I want it to return nothing if it's not the last item. Thanks, – Jeremy B Apr 26 '13 at 13:00
  • Thanks just one question though :ORDER BY product_id, created_at DESC)", user_id]). order( "product_id") should user_id be id ? thanks, – Jeremy B Apr 26 '13 at 14:00