CODEWITHSUNDEEP
laravellaravel-5laravel-5.4laravel-query-builder
Jass
Jass

Reputation: 4115

Order by not working with db raw custom query

I have made a following query

$query = 'select cdo.id, cdo.user_id, cdo.created_at, cdo.updated_at, cdo.shipping_charges from certi_dia_orders cdo inner join users u on cdo.user_id = u.id where cdo.type = "Order"';

if(isset($_REQUEST['order-status']) && strtolower($_REQUEST['order-status']) == 'pending'){
        $query .= ' and cdo.status = "under process"';
      } else if(isset($_REQUEST['order-status']) && strtolower($_REQUEST['order-status']) == 'delivered'){
        $query .= ' and cdo.status = "delivered"';
      } else if(isset($_REQUEST['order-status']) && strtolower($_REQUEST['order-status']) == 'all'){

      } else {
        $query .= ' and cdo.status = "under process"';
      }

      if(isset($_REQUEST['buyer_username']) && $_REQUEST['buyer_username']!=''){
        $query .= ' and u.username like "%'.addslashes($_REQUEST['buyer_username']).'%"';
      }

      if(isset($_REQUEST['date_from']) && $_REQUEST['date_from']!='' && isset($_REQUEST['date_to']) && $_REQUEST['date_to']!='' && strtotime($_REQUEST['date_to']) > strtolower($_REQUEST['date_from'])){
        $query .= ' and (cdo.created_at between "'.addslashes($_REQUEST['date_from']).'" and "'.addslashes($_REQUEST['date_to']).'")';
      }

$order = DB::table(DB::raw("(".$query.") as tbl"))->orderBy('id','desc')->paginate(10);

But it does not return records in desending order. I have experienced same problem with my other projects as well.

I mean why orderby is not working as expected? It is showing records in accending order.

What could be problem and possible solutions?

Upvotes: 0

Views: 2440

Answers (2)

MohamedSabil83
MohamedSabil83

Reputation: 1559

As +marcin-nabiałek says, you have to write a valid syntax written in correct guide. Your code must written like this:

$order = DB::table('certi_dia_orders as cdo')
        ->leftJoin('users as u', 'cdo.user_id', '=', 'u.id')
        ->select(['cdo.id, cdo.user_id, cdo.status, cdo.created_at, cdo.updated_at, cdo.shipping_charges, u.id'])
        ->where('cdo.type', 'Order');

    if (isset($_REQUEST['order-status']) && strtolower($_REQUEST['order-status']) == 'pending') {
        $order->where('cdo.status', '=', 'under process');
    } else {
        if (isset($_REQUEST['order-status']) && strtolower($_REQUEST['order-status']) == 'delivered') {
            $order->where('cdo.status', '=', 'delivered');
        } else {
            if (isset($_REQUEST['order-status']) && strtolower($_REQUEST['order-status']) == 'all') {

            } else {
                $order->where('cdo.status', '=', 'under process');
            }
        }
    }

    if (isset($_REQUEST['buyer_username']) && $_REQUEST['buyer_username'] != '') {
        $order->where('u.username', 'like', '%' . addslashes($_REQUEST['buyer_username']) . '%');
    }

    if (isset($_REQUEST['date_from']) && $_REQUEST['date_from'] != '' && isset($_REQUEST['date_to']) && $_REQUEST['date_to'] != '' && strtotime($_REQUEST['date_to']) > strtolower($_REQUEST['date_from'])) {
        $order->whereBetween('cdo.created_at', [
            addslashes($_REQUEST['date_from']),
            addslashes($_REQUEST['date_to']),
        ]);
    }

    $orders = $order->orderBy('cdo.id', 'desc')->paginate(10);

another recommended thing, instead of $_REQUEST['variable'] use a laravel way request('variable')

Upvotes: 1

Marcin Nabiałek
Marcin Nabiałek

Reputation: 111859

This might be because you are using invalid syntax.

You should use:

 DB::table(...)->select(...)->orderBy('id','DESC')->paginate(10);

and not put raw expression inside DB::table

Reference

Upvotes: 0

Related Questions