Can you use the same query and set two different "ORDER BY"?

Question

Can you use the same query and set two different "ORDER BY"?

Navigation

#1 by (3 votes)
#2 by (1 votes)

2

I use the following excerpt for one queried to the database

$cs1 = $pdo->query("SELECT TITULO, URL, IMAGEM , TEMPO FROM post ORDER BY CLIQUES DESC LIMIT 5")->fetchAll();

I would then like to explore this query further in order to use as little memory as possible.

This is the following on the home page of the site I'm using two different queries

$cs1 = $pdo->query("SELECT TITULO, URL, IMAGEM , TEMPO FROM post ORDER BY CLIQUES DESC LIMIT 5")->fetchAll();

$cs2 = $pdo->query("SELECT TITULO, URL, IMAGEM , TEMPO FROM post ORDER BY VOTOS DESC LIMIT 5")->fetchAll();

As you can see, the only thing that changes between queries is the column quoted in ORDER BY , where in one the cited column of ORDER BY is CLIQUES and in other VOTOS .

In my view this is a waste of resources, since both queries are displayed on the same page. Is there any way to make a single query yet determine different columns in ORDER BY ? Something like this:

<?php 
    $colunas = ID, VOTOS, CLIQUES, CATEGORIA;

    $csu = $pdo->query("SELECT TITULO, URL, IMAGEM , TEMPO FROM post ORDER BY $colunas DESC LIMIT 5")->fetchAll();

?>

Alike type, every time you reach the limit of 5, skip to next term quoted in the variable $colunas

php mysql query

asked by anonymous 08.12.2014 / 00:05

2 answers

1

Well ivan veloso, on the question of resources, to do these queries separately, will not be so costly, since MySQL works with Query Cache and in this way, before the queries are executed, they will be compared with the results stored in the cache , and if the result already exists a new query is not necessary. For you to verify that this functionality is enabled in your MySQL you can execute the command:

SHOW VARIABLES LIKE 'have_query_cache';

As for your issue, if you just want to make a return, for your various queries with different sorts of things, you can use the UNION ALL feature. However the return will be grouped and you will have to treat it in PHP. Your query would look something like this:

(SELECT TITULO, URL, IMAGEM , TEMPO FROM post ORDER BY CLIQUES DESC LIMIT 5) as postcliques
UNION ALL
(SELECT TITULO, URL, IMAGEM , TEMPO FROM post ORDER BY VOTOS DESC LIMIT 5) as postvotos

In this case, if you have more than 5 records in the post table, the return will be 10 records, the first 5 sorted by the clicks and the last 5 sorted by the votes.

08.12.2014 / 02:02

When to use In or%? Error function not defined

score 3 · Accepted Answer

I see two possible workarounds for this problem:

Continue using two distinct queries and create indexes in columns CLIQUES and VOTOS . When using indexes your database manager system will bring both queries faster.

Bring all resultset and sort with PHP. That way you do not have to create the indexes and you can always have the same query.

Sorting by column CLIQUES :

usort($resultset, function($a, $b) {
    return $a['CLIQUES'] > $b['CLIQUES'];
});

Sorting by column VOTOS :

usort($resultset, function($a, $b) {
    return $a['VOTOS'] > $b['VOTOS'];
});