Laravel Question posted in
The official Laravel Documentation can be found here.

has and doesnotHave methods not exist on DB in Laravel

I need help with the DB query builder. I tried to check if such a relationship already exists but got an error saying Call to undefined method IlluminateDatabaseQueryBuilder::has()

   $startDate = Carbon::today()->format('Y-m-dT00:00:00');
    $endDate = Carbon::today()->format('Y-m-dT23:59:59');

    if (request('start_date') && request('end_date')) {
        $startDate = Carbon::parse(request('start_date') . 'T00:00:00');
        $endDate = Carbon::parse(request('end_date') . 'T23:59:59');
    } elseif (request('current_date')) {
        $startDate = Carbon::parse(request('current_date'))->format('Y-m-dT00:00:00');
        $endDate = Carbon::parse(request('current_date'))->format('Y-m-dT23:59:59');
    }

    $orders = DB::table(DB::raw("
            (SELECT
                DATE_FORMAT(orders.created_at, '%Y-%m') as month,
                COUNT(*) as total_orders,
                SUM(CASE WHEN delivery_statuses.status != 'confirmed' THEN 1 ELSE 0 END) as total_unconfirmed,
                SUM(CASE WHEN delivery_statuses.status = 'confirmed' THEN 1 ELSE 0 END) as total_confirmed,
                MIN(orders.created_at) as min_created_at
            FROM orders
            LEFT JOIN delivery_statuses ON orders.id = delivery_statuses.order_id
            WHERE orders.should_deliver = 'now'
            AND orders.status IN ('completed', 'approved', 'success')
            AND orders.stocked = false
            AND orders.created_at BETWEEN ? AND ?
            GROUP BY month
            ) as subquery
        "))
        ->has("customer")
        ->doesnotHave("logistics")
        ->addBinding($startDate)
        ->addBinding($endDate)
        ->orderBy('subquery.min_created_at', 'desc')
        ->get();

How can I replace has and doesnotHave methods? I appreciate any help you can provide.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    thanks for your answer. There are some errors so I corrected your answer:

            return Order::selectRaw("DATE_FORMAT(orders.created_at, '%Y-%m') as month,
            COUNT(*) as total_orders,
            SUM(CASE WHEN delivery_statuses.status != 'confirmed' THEN 1 ELSE 0 END) as total_unconfirmed,
            SUM(CASE WHEN delivery_statuses.status = 'confirmed' THEN 1 ELSE 0 END) as total_confirmed,
            MIN(orders.created_at) as min_created_at")
        ->leftJoin('delivery_statuses', 'orders.id', '=', 'delivery_statuses.order_id')
        ->where('should_deliver', 'now')
        ->whereIn('orders.status', ['completed', 'approved', 'success'])
        ->where('stocked', false)
        ->whereBetween("orders.created_at", [$startDate, $endDate])
        ->has('customer')
        ->doesntHave('logistics')
        ->orderBy('min_created_at', 'desc')
        ->groupBy('month')
        ->get();

    I appreciate your help.


  2. 0

    It appears customer and logistics are relationships of the Order model. So we have to write the DB query via the Order model in order to have access to the has and doesNotHave method.

    return Order::selectRaw('DATE_FORMAT(orders.created_at, '%Y-%m') as month,
                COUNT(*) as total_orders,
                SUM(CASE WHEN delivery_statuses.status != 'confirmed' THEN 1 ELSE 0 END) as total_unconfirmed,
                SUM(CASE WHEN delivery_statuses.status = 'confirmed' THEN 1 ELSE 0 END) as total_confirmed,
                MIN(orders.created_at) as min_created_at')
    ->leftJoin('delivery_statuses', 'orders.id', '=', 'delivery_statuses.order_id')
    ->where('should_deliver', 'now')
    ->whereIn('status', ['completed', 'approved', 'success'])
    ->where('stocked', false)
    ->whereBetween($startDate, $endDate)
    ->has("customer")
    ->doesnotHave("logistics")
    ->orderBy('subquery.min_created_at', 'desc')
    ->groupBy('month')
    ->get();

    And it is also more readable this way.

    Pls let me know if this solves your problem. If not, I can certainly translate that has and doesNotHave functionalities to raw queries that can be part of your DB query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search