Rails 3 - Complex active record query /w subqueries

I have a simple history table named actions that resembles the following:

id | actionable_id | actionable_type | user_id
----------------------------------------------

The table's actionable_types can either be 'comment', 'vote' or 'submission' with the actionable_id pertaining to the primary key of each respective table. As well, all comments and votes belong to a submission.

Furthermore, I have a friendships table that resembles the following:

id | user_id | friend_id | user_id
----------------------------------

The table's user_id pertains to the user who owns a friend (with the friend_id being the friend).

If you haven't guessed it already, any time a user comments, votes or uploads a new submission, it gets added to the history table as a new 'action'. To get all of the actions for a single user's friends is quick and straightforward and has worked wonderfully thus far. However, that is not my problem...

I would like to grab all of the comments and votes that belong to the current user's group of non-friends. Furthermore, the comments and votes on a submission entry will only be selected if the current user has also voted or commented on the same submission.

Therefore, I need to make the following two queries, assuming current_user is the presently logg开发者_开发技巧ed in user.

Query 1 - all actions of type vote that meet the following criteria :

• they belong to the current_user's non-friends
• they do not belong to the current_user
• the current_user has at least one vote that belongs to the submission (in other words, if a vote belongs to this collection, then there exists at least one vote outside of the collection whose vote.user_id = current_user.user_id).

Query 2 - all actions of type comment that meet the following criteria :

• they belong to the current_user's non-friends
• they do not belong to the current_user
• the current_user has at least one comment that belongs to the submission (in other words, if a comment belongs to this collection, then there exists at least one comment outside of the collection whose comment.user_id = current_user.user_id).

Since both queries are hitting the same Action table, it would be ideal for me to have a single query (although I feel that is highly unlikely, given the amount of JOINS that are needed).

The main hangup on my end is the subqueries related to checking non-friendships and the last condition of each query (current_user has at least one...). I will spare you my current code-base of queries as it may not even be close.

Thanks for looking & helping out.

---

Quite a complex query you would like there. Here is my suggestion (assuming I have understood your request correctly:

SELECT a.* FROM actions a
LEFT OUTER JOIN vote v
ON v.id=a.actionable_id AND a.actionable_type='vote'
LEFT OUTER JOIN comment c
ON c.id=a.actionable_id AND a.actionable_type='comment'
WHERE a.user_id<>current_user.id
AND NOT EXISTS (
  SELECT 1 FROM friendships f
  WHERE f.user_id=current_user.id
  AND f.friend_id=a.user_id
) AND EXISTS (
  SELECT 1 FROM submission s
  LEFT OUTER JOIN vote v1
  ON v1.submission_id=s.id
  LEFT OUTER JOIN comment c1
  ON c1.submission_id=s.id
  WHERE (v1.id IS NOT NULL
  AND v1.submission_id=v.submission_id
  AND v1.id<>v.id
  AND v1.user_id=current_user.id) OR
  (c1.id IS NOT NULL
  AND c1.submission_id=c.submission_id
  AND c1.id<>c.id
  AND c1.user_ud=current_user.id)
);

So I grab all actions and join the votes and comments against the actions. Then I filter out all actions are the same user_id which my current user has. Make sure that the action is not of a friend. And finally I get the submissions and join the current_users votes and comments against them, and when either vote or comment exists for the submission, which is the same submission from the action selected, then I have found the action for the non-friend user which you are looking for.