Выберите одну строку, в которой тип предупреждения и идентификатор элемента совпадают, а также сколько похожих строк

Question

Я пытаюсь сделать систему уведомлений для моего проекта как можно более простой на стороне пользователя.

"Джон Браун и еще 4 понравились твои фото"

Я старался:

Select a.id, a.item_id, a.type u.f_name, u.l_name, count(a)
From alerts
Join users on a.user_id = u.u_id
Where owner_id = {THE_USER_ID}

но это не "работает"

---

Таблица пользователей

u_id | f_name | l_name 
==========================
234    roy      wright    
654    pam      brown
564    kim      harris
334    tory     plummer
876    rick     forbes    
123    paul     nichol
980    mario    chang
454    todd     thompson
886    sam      richards
215    tash     gates
...    .....    ........

Таблица оповещений

id   |   item_id   |   type    | user_id | owner_id
===================================================
1         21           like      234
2         21           comment   654
3         32           share     876
4         21           like      778
5         21           like      890
6         21           share     123
7         54           share     454
8         32           comment   655
9         60           comment   886
10        32           like      215
..        ..           ......    ...

Результаты, которые я хочу

id | item_id | type   | u_id | f_name | l_name | amount_more 
============================================================
1    21       like     234    roy      wright    2
2    21       comment  654    pam      brown
3    32       comment  876    rick     forbes    
6    21       share    123    paul     nichol
7    54       share    454    todd     thompson
9    60       comment  886    sam      richards
10   32       like     215    tash     gates
..   ..       ....     ...    .....    ........

Answer 1

SELECT
    MIN(CASE WHEN RowNumber = 1 THEN id END) as Id
    ,item_id
    ,type
    ,MIN(CASE WHEN RowNumber = 1 THEN u_id END) as u_id
    ,MIN(CASE WHEN RowNumber = 1 THEN f_name END) as f_name
    ,MIN(CASE WHEN RowNumber = 1 THEN l_name END) as l_name
    ,COUNT(*) - 1 as amount_more
FROM
  (
    SELECT
        t.*
        ,(@rn:= if((@item = t.item_id) AND (@type = t.type),@rn + 1,
                   if((@item:=t.item_id) AND (@type:=t.type),1,1)
           )
        ) as RowNumber
    FROM
        (SELECT *
         FROM
            alerts a
            LEFT JOIN usersTbl u
            ON a.user_id = u.u_id
         WHERE
            a.owner_id = 201
         ORDER BY
             a.item_id
            ,a.type
            ,a.id) t
        CROSS JOIN (SELECT @rn:=0,@item:=0,@type:='') var
    ORDER BY
        item_id
        ,type
        ,id
   ) t2         
GROUP BY
    item_id
    ,type
ORDER BY
    id
;    

Таким образом, вам нужно создать разделенный номер строки, чтобы определить, какую запись вы хотите рассмотреть в первую очередь. Затем, используя условное агрегирование, вы можете выбрать его как своего пользователя. это работает, проверьте это здесь: http://rextester.com/VXZONO82847

Answer 2

В запросе вы не используете псевдонимы a и u правильно, используйте это:

Select 
a.id, 
a.item_id, 
a.type,
u.f_name, 
u.l_name, 
count(a.*)
From alerts a Join users u
on(a.user_id = u.u_id)
Where owner_id = {THE_USER_ID}
group by 1,2,3,4,5;