Group by by date using left join mysql

Question

Group by by date using left join mysql

Navigation

#1 by (3 votes)

5

I am trying to make a query that involves 3 tables, post, comments and likes. I need to know the date of the post and the title of the post, the number of people who have commented on the post and the number of likes for each person, the detail is in what I need to do group by with the date of the comments and the likes of each person to make a estadisca with the values that I obtain with the data of likes and comments per person. if there is no value in the "comments or likes" table, it would be replaced by 0.

Tables and data

CREATE TABLE IF NOT EXISTS 'post' (
'id' int(11) NOT NULL,
'titulo' varchar(200) NOT NULL,
'user_id' int(11) NOT NULL,
'created_at' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE         CURRENT_TIMESTAMP
 );

INSERT INTO 'post' ('id', 'titulo', 'user_id', 'created_at') VALUES
(1, 'group by mysql', 19, '2018-02-18 19:10:30');

CREATE TABLE IF NOT EXISTS 'comentarios' (
'id' int(11) NOT NULL,
'content' varchar(200) NOT NULL,
'post_id' int(11) NOT NULL,
'user_id' int(11) NOT NULL,
'created_at' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 );

INSERT INTO 'comentarios' ('id', 'content', 'post_id', 'user_id',    'created_at') VALUES
(1, 'comentario 1', 1, 19, '2018-02-19 13:12:09'),
(2, 'comentario 2', 1, 20, '2018-02-20 23:42:09'),
(3, 'comentario 3', 1, 19, '2018-02-21 19:12:30'),
(4, 'comentario 4', 1, 21, '2018-02-26 11:38:34'),
(5, 'comentario 5', 1, 22, '2018-02-28 19:13:15');

CREATE TABLE IF NOT EXISTS 'likes' (
'id' int(11) NOT NULL,
'post_id' int(11) NOT NULL,
'user_id' int(11) NOT NULL,
'created_at' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 );

INSERT INTO 'likes' ('id', 'post_id', 'user_id', 'created_at') VALUES
(1, 1, 19, '2018-02-28 19:16:12'),
(2, 1, 21, '2018-02-28 19:16:12'),
(3, 1, 22, '2018-02-22 19:16:21');

EDIT

I have advanced in the query, I still need to get that if there are likes on a date that there are no comments comments and the counter of the comments come out in null or 0. likewise if there is a comment on the date that there are no likes that the likes come out in null or 0.

Current query

 SELECT
 p.id as post_id,
 titulo,
 MAX(DATE(p.created_at)) as fecha_post,
 MAX(DATE(c.created_at)) as fecha_comentario,
 COUNT(c.user_id) as count_personas,
 MAX(DATE(l.created_at)) as fecha_likes,
COUNT(l.user_id) as count_likes
  FROM post as p
  LEFT OUTER JOIN comentarios as c 
   ON p.id=c.post_id 
  LEFT OUTER JOIN likes as l
    ON p.id=l.post_id and date(c.created_at)=date(l.created_at)
  GROUP BY p.id, DATE(p.created_at), DATE(c.created_at), DATE(l.created_at);

update fields

UPDATE  likes
SET created_at='2018-02-22 14:46:21'
WHERE id=3

Exit I hope

Can someone help me finish the consultation and get the desired result?

mysql php

asked by Josee Naava 28.02.2018 в 20:35

source

1 answer

Problem with code snippet Visual Studio 2015 real-time currency exchange from MXN to USD online store to show prices

score 3 · Answer 1

To be able to do it, you need to make a series of changes:

Table comentarios must have a well-defined PRIMARY KEY.

Relate table likes with that of comentarios .

I leave you the structure of the two tables:

CREATE TABLE 'comentarios' (
  'id' int(11) unsigned NOT NULL AUTO_INCREMENT,
  'content' varchar(200) NOT NULL,
  'post_id' int(11) NOT NULL,
  'user_id' int(11) NOT NULL,
  'created_at' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE 'likes' (
  'id' int(11) unsigned NOT NULL AUTO_INCREMENT,
  'post_id' int(11) NOT NULL,
  'user_id' int(11) NOT NULL,
  'comment_id' int(11) NOT NULL,
  'created_at' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY ('id'),
  KEY 'comment_idx' ('comment_id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Do not forget to add the values in the column comment_id that relate the table likes with comentarios , example:

UPDATE likes SET comment_id = 5 WHERE comment_id = 0;

The desired query:

SELECT
  p.id as post_id,
  DATE(p.created_at) as fecha_post,
  c.user_id AS usuario_comentario,
  DATE(c.created_at) as fecha_comentario,
  COUNT(c.user_id) as count_personas,
  DATE(l.created_at) as fecha_likes,
  COUNT(l.user_id) as count_likes
FROM post as p
LEFT JOIN comentarios as c 
       ON p.id=c.post_id
LEFT JOIN likes as l
       ON p.id=l.post_id AND l.comment_id = c.id
GROUP BY p.id, DATE(p.created_at), DATE(c.created_at), DATE(l.created_at);

Desired output:

+---------+------------+--------------------+------------------+----------------+-------------+-------------+
| post_id | fecha_post | usuario_comentario | fecha_comentario | count_personas | fecha_likes | count_likes |
+---------+------------+--------------------+------------------+----------------+-------------+-------------+
|       1 | 2018-02-18 |                 19 | 2018-02-19       |              1 | NULL        |           0 |
|       1 | 2018-02-18 |                 20 | 2018-02-20       |              1 | NULL        |           0 |
|       1 | 2018-02-18 |                 19 | 2018-02-21       |              1 | NULL        |           0 |
|       1 | 2018-02-18 |                 21 | 2018-02-26       |              1 | NULL        |           0 |
|       1 | 2018-02-18 |                 22 | 2018-02-28       |              3 | 2018-03-01  |           3 |
+---------+------------+--------------------+------------------+----------------+-------------+-------------+