MySQL result set ordered by fixed positions

Question

I've got following, simple table

Item (id, name, date, fixed_position)

(1, 'first entry', '2016-03-09 09:00:00', NULL)
(2, 'second entry', '2016-03-09 04:00:00', 1)
(3, 'third entry', '2016-03-09 05:00:00', NULL)
(4, 'fourth entry', '2016-03-09 19:00:00', NULL)
(5, 'fifth entry', '2016-03-09 13:00:00', 4)
(6, 'sixth entry', '2016-03-09 21:00:00', 2)

The number of items is not fixed, in fact can vary from ~100 to ~1000.

What i want to achieve is to perform a query to return set of Items ordered by date field which takes into consideration fixed_position field, which stands for something like "pinned" results to specific positions. If fixed_position for given entry is not NULL the result should be pinned to n-th position and if fixed_position is NULL the ORDER BY should take precedence.

Desired output of query for brighter explanation:

(2, 'second entry', '2016-03-09 04:00:00', 1)    // pinned to 1-st position
(6, 'sixth entry', '2016-03-09 21:00:00', 2)     // pinned to 2-nd position
(3, 'third entry', '2016-03-09 05:00:00', NULL)  // ORDER BY `date`
(5, 'fifth entry', '2016-03-09 13:00:00', 4)     // pinned to 4-th position
(1, 'first entry', '2016-03-09 09:00:00', NULL)  // ORDER BY `date`
(4, 'fourth entry', '2016-03-09 19:00:00', NULL) // ORDER BY `date`

I've tried solution posted in Ordering MySql results when having fixed position for some items but even with copy-paste method this doesn't seem to work at all.

What I've tried this far is this query:

SELECT
  @i := @i +1 AS iterator,
  t.*,
  COALESCE(t.fixed_position, @i) AS positionCalculated
FROM
  Item AS t,
  (
SELECT
  @i := 0
) AS foo
GROUP BY
  `id`
ORDER BY
  positionCalculated,
  `date` DESC

Which returns:

iterator | id | name        | date                | fixed_position | positionCalculated 
1          1    first entry   2016-03-09 09:00:00   NULL             1
2          2    second entry  2016-03-09 04:00:00   1                1
6          6    sixth entry   2016-03-09 21:00:00   2                2
3          3    third entry   2016-03-09 05:00:00   NULL             3
4          4    fourth entry  2016-03-09 19:00:00   NULL             4
5          5    fifth entry   2016-03-09 13:00:00   4                4

Does MySQL can perform such task or should I take backend approach and perform PHP's array_merge() on two result sets?

Answer 1

A brute force method to solve this would be to first create a tally table having an amount of rows bigger than the original table:

SELECT @rn := @rn + 1 AS rn
FROM (
   SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS t1
CROSS JOIN (   
   SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS t2
CROSS JOIN (SELECT @rn := 0) AS v

Then you can left join this table to a derived table containing all fixed positions of your original table:

SELECT Tally.rn
FROM (
   ... tally table query here 
) AS Tally
LEFT JOIN (
   SELECT fixed_position
   FROM Item
) AS t ON Tally.rn = t.fixed_position
WHERE t.t.fixed_position IS NULL

The above returns the to-be-filled missing order positions.

Demo here

You can now use the above query as yet another derived table joined to the original table to achieve the desired ordering:

SELECT id, name, `date`, fixed_position, Gaps.rn, 
       derived.seq, Gaps.seq
FROM (
  SELECT id, name, `date`, fixed_position,
         @seq1 := IF(fixed_position IS NULL, @seq1 + 1, @seq1) AS seq
  FROM Item     
  CROSS JOIN (SELECT @seq1 := 0) AS v
  ORDER BY `date`
 ) AS derived
LEFT JOIN ( 
    SELECT Tally.rn,
           @seq2 := @seq2 + 1 AS seq
    FROM (
      SELECT @rn := @rn + 1 AS rn
      FROM (
        SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS t1
      CROSS JOIN (   
        SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1) AS t2
      CROSS JOIN (SELECT @rn := 0) AS v
    ) AS Tally
    LEFT JOIN (
      SELECT fixed_position
      FROM Item
   ) AS t ON Tally.rn = t.fixed_position  
   CROSS JOIN (SELECT @seq2 := 0) AS v
   WHERE t.t.fixed_position IS NULL
   ORDER BY rn
 ) AS Gaps ON (derived.seq = Gaps.seq) AND (derived.fixed_position IS NULL)
 ORDER BY COALESCE(derived.fixed_position, Gaps.rn) 

Demo here