CODEWITHSUNDEEP
mysqlsql-order-by
bhttoan
bhttoan

Reputation: 2736

MySQL order once by field value from join then by non joined column

I have 2 tables - one contains meta data about a form and the other contains the fields themselves. In the metadata table there is an option to choose which field gets displayed first and then within the fields table there is an ordering column which displays the ordering for the remaining fields.

I need to be able to sort a query so it shows the value of the joined metadata field first and then by the ordering column.

For example:

forms
id sort_field
1  6
2  0

form_fields
id      form_id     name      ordering
1       2           field1    1
2       2           field2    3
3       2           field3    2
4       1           fielda    3
5       1           fieldd    2
6       1           fieldc    4
7       1           fieldb    1

My current query is:

select ff.name 
from form_fields ff 
     left join forms f on ff.form_id=f.id 
order by f.sort_field DESC, ff.ordering ASC

This works for form 2 as it doesn't have any sorting in forms - it doesn't work for form 1 as, using the join, sorting_field is always 6 hence it sorts as if there was no value in sorting_field.

My desired output for form 1 would be:

fieldc
fieldb
fieldd
fielda

Is it possible to sort by sort_field once in effect if it is set and then by the remaining ordering fields as normal?

Upvotes: 0

Views: 116

Answers (2)

Darshan Mehta
Darshan Mehta

Reputation: 30809

You can do it using a UNION query, e.g.:

(SELECT name, 0 as ordering
FROM form 
WHERE id = (SELECT sort_field FROM form_fields where id = 1)
AND form_id = 1)

UNION

(SELECT name, ordering
FROM form
WHERE form_id = 1
AND id != (SELECT sort_field FROM form_fields where id = 1))
ORDER BY ordering

Here's the SQL Fiddle.

Upvotes: 1

McNets
McNets

Reputation: 10807

You can set a new_order field.

select name
from
     (select ff.name
            case when id = f.sort_field then 0 else ff.ordering end new_order
      from form_fields ff 
           left join forms f on ff.form_id=f.id
     ) t 
order by t.new_order

Upvotes: 1

Related Questions