PHP run a raw SQL query in eloquent model

Question

I'm trying to use Eloquent ORM from Laravel in my own legacy project. I have the models set-up and Eloquent is working fine but I can't understand how to run a raw SQL query using the eloquent database connection inside the model. I need to run raw SQL queries for now until I can refactor the entire code base over to use the ORM.

namespace App\Providers;

use Illuminate\Database\Capsule\Manager;
use League\Container\ServiceProvider\AbstractServiceProvider;

class DatabaseServiceProvider extends AbstractServiceProvider
{

    protected $provides = [
        Manager::class
    ];

    public function register()
    {

        $container = $this->getContainer();

        $config = $container->get('config');

        $config = $config->get('db.mysql');

        $capsule = new Manager;

        $capsule->addConnection([
            $config
        ]);

        $capsule->setAsGlobal();
        $capsule->bootEloquent();

        $container->share(Manager::class, function () use ($capsule){
            return $capsule;
        });

    }

}

Now in my model I have the following:

namespace App\Models;

use Illuminate\Database\Eloquent\Model as Eloquent;

class User extends Eloquent
{
    protected $table = '_users';
}

Then I'm trying to call that model in my controller:

namespace App\Controllers;

use App\Views\View;
use App\Models\User;
use Psr\Http\Message\RequestInterface;
use Psr\Http\Message\ResponseInterface;

class HomeController
{

    protected $view;

    public function __construct(View $view)
    {
        $this->view = $view;
    }

    public function index(RequestInterface $request, ResponseInterface $response)
    {
        $user = User::find(1);

        dump($user);

    }
}

So this all works nicely but I can't figure out how to run a raw query inside of the model? Obviously the below won't work but I want something like that, creating a new function and it returns the result:

Model:

public function customQuery()
{
    $query = 'SELECT * FROM _users';
    return $query;
}

Controller:

$user = new User;
$user->customQuery();

@JonasStaudenmeir there is around 8000 different queries in this legacy app so it't not visible to rewrite them all into an Eloquent format straight away, it will have to be done over time. So being able to drop in the current queries is a most at the moment. — twigg
– twigg, Commented Jul 13, 2018 at 13:22

Piterden · Accepted Answer · 2018-07-15 20:33:58Z

9

Laravel makes interacting with databases extremely simple across a variety of database backends using either raw SQL, the fluent query builder, and the Eloquent ORM.

All three has there own standard syntax format.

Recommendation : You should not create raw SQL inside model instead you can create even in controller (Though its recommended to follow repository pattern) but as a beginner following step will give you your expected result

Update your HomeController for following

Use the Illuminate\Support\Facades\DB;

Update the index method

public function index()
{
    $users = DB::select('SELECT * FROM users');

    dd($users);
}

Reference : https://laravel.com/docs/5.6/database#running-queries

Laravel is great framework. Keep learning !!

edited Jul 15, 2018 at 20:33

Piterden

7995 silver badges17 bronze badges

answered Jul 13, 2018 at 12:06

Amitesh Bharti

16k6 gold badges73 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

twigg Over a year ago

Please note i'm not using Laravel just the Eloquent package. I get the following error when trying your solution 'RuntimeException A facade root has not been set' where do you set the facade root?

Jonas Staudenmeir Over a year ago

Replace DB with Illuminate\Database\Capsule\Manager.

Amitesh Bharti Over a year ago

Thanks Jonas or add namespace : use Illuminate\Database\Capsule\Manager as DB;

Piterden Over a year ago

Exactly like I did github.com/Piterden/backup_manager-module/blob/1.0/src/Dump/…

Vipul · Accepted Answer · 2018-09-11 06:59:11Z

2

DB::raw() is used to make arbitrary SQL commands which aren't parsed any further by the query builder. Check this ref. link, with more details: http://fideloper.com/laravel-raw-queries

Example of \DB::raw and \DB::select

edited Sep 11, 2018 at 6:59

answered Jul 13, 2018 at 11:55

Vipul

9317 silver badges16 bronze badges

2 Comments

twigg Over a year ago

I'm guessing I need to add some sort of facade to my model in order to get this functionality?

Vipul Over a year ago

try by using namespace use \DB.

mehlichmeyer · Accepted Answer · 2018-07-13 11:52:08Z

0

try the whereRaw() function

https://laravel.com/docs/5.6/queries

From Documentation:

The whereRaw and orWhereRaw methods can be used to inject a raw where clause into your query. These methods accept an optional array of bindings as their second argument:

$orders = DB::table('orders') ->whereRaw('price > IF(state = "TX", ?, 100)', [200]) ->get();

answered Jul 13, 2018 at 11:52

mehlichmeyer

1543 silver badges18 bronze badges

3 Comments

twigg Over a year ago

So I need to include the DB Facades into my models somehow to use this?

Piterden Over a year ago

No, you don't need it. It's the bad practice. You should use ORM for queries. Also using facades is the bad practice too. You should use injected classes.

mehlichmeyer Over a year ago

You would need to use Eloquen\Builder Check other answers out as well, this is just one of a few options that should work

Piterden · Accepted Answer · 2018-07-13 11:53:48Z

0

Try this and never use any raw queries in Laravel

/* @var User $query */
$user = new User();

/* @var Builder $query */
$query = $user->newQuery();

dd($query->select('*')->from('_users')->get());

answered Jul 13, 2018 at 11:53

Piterden

7995 silver badges17 bronze badges

2 Comments

twigg Over a year ago

'never use any raw queries in Laravel' I'm not using Laravel. The above query works yes in very basic use cases but as in my question I have large complex quires from the legacy application which I need to run that simply aren't ready for eloquent style queries yet as the database is a mess

twigg Over a year ago

that's awesome, great work but right now it's simply not an option to convert the 8,000+ queries in this application over to an Eloquent style, just yet. This is obviously the end goal but from a business prospective it's simply not fiscally justifiable yet. So thats why I need a way to drop in the old raw SQL queries.

FULL STACK DEV · Accepted Answer · 2018-07-13 12:05:00Z

0

use Laravel scopes() this way you can easily use eloquent syntax.

$user = new User::customQuery();

public function scopeSustomQuery($query)
{
    $query = $query->where('You condition');
    return $query;
}

answered Jul 13, 2018 at 12:05

FULL STACK DEV

16k6 gold badges49 silver badges67 bronze badges

Comments

Hussein mahyoub · Accepted Answer · 2025-09-26 02:27:58Z

0

Simply, Convert the results to an Eloquent Collection

use Illuminate\Support\Collection; // Import if not already imported

$acc = DB::select('select id,name from accounts limit 5');
return  $collection = collect($acc);

answered Sep 26 at 2:27

Hussein mahyoub

4926 silver badges6 bronze badges

Collectives™ on Stack Overflow

PHP run a raw SQL query in eloquent model

6 Answers 6

4 Comments

2 Comments

3 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

2 Comments

3 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related