How to customize the order of the results in MYSQL

Question

How to customize the order of the results in MYSQL

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

I have a table where I keep the post of a forum I did with PHP and Mysql:

CREATE TABLE 'sala_muro_temas' (
  'Id' int(11) NOT NULL AUTO_INCREMENT,
  'categoria' int(11) DEFAULT NULL,
  'id_clase' bigint(20) DEFAULT NULL,
  'id_autor' bigint(20) unsigned NOT NULL DEFAULT '0',
  'autor' varchar(100) DEFAULT NULL,
  'titulo' text,
  'descripcion' text,
  'imagenes' int(2) DEFAULT NULL,
  'fecha' varchar(10) DEFAULT NULL,
  'last_update' varchar(10) DEFAULT NULL,
  'borrado' varchar(1) NOT NULL DEFAULT '',
  'sala' varchar(32) DEFAULT NULL,
  'ejercicio' tinyint(1) DEFAULT NULL,
  PRIMARY KEY ('Id')
)

Now I want to add the function so that you can post the posts that the user chooses above all and that are displayed first. To do this I have created a table where I save the id of the user and the id of the posted post:

CREATE TABLE 'sala_muro_temas_fijados' (
  'id_usuario' bigint(1) DEFAULT NULL,
  'id_tema' int(11) DEFAULT NULL
)

My question is how I perform the SQL query so that the posts saved by the user are displayed first and then the rest. I hope you can guide me. Thanks !!

mysql sql php

asked by Patricio 12.10.2018 в 22:46

source

2 answers

DJANGO DRF, Serialize related models Cancel button of a Login C # form

score 2 · Answer 1

Taking Rostan's answer as a reference, we are going to change some things.

The first consultation with the user's topics.

SELECT
     b.*
     2 campoOrdenamiento 
FROM muro_temas_fijados AS a
INNER JOIN muro_temas AS b ON (A.id_tema = B.Id)
WHERE A.id_usuario = @Tu_variable_usuario)

Then perform the union of the two consultations

 UNION

For topics that are not fixed, we will use this code to take into account all post not fixed.

 SELECT
     b.*
     1  campoOrdenamiento
 FROM muro_temas AS B
 WHERE ID NOT IN (SELECT ID_TEMA FROM muro_temas_fijados WHERE A.id_usuario = @Tu_variable_usuario)

Finally we put the order

ORDER BY campoOrdenamiento, ID DESC

This to order you by the last id set and also this the order id not set. I hope this works for you

score 0 · Answer 2

As you need to first get the user's topics and then the rest, I would recommend doing a UNION of two queries, as follows:

-- La primera consulta con los temas del usuario.
(SELECT* 
FROM muro_temas AS B
INNER JOIN muro_temas_fijados AS A
    ON (A.id_usuario = B.id_autor
        AND A.id_tema = B.Id)
WHERE A.id_usuario = @Tu_variable_usuario)

UNION -- Union de ambas consultas

-- Los temas que no son del usuario
(SELECT* 
FROM muro_temas AS B
INNER JOIN muro_temas_fijados AS A
    ON (A.id_usuario = B.id_autor
        AND A.id_tema = B.Id)
WHERE A.id_usuario <> @Tu_variable_usuario)

I hope my answer will be of your help. Greetings.