LoicTheAztec LoicTheAztec - 6 months ago 8
MySQL Question

SQL Get X last entries from a 'custom' post_type counting users individual number of custom post_type

I would like, if possible to get in one query:


  • The last 4 different users

  • Excluding
    'ID' = '1'

  • WITH post_type =
    'custom'

  • Ordered by
    date
    or
    ID
    (
    DESC
    )

  • Counting the total number of 'custom' post_type for each user (COUNT())



Here is a data example:

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"


The incomplete query that I have:

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


Ordering by
ID
is similar here than ordering by
date
.
Without
GROUP BY 'user'
this works, but the problem is that I want to avoid in this selection 2 'custom' post for one user: I need a selection of 4 different users. So my problem is
GROUP BY
.


How can I solve this issue?

Last thing:

Is it possible with
COUNT()
, counting total 'custom' posts for each user in this selection, and returning the value in a new column?

Answer

Try this;)

Query 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

Check this SqlFiddle Results:

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

Subquery t2 will get the max ID in each user when user` != '1' and `post_type` = 'custom'`, then `inner join` t1 with t2 on `t1.`ID` = t2.`ID` and t1.`user` = t2.`user will get us record which has max ID by each user in table post. Like : "2783", "2759", "2757", "2683", "2681", "2652", "2630" , "2617", "2596", "2215".

And last with order by and limit, of course you can get "2783", "2759", "2757", "2683". Hope I did not mistake your question.