CODEWITHSUNDEEP
phpmysqlcodeigniter
Sonu Jangra
Sonu Jangra

Reputation: 69

mysql one field by different priority ordering

I have a table which having a pay_mode field in which SUBSCRIPTION,ONETIME,FREE are entries, So i want to fetch the record on SUBSCRIPTION,ONETIME by random order and the FREE listing are on descending order(NOT BY RANDOM) by date. 

   $this->db->select("p.*", FALSE);                  
   $this->db->from($this->table_classified." p");
   $this->db->where('p.is_active', '1');
   $this->db->where('p.is_deleted', 'NO');

   $this->db->order_by("(CASE `pay_mode`
            WHEN 'RECURRING' THEN 3
            WHEN 'ONETIME' THEN 2
            WHEN 'FREE' THEN 1
         END)",'DESC');

   $this->db->order_by('pay_mode', 'RANDOM');
   $query    = $this->db->get();

Please find the schema here

Upvotes: 2

Views: 704

Answers (3)

Tirthraj Singh Bartwal
Tirthraj Singh Bartwal

Reputation: 329

Use this code and set your order by asc,desc or RANDOM

$this->db->select("p.*, (CASE `pay_mode` WHEN 'RECURRING' THEN 3 WHEN 'ONETIME' THEN 2 WHEN 'FREE' THEN 1 END) as pay_mode_no");</p>

$this->db->from('classified as p');

$this->db->where('p.is_active', '1');
$this->db->where('p.is_deleted', 'NO');

$this->db->order_by('pay_mode_no', 'ASC');
$this->db->get()->result();

Upvotes: 0

truesource
truesource

Reputation: 397

You can write a custom query on codeigniter and you can fetch recurring and onetime by random then use union and fetch free listing by desc order. 

 $query = "(SELECT * 
     FROM   (SELECT nc_classified.*, 
                    nc_classified_category.title_zh AS cat_title_zh, 
                    nc_classified_category.title    AS cat_title 
             FROM   `nc_classified` 
                    LEFT JOIN `nc_classified_category` 
                           ON nc_classified.cat_id = nc_classified_category.id 
             WHERE  ( $where ) 
                    AND `pay_mode` IN( 'RECURRING', 'ONETIME' ) 
             ORDER  BY ( CASE `pay_mode` 
                           WHEN 'RECURRING' THEN 1 
                           WHEN 'ONETIME' THEN 2 
                         end ), 
                       Rand()) a) 
    UNION 
    (SELECT * 
     FROM   (SELECT nc_classified.*, 
                    nc_classified_category.title_zh AS cat_title_zh, 
                    nc_classified_category.title    AS cat_title 
             FROM   `nc_classified` 
                    LEFT JOIN `nc_classified_category` 
                           ON nc_classified.cat_id = nc_classified_category.id 
             WHERE  ( $where ) 
                    AND `pay_mode` IN( 'FREE' ) 
             ORDER  BY id DESC) a) ";

   $cQuery = $this->db->query($query);

Upvotes: 1

LeGEC
LeGEC

Reputation: 51988

Your pay_mode column is an enum, sorting by pay_mode will work straight away :

$this->db->order_by('pay_mode', 'DESC'); // RECURRING -> ONETIME -> FREE

$this->db->order_by('pay_mode', 'ASC'); // FREE -> ONETIME -> RECURRING

For the randomization part, I would advise you to do this in your PHP code (see for example shuffle).

Upvotes: 0

Related Questions