Rails and PostgreSQL issue with Having

Question

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

Answer 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")