can you write this query using orm in laravel: SELECT 
 `message\_id`, 
 `location\_id`, 
 `customer\_id`, 
 `from`, 
 `to`, 
 `decoded\_message`, 
 `schedule\_datetime`, 
 `status`, 
 `type`, 
 `dispatch\_datetime`, 
 `delivery\_datetime`, 
 `message\_status`, 
 `direction`, 
 `created\_time`, 
 `is\_archived` 
FROM `messages` 
USE INDEX (message\_id, location\_id, customer\_id)
WHERE EXISTS (
 SELECT \* 
 FROM `customers` 
 WHERE `messages`.`customer\_id` = `customers`.`customer\_id`
 AND (
 concat(first\_name," ",last\_name," ") REGEXP 'Esch'
 OR concat(last\_name," ",first\_name," ") REGEXP 'Esch'
 OR `email` REGEXP 'Esch'
 OR `phone\_number` REGEXP 'Esch'
 )
 AND `is\_archived` != 1
)
AND `is\_archived` IN (0, 2)
AND `message\_id` IN (
 SELECT MAX(message\_id) AS message\_id
 FROM `messages`
 WHERE `location\_id` IN ('467')
 AND `is\_archived` IN (0, 2)
 AND `customer\_id` IN (
 SELECT customer\_id
 FROM `customers`
 WHERE `is\_archived` != 1
 )
)
AND `location\_id` IN ('467')
AND `customer\_id` IS NOT NULL
AND `customer\_id` <> ''
ORDER BY `message\_status` DESC, `message\_id` DESC
LIMIT 10 OFFSET 0
user
you can use laravel eloquent orm to achieve the desired result. here's the equivalent query using eloquent orm:

```php
use illuminate\support\facades\db;

$query = db::table('messages')
    ->select([
        'message_id',
        'location_id',
        'customer_id',
        'from',
        'to',
        'decoded_message',
        'schedule_datetime',
        'status',
        'type',
        'dispatch_datetime',
        'delivery_datetime',
        'message_status',
        'direction',
        'created_time',
        'is_archived'
    ])
    ->whereexists(function ($subquery) {
        $subquery
            ->select(db::raw(1))
            ->from('customers')
            ->wherecolumn('messages.customer_id', 'customers.customer_id')
            ->where(function ($query) {
                $query
                    ->where(db::raw('concat(first_name," ",last_name," ")'), 'regexp', 'esch')
                    ->orwhere(db::raw('concat(last_name," ",first_name," ")'), 'regexp', 'esch')
                    ->orwhere('email', 'regexp', 'esch')
                    ->orwhere('phone_number', 'regexp', 'esch');
            })
            ->where('is_archived', '!=', 1);
    })
    ->wherein('is_archived', [0, 2])
    ->wherein('message_id', function ($subquery) {
        $subquery
            ->select(db::raw('max(message_id)'))
            ->from('messages')
            ->wherein('location_id', [467])
            ->wherein('is_archived', [0, 2])
            ->wherein('customer_id', function ($query) {
                $query
                    ->select('customer_id')
                    ->from('customers')
                    ->where('is_archived', '!=', 1);
            });
    })
    ->wherein('location_id', [467])
    ->wherenotnull('customer_id')
    ->where('customer_id', '<>', '')
    ->orderby('message_status', 'desc')
    ->orderby('message_id', 'desc')
    ->limit(10)
    ->offset(0)
    ->get();
```

please note that this query will give you a collection of result rows, and you can loop through it or perform other operations as needed.
assistant
You can use Laravel Eloquent ORM to achieve the desired result. Here's the equivalent query using Eloquent ORM:

```php
use Illuminate\Support\Facades\DB;

$query = DB::table('messages')
    ->select([
        'message_id',
        'location_id',
        'customer_id',
        'from',
        'to',
        'decoded_message',
        'schedule_datetime',
        'status',
        'type',
        'dispatch_datetime',
        'delivery_datetime',
        'message_status',
        'direction',
        'created_time',
        'is_archived'
    ])
    ->whereExists(function ($subquery) {
        $subquery
            ->select(DB::raw(1))
            ->from('customers')
            ->whereColumn('messages.customer_id', 'customers.customer_id')
            ->where(function ($query) {
                $query
                    ->where(DB::raw('concat(first_name," ",last_name," ")'), 'REGEXP', 'Esch')
                    ->orWhere(DB::raw('concat(last_name," ",first_name," ")'), 'REGEXP', 'Esch')
                    ->orWhere('email', 'REGEXP', 'Esch')
                    ->orWhere('phone_number', 'REGEXP', 'Esch');
            })
            ->where('is_archived', '!=', 1);
    })
    ->whereIn('is_archived', [0, 2])
    ->whereIn('message_id', function ($subquery) {
        $subquery
            ->select(DB::raw('MAX(message_id)'))
            ->from('messages')
            ->whereIn('location_id', [467])
            ->whereIn('is_archived', [0, 2])
            ->whereIn('customer_id', function ($query) {
                $query
                    ->select('customer_id')
                    ->from('customers')
                    ->where('is_archived', '!=', 1);
            });
    })
    ->whereIn('location_id', [467])
    ->whereNotNull('customer_id')
    ->where('customer_id', '<>', '')
    ->orderBy('message_status', 'DESC')
    ->orderBy('message_id', 'DESC')
    ->limit(10)
    ->offset(0)
    ->get();
```

Please note that this query will give you a Collection of result rows, and you can loop through it or perform other operations as needed.
assistant