N+1 query: fetching both inbox and sentbox #80

Closed
ghost opened this Issue Aug 31, 2012 · 4 comments

Comments

Projects
None yet
1 participant
@ghost

ghost commented Aug 31, 2012

Hello,

I need to check that a conversation belongs to a user and is present in either inbox or sentbox but not in trash.

Ruby code: (current_user.mailbox.inbox | current_user.mailbox.sentbox).include? @conversation

Which executes:

User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT 1  [["id", 1]] 
Conversation Load (0.1ms)  SELECT "conversations".* FROM "conversations" WHERE "conversations"."id" = 22 LIMIT 1
Conversation Load (0.4ms)  SELECT DISTINCT conversations.* FROM "conversations" INNER JOIN "notifications" ON "notifications"."conversation_id" = "conversations"."id" AND "notifications"."type" IN ('Message') INNER JOIN "receipts" ON "receipts"."notification_id" = "notifications"."id" WHERE "notifications"."type" = 'Message' AND "receipts"."receiver_id" = 1 AND "receipts"."receiver_type" = 'User' AND "receipts"."mailbox_type" = 'inbox' AND "receipts"."trashed" = 'f' ORDER BY conversations.updated_at DESC
Conversation Load (0.7ms)  SELECT DISTINCT conversations.* FROM "conversations" INNER JOIN "notifications" ON "notifications"."conversation_id" = "conversations"."id" AND "notifications"."type" IN ('Message') INNER JOIN "receipts" ON "receipts"."notification_id" = "notifications"."id" WHERE "notifications"."type" = 'Message' AND "receipts"."receiver_id" = 1 AND "receipts"."receiver_type" = 'User' AND "receipts"."mailbox_type" = 'sentbox' AND "receipts"."trashed" = 'f' ORDER BY conversations.updated_at DESC

So the only difference in the last two queries is "receipts"."mailbox_type" which equals mailbox in the 3rd query and sentbox in the last query.

How is it possible to avoid the N+1 query problem and run just one query with "mailbox_type" IN ('mailbox', 'sentbox') ?

Owner

Roendal commented Aug 31, 2012

Hi @mdnzenati!

You should be able to achieve this with

@conversation.receipts_for(current_user).not_trash != 0

Wich means current_user has receipts for this conversation (inbox o sentbox) and it isn't fully trashed.

Does this help you?

@ghost

ghost commented Aug 31, 2012

Hi @Roendal,

It partially works because it checks that current_user is participant in @conversation but if I do

@conversation.move_to_trash current_user`

and then run:

@conversation.receipts_for(current_user).not_trash != 0

the output is True and it should be False.

Owner

Roendal commented Aug 31, 2012

This means that one of the lines seems to be doing it wrong ... I can't do any test because I don't have any virtual machine with rails runing.

Can you discern which one is failing? Creating a conversation with several messages, moving them to trash for one user and checking the receipts are trashed should do the trick.

Thanks @mdnzenati!

Owner

Roendal commented Dec 13, 2012

Closed due to inactivity. Feel free to reopen if you still need it.

Roendal closed this Dec 13, 2012

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment