Order MySql results at a fixed position for some items

Question

I have the following tables:

Collections ( 'id', 'name') (1, 'one collection') ItemToCollection ( 'item_id', 'collection_id') (1,1), (2,1), (3,1), ...etc Items ( 'id', 'name' ) (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five'), (6, 'six'), (7, 'seven') CollectionsPositions ( 'col_id', 'item_id', 'pos' ) (1, 2, 2), (1, 4, 7), (1, 1, 4) 

I want to return items ordered in the order specified in CollectionsPositions for Collection 1, but the problem is that I don’t have positions for all elements, and I can’t organize ORDER BY, having NULL values ​​for some elements.

I tried something like this:

 SELECT FROM ItemToCollection JOIN Item ON ItemToCollection.item_id = Item.id LEFT JOIN CollectionsPositions ON col_id = 1 AND item_id = ItemToCollection.item_id WHERE ItemToCollection.collection_id = 1 

This will return me something like this: column 'pos':

 NULL, NULL, NULL, NULL, NULL, 2, 4, 7 

How can I order the items in the following order :?

 NULL, 2, NULL, 4, NULL, NULL, 7 

I understand that I need to make a new column and sort by this column, but how do I handle already certain positions when I create a new column for an order?

Answer 1

Try it (I tested it on the local computer and it works):

 SELECT tmp.pos FROM ( SELECT CollectionsPositions.pos, IF( CollectionsPositions.pos IS NULL , 0, @c := CollectionsPositions.pos ) FROM ItemToCollection JOIN Item ON ItemToCollection.item_id = Item.id LEFT JOIN CollectionsPositions ON col_id = 1 AND item_id = ItemToCollection.item_id WHERE ItemToCollection.collection_id = 1 ORDER BY IFNULL(CollectionsPositions.pos,@c) ) as tmp