How to query a complex boolean condition from MySQL table

Question

I have the following table for personal messages:

id*    sender*    recipient* del_by_rec del_by_send
-+----------+-------------+-----------+-----------+
 2     tom        beth            0          1
 5     tom        josh            0          1
 5     tom        chris           1          1
 7     ashley     burt            1          0
 8     jenna      tom             1          0
 8     jenna      ashley          0          0
 8     jenna      jenna           1          0
10     ashley     burt            0          0

where

id is the message id,
sender and recipient are user logins,
sender is unique to a message id
there can be more than one recipient to a message id
del_by_rec is 1 if message has been deleted by recipient from his inbox and
del_by_send is 1 if message has been deleted by the sender form his outbox.
id-sender-recipient is the primary key, and
id references the primary key id in another table that contains the message.

I need to determine if a message is safe to be deleted from the table once the current user has decided to do so:

from the recipient's inbox:

if the sender has already deleted the message from his outbox (del_by_send = 1); and
if this user is the only recipient that has not yet deleted this message from his inbox (del_by_rec = 0 for this user, and del_by_rec = 1 for all other recipients.)

or from the sender's outbox:

if all recipients have deleted this message from their inboxes (del_by_rec = 1 for all recipients of this message).

otherwise, the current user will just flag this message for deletion by setting his corresponding del_by_rec or del_by_send flag to 1.

Is there a way to efficiently query this criteria for

the current user viewing the current message; or
the current user mass deleting multiple messages (I'm thinking for this case a multiple-row result is to be returned).

It would be awesome to have a boolean/int returned.

For the love of me I couldn't get past this query (assuming the user is ashley and she wants to delete message 8 from her inbox):

SELECT (
    (SUM(del_by_send) > 0) # sender has deleted (at least 1)
    && ((SUM(del_by_rec) + 1) >= COUNT(id)) # only 1 recipient has not deleted yet
    && ((SELECT del_by_rec FROM msg_meta WHERE recipient = 'ashley' AND id = 8) = 0) # and that recipient is this user
)
FROM msg_meta
WHERE id = 8
GROUP BY id

This seems to do the job, but is it kinda overkill?
This fails for multiple message ids to avoid an expensive foreach. I tried WHERE id IN (7,10) for recipient burt but I couldn't quite work it into the subquery..

Help. Thanks y'all..

Gordon Linoff · Accepted Answer · 2012-08-09 14:07:53Z

1

My suggestion is to use a subquery to get the information that you want, at the message level, and then apply logic to this. The following query comes close:

select id, (case when sum_del_by_send > 0 and (num_rec - sum_del_by_rec) <= 1
                 then 'Y'
                 else 'N'
            end) as IsSafeToDelete
from (select id,
             sum(del_by_send) as sum_del_by_send,
             sum(del_by_rec) as sum_del_by_rec,
             count(*) as num_rec
      from msg_meta
      group by id
     ) m

This doesn't take the current recipient into account. This variant does:

select id, (case when sum_del_by_send > 0 and (num_others - sum_del_by_others) = 1
                 then 'Y'
                 else 'N'
            end) as IsSafeToDelete
from (select id,
             sum(del_by_send) as sum_del_by_send,
             sum(case when recipient <> @RECIPIENT then del_by_rec end) as sum_del_by_others,
             sum(case when recipient <> @RECIPIENT then 1 else 0 end) as num_others,
             count(*) as num_rec
      from msg_meta
      group by id
     ) m

This handles all messages at once. To handle a specific message id, just replace "group by id" in the subquery with:

where id = @ID

(@RECIPIENT and @ID are meant to be the values that you want to customize the query for.)

answered Aug 9, 2012 at 14:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ana Ban Over a year ago

whoa, you're a legend! epic thanks!! :D i did have to add where id in (...) before group by id instead of replacing it to target more than 1 record. that's ok, right? again, wow.

Gordon Linoff Over a year ago

Yes, adding in a where clause to select more than one id is fine.

Ana Ban · Accepted Answer · 2012-08-09 11:46:58Z

1

Idea 1 :
Invert the booleans(call the columns something like keep or save) , update the user's deletes first and then do :

SELECT id , SUM(del_by_send) + SUM(del_by_rec)
FROM msg_meta
WHERE id = 8
GROUP BY id

All the records with 0 in the second column are deletable.

Idea 2 :
You could do it in 2 steps and with a sub query(viable for 1 id but it might be slow for a lot)
Step 1 : Update the flag for delete(either by rec or by sent) for either 1 or multiple message id's.
Step 2 :

SELECT id 
FROM msg_meta t
WHERE t.id = 8
AND NOT EXISTS(
  SELECT id
  FROM msg_meta t2
  WHERE t.id = t2.id
  AND (t2.del_by_rec = 0 OR t2.del_by_sent = 0)
)

Maybe 1 of these might give you the idea you need or the spark for idea 3 ;)

edited Aug 9, 2012 at 11:46

Ana Ban

1,4054 gold badges22 silver badges30 bronze badges

answered Aug 9, 2012 at 11:36

Kristof

3,3351 gold badge22 silver badges31 bronze badges

Comments

cptHammer · Accepted Answer · 2012-08-09 12:11:51Z

0

What a well documented post... (I wiched they were all like yours.)...

I see that there can only be one sender per message... so why wouldn't you put the sender's login, and the del_by_send flag in the messages table ?

This will save quite a lot of time updating only one row when the sender decide to delete the message. and a lot of space by recording only once the sender name for each message...

This should already speed things up because your tables will be lighter, and the updates never change more then one row.

(this would be closer to what is recommended by normalisation standards. Check http://en.wikipedia.org/wiki/Database_normalization for more info on database normalization)

to simplify your quest to safe-deletion, I would record in the message table the number of people involved with the message. (say, for message ID 5, people_count would be 3 (Tom, Josh and Chris). Each time a user (sender or receiver) deletes the message, decrement this counter. When the counter is down to 0, it is safe to delete de message.

Hope this heps...

edited Aug 9, 2012 at 12:11

answered Aug 9, 2012 at 12:06

cptHammer

4602 silver badges12 bronze badges

1 Comment

Ana Ban Over a year ago

haha, thanks, @cptHammer . will surely work that out, i knew there was something off with how i made the meta table.. this is what pizza and 3 hours of sleep does to humans. lol. lemme know if you come up wit more. thanks

Collectives™ on Stack Overflow

How to query a complex boolean condition from MySQL table

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related