CODEWITHSUNDEEP
mysqlbooleanconditional-statements
Ana Ban
Ana Ban

Reputation: 1405

How to query a complex boolean condition from MySQL table

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

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:

  1. if the sender has already deleted the message from his outbox (del_by_send = 1); and
  2. 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:

  1. 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

  1. the current user viewing the current message; or
  2. 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
  1. This seems to do the job, but is it kinda overkill?
  2. 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..

Upvotes: 2

Views: 1192

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.)

Upvotes: 1

cptHammer
cptHammer

Reputation: 460

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...

Upvotes: 0

Kristof
Kristof

Reputation: 3315

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 ;)

Upvotes: 1

Related Questions