Asked  7 Months ago    Answers:  5   Viewed   31 times

How can I retrieve the raw executed SQL query in Laravel 3/4 using Laravel Query Builder or Eloquent ORM?

For example, something like this:

DB::table('users')->where_status(1)->get();

Or:

(posts (id, user_id, ...))

User::find(1)->posts->get();

Otherwise, at the very least how can I save all queries executed to laravel.log?

 Answers

30

Laravel 4+

In Laravel 4 and later, you have to call DB::getQueryLog() to get all ran queries.

$queries = DB::getQueryLog();
$last_query = end($queries);

Or you can download a profiler package. I'd recommend barryvdh/laravel-debugbar, which is pretty neat. You can read for instructions on how to install in their repository.

Note for Laravel 5 users: You'll need to call DB::enableQueryLog() before executing the query. Either just above the line that runs the query or inside a middleware.


Laravel 3

In Laravel 3, you can get the last executed query from an Eloquent model calling the static method last_query on the DB class.

DB::last_query();

This, however, requires that you enable the profiler option in application/config/database.php. Alternatively you could, as @dualed mentioned, enable the profiler option, in application/config/application.php or call DB::profile() to get all queries ran in the current request and their execution time.

Wednesday, March 31, 2021
 
HexaGridBrain
answered 7 Months ago
13

You can look at: Laravel access request object outside controller

There is request helper in laravel. You can use Request Object anywhere. For example

request()->field_name 
Wednesday, March 31, 2021
 
Niels
answered 7 Months ago
36

This is because you should use with when you don't have object yet (you are making query), and when you already have an object you should use load.

Examples:

Collection of users:

$users = User::with('profile')->get();

or:

$users = User::all();
$users->load('profile');

Single user:

$user = User::with('profile')->where('email','sample@example.com')->first();

or

$user = User::where('email','sample@example.com')->first();
$user->load('profile');

Methods implementation in Laravel

Also you can look at with method implementation:

public static function with($relations)
{
    return (new static)->newQuery()->with(
        is_string($relations) ? func_get_args() : $relations
    );
}

so it's starting new query so in fact it won't execute the query until you use get, first and so on where is load implementation is like this:

public function load($relations)
{
    $query = $this->newQuery()->with(
        is_string($relations) ? func_get_args() : $relations
    );

    $query->eagerLoadRelations([$this]);

    return $this;
}

so it's returning the same object, but it load relationship for this object.

Saturday, May 29, 2021
 
relyt
answered 5 Months ago
12

By default, the query log is disabled in Laravel 5: https://github.com/laravel/framework/commit/e0abfe5c49d225567cb4dfd56df9ef05cc297448

You will need to enable the query log by calling:

DB::enableQueryLog();

// and then you can get query log

dd(DB::getQueryLog());

or register an event listener:

DB::listen(
    function ($sql, $bindings, $time) {
        //  $sql - select * from `ncv_users` where `ncv_users`.`id` = ? limit 1
        //  $bindings - [5]
        //  $time(in milliseconds) - 0.38 
    }
);  

Some Tips

1. Multiple DB connections

If you have more than one DB connection you must specify which connection to log

To enables query log for my_connection:

DB::connection('my_connection')->enableQueryLog();

To get query log for my_connection:

print_r(
   DB::connection('my_connection')->getQueryLog()
);

2. Where to enable query log ?

For an HTTP request lifecycle, you can enable query log in the `handle` method of some `BeforeAnyDbQueryMiddleware` [middleware][1] and then retrieve the executed queries in the [`terminate`][2] method of the same middleware.
class BeforeAnyDbQueryMiddleware
{
    public function handle($request, Closure $next)
    {
        DB::enableQueryLog();
        return $next($request);
    }

    public function terminate($request, $response)
    {
        // Store or dump the log data...
        dd(
            DB::getQueryLog()
        );
    }
}

A middleware's chain will not run for artisan commands, so for CLI execution you can enable query log in the artisan.start event listener.

For example you can put it in the bootstrap/app.php file

$app['events']->listen('artisan.start', function(){
    DB::enableQueryLog();
});

3. Memory

Laravel keeps all queries in memory. So in some cases, such as when inserting a large number of rows, or having a long running job with a lot of queries, this can cause the application to use excess memory.

In most cases you will need the query log only for debugging, and if that is the case I would recommend you enable it only for development.

if (App::environment('local')) {
    // The environment is local
    DB::enableQueryLog();
}

References

  • https://laravel.com/docs/5.0/database#query-logging
Tuesday, June 1, 2021
 
sholsinger
answered 5 Months ago
74

I've added a package for this i-rocky/eloquent-dynamic-relation

In case anyone still looking for a solution , here is one. If you think it's a bad idea, let me know.

trait HasDynamicRelation
{
    /**
     * Store the relations
     *
     * @var array
     */
    private static $dynamic_relations = [];

    /**
     * Add a new relation
     *
     * @param $name
     * @param $closure
     */
    public static function addDynamicRelation($name, $closure)
    {
        static::$dynamic_relations[$name] = $closure;
    }

    /**
     * Determine if a relation exists in dynamic relationships list
     *
     * @param $name
     *
     * @return bool
     */
    public static function hasDynamicRelation($name)
    {
        return array_key_exists($name, static::$dynamic_relations);
    }

    /**
     * If the key exists in relations then
     * return call to relation or else
     * return the call to the parent
     *
     * @param $name
     *
     * @return mixed
     */
    public function __get($name)
    {
        if (static::hasDynamicRelation($name)) {
            // check the cache first
            if ($this->relationLoaded($name)) {
                return $this->relations[$name];
            }

            // load the relationship
            return $this->getRelationshipFromMethod($name);
        }

        return parent::__get($name);
    }

    /**
     * If the method exists in relations then
     * return the relation or else
     * return the call to the parent
     *
     * @param $name
     * @param $arguments
     *
     * @return mixed
     */
    public function __call($name, $arguments)
    {
        if (static::hasDynamicRelation($name)) {
            return call_user_func(static::$dynamic_relations[$name], $this);
        }

        return parent::__call($name, $arguments);
    }
}

Add this trait in your model as following

class MyModel extends Model {
    use HasDynamicRelation;
}

Now you can use the following method to add new relationships

MyModel::addDynamicRelation('some_relation', function(MyModel $model) {
    return $model->hasMany(SomeRelatedModel::class);
});
Saturday, August 14, 2021
 
user9824674
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :