How I can see what queries run in my mssql server once I exeture them via eloquent model?

Guys can you help me with that?

How I can see what queries run in my mssql server once I exeture them via eloquent model?

Jan 9 '24 Comments: 2 Answers: 1

0

In my laravel tinker session I run the following:

$records = MyModel::with('parent','children')->whereNull('parent_id')->get()

The model is:

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasOne;

class MyModel extends Model
{

    protected $table='mymodel';

    public function setNameAttribute($name)
    {
        $name=htmlspecialchars($name, ENT_QUOTES, 'UTF-8');
        $this->attributes['name']=$name;
    }
    public function parent(): HasOne
    {
        return $this->hasOne(Category::class,'id','parent_id');
    }

    public function children():

…

Open Full Question

I try to fetch a table with self references that generates a tree structure as efficient as possible:

$records = MyModel::with('parent','children')->whereNull('parent_id')->get();

Therefore, I want to see what queries are executed. But the way I do in the SO question I do no see the proper queries:

select * from mymodel ....

But instead:

In my laravel tinker session I run the following:

$records = MyModel::with('parent','children')->whereNull('parent_id')->get();

The model is:

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasOne;

class MyModel extends Model
{

    protected $table='mymodel';

    public function setNameAttribute($name)
    {
        $name=htmlspecialchars($name, ENT_QUOTES, 'UTF-8');
        $this->attributes['name']=$name;
    }
    public function parent(): HasOne
    {
        return $this->hasOne(Category::class,'id','parent_id');
    }

    public function children(): HasMany
    {
        return $this->hasMany(Category::class, 'parent_id');
    }

}

And I want to see what is actually executed in my mssql server that I run via docker-compose in my linux machine:

version: "3.3"
services:

  # PHP and nginx is here - ommited in order to keel question length sane

  mssql:
    # Replace with your own
    image: mcr.microsoft.com/mssql/server:2022-latest
    user: root
    networks:
      private:
      public:
        ipv4_address: ${IP_BASE}.3
    command: bash -c "chmod -R 777 /usr/src/sql/ && /opt/mssql/bin/sqlservr"
    volumes:
      - ./volumes/db/data:/var/opt/mssql/data
      - ./volumes/db/log:/var/opt/mssql/log
      - ./sql:/usr/src/sql
    environment:
      ACCEPT_EULA: "Y"
      MSSQL_SA_PASSWORD: "<censored>"
      MSSQL_PID: "Developer"

At my mssql tool (DBeaver) I've run:

CREATE EVENT SESSION QueryMonitor
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(ACTION(sqlserver.sql_text))
ADD TARGET package0.event_file
(SET filename='/var/opt/mssql/log/QueryMonitor.xel');

ALTER EVENT SESSION QueryMonitor ON SERVER STATE = START;

But by peeking into file QueryMonitor.xel I see only records for telemetry:

SELECT target_data
                                    FROM sys.dm_xe_session_targets xet WITH(nolock)
                                    JOIN sys.dm_xe_sessions xes WITH(nolock)
                                    ON xes.address = xet.event_session_address
                                    WHERE xes.name = 'telemetry_xevents'
                                    AND xet.target_name = 'ring_buffer'$SELECT target_data
                                    FROM sys.dm_xe_session_targets xet WITH(nolock)
                                    JOIN sys.dm_xe_sessions xes WITH(nolock)
                                    ON xes.address = xet.event_session_address
                                    WHERE xes.name = 'telemetry_xevents'
                                    AND xet.target_name = 'ring_buffer'`t
��
  R.    �����d2�SET DEADLOCK_PRIORITY -104SET DEADLOCK_PRIORITY -10`t
��'R.   ��d�if not exists (select * from sys.dm_xe_sessions where name = 'telemetry_xevents') if not exists (select * from sys.dm_xe_sessions where name = 'telemetry_xevents')
    alter event session telemetry_xevents on server state=start`

Any ideas why?