Mysql custom sort

Question

I have a database table containing different category which contains different products and each category having some priority. Suppose cat-1 have five products, cat-2 contains 3 products, cat-3 contains 3 products and cat-4 contains 2 products.

While displaying the products, the order should be as follows.

If categories have same priority (suppose cat-1, cat-2 priority = 1, cat-3 priority = 2, cat-4 priority = NULL), then products will display as follow.

c1p1, c2p1, c1p2, c2p2, c1p3, c2p3, c1p4, c1p5, c3p1, c3p2, c3p3, c4p1, c4p2.

If categories have same priority (suppose cat-1, cat-2 priority = 1, cat-3 and cat-4 priority = 2), then products will display as follow.

c1p1, c2p1, c1p2, c2p2, c1p3, c2p3, c1p4, c1p5, c3p1, c4p1, c3p2, c4p2, c3p3.

If categories have different priority (suppose cat-1 priority = 2, cat-2 priority = 1, cat-3 priority = 3 and cat-4 priority = Null), then products will display as follow.

c2p1, c2p2, c2p3, c1p1, c1p2, c1p3, c1p4, c1p5, c3p1, c3p2, c3p3, c4p1, c4p2.

Here c = category and p = product.

Can this type of sorting is possible in Mysql. Please help.

Here is the structure and sample data of the database tables-

   CREATE TABLE IF NOT EXISTS `categories` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `priority` int(11) DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ;

   INSERT INTO `categories` (`id`, `name`, `priority`) VALUES
   (1, 'c1', 1),
   (2, 'c2', 1),
   (3, 'c3', 2),
   (4, 'c4', NULL);

   CREATE TABLE IF NOT EXISTS `products` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `category_id` int(11) NOT NULL,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
   ) ;

INSERT INTO `products` (`id`, `category_id`, `name`) VALUES
(1, 1, 'c1p1'),
(2, 1, 'c1p2'),
(3, 1, 'c1p3'),
(4, 1, 'c1p4'),
(5, 1, 'c1p5'),
(6, 2, 'c2p1'),
(7, 2, 'c2p2'),
(8, 2, 'c2p3'),
(9, 3, 'c3p1'),
(10, 3, 'c3p2'),
(11, 3, 'c3p3'),
(12, 4, 'c4p1'),
(13, 4, 'c4p2');

Answer 1

This is an indirect solution to get the sequence as per the requirement.

As i was using PHP to fetch data, first i wrote an sql query to fetch the data according to priority.

SELECT `Category`.`id`, `Category`.`name`, `Category`.`priority` , `Product`.`name`, `Product`.`category_id` FROM `categories` AS `Category` INNER JOIN `products` AS `Product` WHERE `Category`.`id` = `Product`.`category_id` ORDER BY CASE WHEN `Category`.`priority` IS NULL THEN 1 ELSE 0 END ASC, `Category`.`priority` ASC;

Then i did a simple array manipulation to get an array as follow.

array(
(int) 1 => array(
    (int) 0 => array(
        (int) 0 => '1',
        (int) 1 => '6'
    ),
    (int) 1 => array(
        (int) 0 => '2',
        (int) 1 => '7'
    ),
    (int) 2 => array(
        (int) 0 => '3',
        (int) 1 => '8'
    ),
    (int) 3 => array(
        (int) 0 => '4'
    ),
    (int) 4 => array(
        (int) 0 => '5'
    ),
),  
(int) 2 => array(
    (int) 0 => array(
        (int) 0 => '9'
    ),
    (int) 1 => array(
        (int) 0 => '10'
    ),
    (int) 2 => array(
        (int) 0 => '11'
    ),      
),
'' => array(
    (int) 0 => array(
        (int) 0 => '12'         
    ),
    (int) 1 => array(
        (int) 0 => '13'

    )
)
)

for series c1p1, c2p1, c1p2, c2p2, c1p3, c2p3, c1p4, c1p5, c3p1, c3p2, c3p3, c4p1, c4p2. Where array keys are priority and leaf values are product ids.

Then i make a string of ids from the above array as follows.

  1,6,2,7,3,8,4,5,9,10,11,12,13

And finally wrote the query as follows.

 SELECT * 
 FROM  `products` 
 WHERE  `id` 
 IN ( 1, 6, 2, 7, 3, 8, 4, 5, 9, 10, 11, 12, 13 ) 
 ORDER BY FIELD( id, 1, 6, 2, 7, 3, 8, 4, 5, 9, 10, 11, 12, 13 );

Answer 2

Finally i got a better solution. I added an extra field in the products table. The motive is to give a serial number to each category products. My final products table structure look as follows.

CREATE TABLE IF NOT EXISTS `products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`sl_no` int(11) NOT NULL,
 PRIMARY KEY (`id`)
);

INSERT INTO `products` (`id`, `category_id`, `name`, `sl_no`) VALUES
(1, 1, 'c1p1', 1),
(2, 1, 'c1p2', 2),
(3, 1, 'c1p3', 3),
(4, 1, 'c1p4', 4),
(5, 1, 'c1p5', 5),
(6, 2, 'c2p1', 1),
(7, 2, 'c2p2', 2),
(8, 2, 'c2p3', 3),
(9, 3, 'c3p1', 1),
(10, 3, 'c3p2', 2),
(11, 3, 'c3p3', 3),
(12, 4, 'c4p1', 1),
(13, 4, 'c4p2', 2);

And the query looks like-

SELECT  `Category`.`id` ,  `Category`.`name` ,  `Category`.`priority` ,    `Product`.`name` ,  `Product`.`category_id` 
FROM  `categories` AS  `Category` 
INNER JOIN  `products` AS  `Product` 
WHERE  `Category`.`id` =  `Product`.`category_id` 
ORDER BY CASE WHEN  `Category`.`priority` IS NULL 
THEN 1 
ELSE 0 
END ASC ,  `Category`.`priority` ASC ,  `Product`.`sl_no` ,  `Product`.`category_id`;

Answer 3

You could create a table with priorities:

create table priorities (
  category varchar(255),
  priority int null);

Then you can select products this way:

select
  products.*
from
  products inner join priorities
  on products.category = priorities.category
order by
  priorities.priority is null, -- this to put null values at the end
  priorities.priority,
  products.id                  -- or some other field

EDIT: This probably is what you are looking for:

select
  products.name
from
  products inner join categories
  on products.category_id = categories.id
order by
  categories.priority is null,
  categories.priority,
  substr(products.name,3),
  categories.name