SQL Obtiene las últimas inputs X de un tipo_personal 'personalizado' que count el número individual de usuarios de post_type personalizado

Me gustaría, si es posible, get una consulta:

  • Los últimos 4 usuarios diferentes
  • Excluyendo 'ID' = '1'
  • WITH post_type = 'custom'
  • Pedido por date o ID ( DESC )
  • Contando el número total de post_type 'personalizado' para cada usuario (COUNT ())

Aquí hay un ejemplo de datos:

 Table Name: 'post' ID | user | Date | title | status | post_type | | | | | "2785" | "1" | "2016-05-24 18:49:15" | "Title" | "published" | "page_post" "2783" | "5" | "2016-05-24 11:24:08" | "Title" | "published" | "custom" "2781" | "1" | "2016-05-18 20:40:11" | "Title" | "published" | "custom" "2759" | "3" | "2016-05-07 14:00:22" | "Title" | "published" | "custom" "2757" | "12" | "2016-05-02 12:41:00" | "Title" | "published" | "custom" "2756" | "1" | "2016-04-30 22:47:07" | "Title" | "published" | "custom" "2755" | "5" | "2016-04-29 13:54:21" | "Title" | "published" | "blog_post" "2754" | "1" | "2016-04-29 11:33:36" | "Title" | "published" | "page_post" "2738" | "3" | "2016-05-06 12:45:58" | "Title" | "published" | "custom" "2736" | "12" | "2016-04-24 17:17:04" | "Title" | "published" | "custom" "2683" | "15" | "2016-04-22 20:27:45" | "Title" | "published" | "custom" "2681" | "18" | "2016-04-21 00:20:55" | "Title" | "published" | "custom" "2671" | "1" | "2016-04-11 18:38:57" | "Title" | "published" | "other_post" "2652" | "4" | "2016-04-02 17:43:41" | "Title" | "published" | "custom" "2651" | "5" | "2016-03-28 17:12:00" | "Title" | "published" | "custom" "2639" | "18" | "2016-03-22 14:58:00" | "Title" | "published" | "custom" "2630" | "19" | "2016-03-21 15:27:00" | "Title" | "published" | "custom" "2617" | "14" | "2016-03-17 12:22:06" | "Title" | "published" | "custom" "2616" | "5" | "2016-03-16 15:23:00" | "Title" | "published" | "page_post" "2598" | "4" | "2016-03-14 15:27:29" | "Title" | "published" | "custom" "2596" | "2" | "2016-03-10 17:43:00" | "Title" | "published" | "custom" "2571" | "1" | "2016-03-09 14:19:31" | "Title" | "published" | "blog_post" "2250" | "19" | "2016-02-29 12:15:48" | "Title" | "published" | "custom" "2249" | "15" | "2016-02-29 09:45:35" | "Title" | "published" | "custom" "2215" | "13" | "2016-02-22 18:21:54" | "Title" | "published" | "custom" "2201" | "3" | "2016-02-15 17:40:00" | "Title" | "published" | "custom" "1914" | "2" | "2015-11-13 12:08:00" | "Title" | "published" | "other_post" 

La consulta incompleta que tengo:

 SELECT * FROM 'posts' WHERE 'user' != 1 AND 'post_type' = 'custom_type' GROUP BY 'user' ORDER BY 'ID' DESC LIMIT 4 

El pedido por ID es similar aquí que orderando por date . Sin GROUP BY 'user' esto funciona, pero el problema es que quiero evitar en esta selección 2 publicaciones 'personalizadas' para un usuario: necesito una selección de 4 usuarios diferentes. Entonces mi problema es GROUP BY .

¿Cómo puedo resolver este problema?

Última cosa:
¿Es posible con COUNT() , contar el total de publicaciones "personalizadas" para cada usuario en esta selección y devolver el valor en una nueva columna?

Prueba esto;)

Consulta 1 :

 select t1.*, t2.userCnt from `posts` t1 inner join ( select max(`ID`) as `ID`, `user`, count(1) as userCnt from `posts` where `user` != '1' and `post_type` = 'custom' group by `user` ) t2 on t1.`ID` = t2.`ID` and t1.`user` = t2.`user` order by t1.`ID` desc limit 4 

Verifique estos resultados SqlFiddle :

 | ID | user | Date | title | status | post_type | userCnt | |------|------|---------------------|-------|-----------|-----------|---------| | 2783 | 5 | 2016-05-24 11:24:08 | Title | published | custom | 2 | | 2759 | 3 | 2016-05-07 14:00:22 | Title | published | custom | 3 | | 2757 | 12 | 2016-05-02 12:41:00 | Title | published | custom | 2 | | 2683 | 15 | 2016-04-22 20:27:45 | Title | published | custom | 2 | 

La subconsulta t2 obtendrá la ID máxima en cada usuario cuando el user != '1' and post_type = 'custom' , luego inner join t1 con t2 en t1.ID = t2.ID and t1.user = t2.user nos hará grabar que tiene un ID máximo por cada user en la post tabla. Me gusta: "2783", "2759", "2757", "2683", "2681", "2652", "2630", "2617", "2596", "2215".

Y por último con el order by limit y, por supuesto, puede get "2783", "2759", "2757", "2683". Espero no haber confundido tu pregunta.

Puede usar una consulta con variables para get los ID de usuario de los últimos 4 usuarios:

 SELECT DISTINCT `user` FROM ( SELECT user, @cnt := IF(FIND_IN_SET(`user`, @uid) > 0, @cnt, IF(@uid := CONCAT(@uid, ',', CAST(`user` AS CHAR(4))), @cnt + 1, @cnt + 1)) AS cnt FROM posts CROSS JOIN (SELECT @cnt := 0, @uid := '') AS vars WHERE `user` != 1 AND `post_type` = 'custom' ORDER BY ID DESC) AS t WHERE t.cnt <= 4 

Esta consulta devuelve los siguientes valores:

 user ---- 5 3 12 15 

Usando la consulta anterior como subconsulta, puede get el resultado esperado con:

 SELECT `user`, SUM(`post_type` = 'custom') AS cnt FROM posts WHERE `user` IN (... above query goes here ...) GROUP BY `user` 

Salida:

 user | cnt -----+----- 3 | 3 5 | 4 12 | 2 15 | 2 

Demo aquí