stack stack - 5 months ago 7
SQL Question

How can I count rows based on multiple different groups?

Here is my table structure:

// QandA
+----+----------------------------------------+------+---------+-----------+------------+
| Id | body | type | related | author_id | date_time |
+----+----------------------------------------+------+---------+-----------+------------+
| 1 | content of question1 | 0 | null | 12345 | 1467468795 |
| 2 | content of first answer for question1 | 1 | 1 | 53456 | 1467469311 |
| 3 | content of question2 | 0 | null | 43634 | 1467469512 |
| 4 | content of second answer for question1 | 1 | 1 | 43665 | 1467470098 |
| 5 | content of first answer for question2 | 1 | 3 | 43324 | 1467471291 |
+----+----------------------------------------+------+---------+-----------+------------+
-- type: 0 means question, 1 means answer
-- related: null means question, else it's the id of its own question


I'm trying to implement this (something exactly like what stackoverflow does). An user can ask:


  • 1 question per 90 minute

  • 6 question per day

  • 50 question per month



So I need to count the number of previous question for an user before letting him to ask. How can I do that?

Here is my query:

INSERT INTO QandA (id, body, type, related, author_id, date_tiem)
SELECT NULL, :body, 0, NULL, :id, unix_timestamp
FROM QANDA
WHERE author_id = :id AND
type = 1 AND
/* those three conditions */

Answer

Could be somethings lke this query

INSERT INTO QandA (id, body, type, related, author_id, date_tiem)
SELECT NULL, :body, 0, NULL, :id, unix_timestamp
FROM QANDA as a 

WHERE author_id = :id 
AND type = 1 
AND a.author_id not in ( select  author_id = :id
                    from QANDA  
                    where date_time < unix_timestamp(DATE_SUB(now(),INTERVAL 90 minute  ))  
                    and  author_id = :id
                    and  type =>1
                    group by  author_id
                    having count(*) => 1  ) 
AND a.author_id not in ( select  author_id = :id
                    from QANDA  
                    where date_time < unix_timestamp(DATE_SUB(now(),INTERVAL 1 DAY   ))  
                    and  author_id = :id
                    and  type = 1
                    group by  author_id
                    having count(*) => 6  )     
AND a.author_id not in ( select  author_id = :id, count(*) 
                    from QANDA  
                    where date_time < unix_timestamp(DATE_SUB(now(),INTERVAL 1 MONTH   ))  
                    and  author_id = :id
                    and  type = 1
                    group by  author_id
                    having count(*) =>  50  ) 

Be careful with not in ..check also for empty result ..