Order_by Issue on joined Tables

Question

My Query is this I want the records order by average rating but it's not getting records in descending order

$this->db->select('doctors.* , speciality.speciality_name , avg(rating.rating) as avg_rating');
        $this->db->from('doctors');
        $this->db->join('speciality' , 'doctors.speciality_id = speciality.id' , 'left');
        $this->db->join('rating' , 'doctors.id = rating.doctor_id' , 'left');
        $this->db->group_by('doctors.id');
        $this->db->order_by('avg_rating' , 'desc');

I am getting records in this order according to avg_rating

[avg_rating] => 4.0000

[avg_rating] => 4.0000

[avg_rating] => 4.5000

[avg_rating] => 3.0000

but I want 4.5 on top

View file code where I am showing this data

<ul class="selectedList">
                    <?php if(isset($doctors_data) && !empty($doctors_data)){ foreach($doctors_data as $result){ ?>
                    <li>
                        <div class="row noMargin">
                            <div class="col-lg-2 col-md-2 col-sm-6 col-xs-5 first">
                                <a href="<?php echo base_url(); ?>home_ctrl/detail_page?refer_id=<?php echo $result->id;?>">
                                    <?php if(isset($result->img_name) && !empty($result->img_name)){ ?>
                                    <img src="<?php  echo base_url(); ?>includes/images/doctors/<?php echo $result->img_name; ?>" alt="Best Lady doctors" class="img-responsive doctorImg">
                                    <?php }else{ ?>
                                    <img src="<?php  echo base_url(); ?>includes/images/doctor.jpg" alt="Best Lady doctors" class="img-responsive doctorImg">   
                                    <?php } ?>
                                </a>
                            </div>
                            <div class="col-lg-3 col-md-3 col-sm-6 col-xs-7 col-md-push-7 third noPadding">
                                <p class="text-right stars" id="<?php echo $result->id; ?>" data-rating="<?php echo round($result->avg_rating); ?>"></p>
                                <p><span class="fa fa-phone tdinnerSpan"></span> <?php echo $result->phone; ?></p>
                                <span class="fa  fa-clock-o tdinnerSpan"></span><p style="width:auto !important" class="innerPara"> <?php echo $result->week_from; ?> - <?php echo $result->week_to; ?> <br> <?php echo date('h:i a', strtotime($result->opening)); ?> - <?php echo date('h:i a', strtotime($result->closing)); ?> </p>
                            </div>
                            <div class="col-lg-7 col-md-7 col-sm-12 col-xs-12 col-md-pull-3 second">
                                <a href="<?php echo base_url(); ?>home_ctrl/detail_page?refer_id=<?php echo $result->id;?>" class="doctorName"><?php echo $result->name; ?>, <small><?php echo $result->education; ?></small></a>
                                <p><span class="fa fa-certificate tdinnerSpan"></span><?php echo $result->speciality_name; ?></p>
                                <span class="fa fa-map-marker tdinnerSpan"></span><p class="innerPara addresPara"><?php echo $result->address; ?></p>
                            </div>
                        </div>
                    </li>
                    <?php } } ?>
                </ul>

I am not getting error but record is getting in simple order according to id like 13, 14 , 15 but I should be according to average rating — Sikandar_ali, Jul 18 '17 at 06:26
Your code looks good, Are you using any framework like `Datatables` or etc..? — Alex Mac, Jul 18 '17 at 06:27
No I am not using `datatables` here I am just showing data in the front end like simply just in paragraphs and and rating in stars — Sikandar_ali, Jul 18 '17 at 06:31
This might be the answer of your problem: https://stackoverflow.com/questions/44865156/mysql-order-by-avg-desc-not-working-when-certain-columns-are-included/45028267?noredirect=1#comment77045922_45028267 — Manoj-kr, Jul 18 '17 at 06:34
@money I am getting data but not in the order in which I want Query is correct — Sikandar_ali, Jul 18 '17 at 06:46
@Sikandar_ali, Please update table structure in OP with some sample data if above post didn't help. — Manoj-kr, Jul 18 '17 at 06:51
print last query and you can find actual query which compiled by query builder where you can find what actual query is fired on database — Anil, Jul 18 '17 at 12:09
Are you try this? `$this->db->select('doctors.* , speciality.speciality_name , avg(rating.rating) as rating.avg_rating');` and then order_by: `$this->db->order_by('rating.avg_rating' , 'desc');` — Vương Nguyễn Minh, Jul 18 '17 at 19:15

Alex Mac · Answer 1 · 2017-07-18T07:21:27.910

0

Please change your query like below mentioned.

$this->db->select('doctors.* , speciality.speciality_name , CAST(avg(package_rating.rating) as DECIMAL(3,2)) as avg_rating');
$this->db->from('doctors');
$this->db->join('speciality', 'doctors.speciality_id = speciality.id', 'left');
$this->db->join('rating', 'doctors.id = rating.doctor_id', 'left');
$this->db->order_by('avg_rating', 'desc');
$this->db->group_by('doctors.id');

I have tried this in my local machine and it working perfect.

$this->db->select('package.* , CAST(avg(package_rating.rating) as DECIMAL(3,2)) as avg_rating');
$this->db->from('package');
$this->db->join('package_rating', 'package.id = package_rating.package_id','left');
$this->db->order_by('avg_rating', 'desc');
$this->db->group_by('package.id');
$data = $this->db->get();

Let me know if it not works.

edited Jul 18 '17 at 07:21

answered Jul 18 '17 at 06:58

Alex Mac

2,970
1
22
39

Group By after Order By ? – Manoj-kr Jul 18 '17 at 07:15
@money :Just for your reference It's Query builder, sequence dosen't matter. When Codeigniter generates query it will automatically add in sequence. – Alex Mac Jul 18 '17 at 07:17
Ok, have no idea about Codeigniter . Thanks ;) – Manoj-kr Jul 18 '17 at 07:19
If you write native mysql query in console than it will not work but in Query Builder you are just assigning values to particular clauses. – Alex Mac Jul 18 '17 at 07:19
@Sikandar_ali : Just for your reference I have added my query in answer. – Alex Mac Jul 18 '17 at 07:22
this is my controller `$data['doctors_data'] = $this->Dashboard_model->get_doctors_data(4);` – Sikandar_ali Jul 18 '17 at 07:27

score 0 · Answer 2 · answered Jul 18 '17 at 08:09

0

Try this

$this->db->select('doctors.* , speciality.speciality_name , avg(rating.rating) as avg_rating');
$this->db->from('doctors');
$this->db->join('speciality' , 'doctors.speciality_id = speciality.id' , 'left');
$this->db->join('rating' , 'doctors.id = rating.doctor_id' , 'left');
$this->db->order_by('avg_rating', 'desc'); # or this $this->db->order_by('rating.rating', 'desc'); 
$this->db->group_by('doctors.id');
$query = $this->db->get();
$result = $query->result(); # or $query->result_array();

answered Jul 18 '17 at 08:09

Abdulla Nilam

36,589
17
64
85

I found the problem but not the solution.I changed my query `$this->db->select('doctors.* , avg(rating.rating) as avg_rating');` but same issue when I remove * and start finding with column names it is working fine but when I write my one column whose **DATATYPE = TEXT** then it's creating issue. I think issue is due to **DATATYPE = TEXT** when i change it to varchar it's working good but I want all my data without changing the datatype – Sikandar_ali Jul 24 '17 at 10:51

Order_by Issue on joined Tables

2 Answers2