I have a votes table that has many subitems. I have a subitems action table that records what action was performed by which user on which group, on which subitem, on which vote

Users can either cast/uncast/delete/update create on actions.

To find out if a user 'belongs' to a subitem, I used groupwise max.The logic is that among all the cast or uncast actions that has has performed on a subitem, if the latest timestamp is cast, then he has indeed voted on it. If it is uncast then he has not.

This is what the query looks like :

select  act.user_id,act.anonymous
      ( SELECT  sub_item_id,user_id ,max(created_at) as newestAction
            from  vote_sub_item_action
            where  sub_item_id =10
              AND  user_id=910
              AND  action_type IN('ACTION_CAST_VOTE','action_uncast_vote')
            GROUP BY  user_id 
      ) as x
    inner join  vote_sub_item_action as act on act.user_id = x.user_id
      and  act.created_at = newestAction
      and  act.action_type='action_cast_vote'
      and  act.sub_item_id =x.sub_item_id; 

Now I would like to get all the subitems that 'belong' to him from a particular vote. The logic of belongs is the same as above.

How may I do that?



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.