Help please build request

I do the designer of forms.

There are three tables (fields stores possible inputs, fieldset - a group of inputs, field_type - possible types of inputs)

fields

  • id
  • field_type_id
  • title
  • required
  • visibility
  • values
  • order
  • belong
  • fieldset_id
  • tooltip

fieldset

  • id
  • title
  • belong
  • order

field_type

  • id
  • name

I would like to make a sample so that the result was:

  • FIELSET 1
  • input 1
  • input 2
  • input 3
  • FIELSET 2
  • input 4

  • input 5

  • input n (which without fieldset)

I make the following request

SELECT a.*, b.name AS field_name, c.id AS fieldset_id, c.title AS fieldset_title, c.order AS fieldset_order FROM fields a LEFT JOIN field_type b ON b.id = a.field_type_id LEFT JOIN fieldset c ON c.id = a.fieldset_id WHERE a.belong = 'employer' ORDER BY a.order, fieldset_order

It turns out to sort either by fieldset order, or by field order, as a result, the input goes out of order, or the fieldset is not in that order

    1 answer 1

    Try through UNION ALL :

     SELECT fl.fieldset_id, 1, fl.title FROM fields fl UNION ALL SELECT fs.id, 0, fs.title FROM fieldset fs ORDER BY 1, 2, 3

    The code showed only two columns. The rest - on their own. In each query, you can use all other constructions such as JOIN and WHERE . The main thing, when using UNION - should coincide the number of columns of both queries.

    Sorting refers to the whole result.