CODEWITHSUNDEEP
mysql
Andrei Stanca
Andrei Stanca

Reputation: 908

Ordering MySql results when having fixed position for some items

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 the Items ordered by the order specified in CollectionsPositions for Collection 1, but the problem is that I don't have positions for all the items and cannot arrange with ORDER BY having NULL values for some of the items.

I was trying 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

That 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 realise that I have to make a new column and order by that column, but how do I handle the already defined positions when I generate the new column for ordering?

Upvotes: 0

Views: 157

Answers (1)

Stephan
Stephan

Reputation: 8090

Try this (i've tested it on local 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

Upvotes: 2

Related Questions