List the post owner and the last user you commented on. Mysql

Asked

Viewed 64 times

0

Eai guys, I have the following query below, where I list all posts, amount of comments and the user who created it, but also wanted to list the last user who commented each post, will be able to do all this in just one query?

SELECT post.titulo, COUNT(comentario.codigo), autor.nome, MAX(comentario.codigo_usuario) FROM post JOIN comentario JOIN usuario autor JOIN usuario ON post.codigo_usuario = autor.codigo AND post.codigo = comentario.codigo_topico AND usuario.codigo = comentario.codigo_usuario GROUP BY post.codigo
mysql query select

  • Just one remark, if you’re going to use JOIN, I think it gets more interesting with the ON clause instead of WHERE: JOIN comentario ON post.codigo = comentario.codigo_post JOIN usuario ON .... Or, if you really prefer WHERE, you don’t need JOIN, separate tables with a comma FROM post, comentario, usuario WHERE post.codigo. Of course you should do as you think best, the suggestion is only in the sense of "standardize", if you find interesting.

  • One possibility would be, assuming that the comments have ID in temporal order use a new JOIN linking the MAX( comment.id ) again to the comments table, and obtaining the commentator ID. Alternatively, in Mysql you can use ORDER BY ... DESC for GROUP to take the last of a given table, but it is not guaranteed that this will work in future versions

  • I’ll use ON then. I didn’t understand very well what you said, I can’t get the name of who created the post and at the same time get the name of the last q commented, and ORDER BY I will use to sort by the name of the post.

  • Remember that nothing prevents you from doing ORDER BY name, post.id DESC, for example (ordering primarily by name, but leaving the post’s decreasing ID as the second sort factor). ORDER BY can accept several different expressions and/or columns separated by a comma.

  • As for picking the names separately, if both are from the user table, you need to give JOIN twice JOIN user AS author ON... JOIN usuario AS commentator ON ...

  • I didn’t know you could use two clauses in ORDER BY. I edited the post by putting how is the query, I used MAX to get the code from the last user q commented, but how do I use this code to compare with the table user(to get the name)? Since you can only use MAX in select and not ON

  • Able to do, I put a "user.code = (select max(codigo_usuario) from commentWhere comment.codigo_post = post.codigo" to make the comparison with the user code. Thanks for the help!

Show 2 more comments

1 answer

-2

You can do it, you need to have an attribute of the DATE or DATETIME genre, which saves you the date the post was made, everything depends on how you have built the database

  • I have a date field, but I can’t imagine how to change this select to list only the last one that commented.

Browser other questions tagged mysql query select

You are not signed in. Login or sign up in order to post.