在laravel中从一系列日期中获取行

Question

I am trying to fetch rows from database by applying a range of two dates. starting and ending

This below is a test data.

Collection {#258 ▼
  #items: array:4 [▼
    0 => Customer_history {#237 ▶}
    1 => Customer_history {#260 ▶}
    2 => Customer_history {#261 ▶}
    3 => Customer_history {#262 ▼
      #attributes: array:12 [▼
        "ch_id" => 1
        "customer_id" => 1
        "invoice_id" => 12
        "created_at" => "2017-07-17 22:57:55"
        "updated_at" => "2017-07-17 22:57:55"
        "branch_id" => 1
        "salesman_id" => 1
        "remarks" => ""
        "invoice_no" => ""
        "advance" => ""
        "is_delivered" => 1
        "is_paid" => 1
      ]
    }
  ]
}

As you see there is one two that has date 2017-07-17 Now when I applied query with two dates created

$from = date_create("2017-07-17");
$till = date_create("2017-07-17");

$customer_histories = Customer_history::leftJoin('invoices AS i', 'i.invoice_id', 'customer_histories.invoice_id')
        ->where('is_paid','=',1)
        ->whereBetween('customer_histories.created_at', [$from,$till])
        ->get();

This doesn't return any rows and it should have return 1 row! I guess I am missing out of something. Can anyone help me out what should be omitted or amended to make it work?

Answer 1

Try this. remove these lines

$from = date_create("2017-07-17");
$till = date_create("2017-07-17");

And put this query

$customer_histories = Customer_history::leftJoin('invoices AS i', 'i.invoice_id', 'customer_histories.invoice_id')
    ->where('is_paid','=',1)
    ->whereRaw('DATE_FORMAT(customer_histories.created_at, "%Y-%m-%d") BETWEEN ? AND ?', [ $from, $till])
    ->get();