Asked  2 Years ago    Answers:  5   Viewed   99 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

4

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.

Sunday, August 28, 2022
2

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 
Saturday, September 17, 2022
2

Well I found the solution. It can be done one by passing a closure function in with() as second index of array like

Post::query()
    ->with(array('user' => function($query) {
        $query->select('id','username');
    }))
    ->get();

It will only select id and username from other table. I hope this will help others.


Remember that the primary key (id in this case) needs to be the first param in the $query->select() to actually retrieve the necessary results.*

Tuesday, August 30, 2022
 
5

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
Friday, August 12, 2022
2

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);
});
Monday, August 8, 2022
 
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 

Browse Other Code Languages