0
Categoria Segmento
1 Atendimento
1 Tempo de espera pela peça
1 Preço
2 Atendimento
2 Negociação
2 Tempo de entrega do veículo
3 Atendimento
3 Preço
3 Serviço
I have a table where I save some data in that order above. If it is category 1, save those segments in that order.
I am showing this data on another page, but the data is not coming in the same order it is saved in the table. Follow the SELECT.
select
segmento,
categoria,
round(avg(dealer_rating_categories.rating)) as media
from
`dealer_ratings`
inner join
`dealer_rating_categories` on `dealer_rating_categories`.`id_avaliacao` = `dealer_ratings`.`id`
inner join
`categories` on `categories`.`id` = `dealer_rating_categories`.`id_categoria`
where
`dealer_ratings`.`deleted_at` is null
and
`dealer_ratings`.`id_concessionaria` = 1
and
`dealer_ratings`.`id_concessionaria` is not null
and
`dealer_rating_categories`.`id_categoria` in (1, 2, 3)
group by
`dealer_rating_categories`.`id_categoria`, `segmento`
order by
`categories`.`ordem`
Upshot
Categoria Segmento
Compra de Veículo Atendimento
Compra de Veículo Negociação
Compra de Veículo Tempo de entrega do veículo
Serviço Preço
Serviço Serviço
Serviço Atendimento
Peças Preço
Peças Atendimento
Peças Tempo de espera pela peça
The only one that is correct is the Vehicle Purchase category which is ID 2. The others, the segment comes out of order.
Just detailing:
I don’t have the segments registered separately in another linking table with the category table. I manually enter the code. Otherwise, I’d create a column ORDEM in this table and add a ORDER BY ORDEM in the SELECT and solve the problem.
But since that’s not the case, what can I do ?
You can reduce server performance and sort by string/varchar. I say,
ORDER BY "categories"."ordem", segmento– DH.
I will test, but I cannot sort by segment because they are not in alphabetical order. It is in insertion order.
– Diego Souza
I just tested and it’s already that way, because I’m using the Laravel.
– Diego Souza
This doesn’t make sense... even if bad practice sort by text, it should work this order by in 2 fields, in the form I made clear: first by ID/Order, then by text
– DH.
Yes, it does work. But when ordering through the first field, mix the second instead of showing by the insertion order, as it is in the first one in my question.
– Diego Souza
Oh right, I was wrong, pardon me. I now understand, you want to return in the same historical order that you entered. Because of how paging and search indexing works, I think it will never return in the same order you entered, I mean, not 100%. Without an auxiliary field there is no good solution for this... Maybe make a select for each id resolve... I’m not sure.
– DH.