Hello Laravel Developers, Good day. Please how can I convert a raw sql query to laravel query builder or eloquent ORM.

I have the following models:

relationships

Model Server

  public function users()
    {
        return $this->belongsToMany(User::class,'server_users')->withPivot('spam','inbox');;
    }

    public function users_cancellation()
    {
        return $this->belongsToMany(User::class,'server_user_cancellations')->withPivot('spam','inbox');;
    }

Model User

   public function servers(): BelongsToMany
    {
        return $this->belongsToMany(Server::class, 'server_users');
    }

    public function servers_cancellation(): BelongsToMany
    {
        return $this->belongsToMany(Server::class, 'server_user_cancellations');
    }

tables

User

   $table->id();
            $table->string('full_name');
            $table->string('username')->unique();
            $table->string('password');
            $table->string('type');
            $table->boolean('is_active')->default(true);
            $table->rememberToken();
            $table->timestamps();
            $table->softDeletes();

Server

    Schema::create('servers', function (Blueprint $table) {
            $table->id();
            $table->string('name')->default('SM');
            $table->ipAddress('ip')->unique();
            $table->string('username')->default('root');
            $table->boolean('is_active')->default(true);
            $table->softDeletes();
            $table->timestamps();
        });

Server_user_cancellations

   Schema::create('server_user_cancellations', function (Blueprint $table) {
            $table->id();
            $table->foreignIdFor(Server::class)->constrained()->cascadeOnDelete()->cascadeOnUpdate();
            $table->foreignIdFor(User::class)->constrained()->cascadeOnUpdate()->cascadeOnDelete();
            $table->boolean('spam')->nullable();
            $table->boolean('inbox')->nullable();
            $table->timestamps();
        });

Server_users

  Schema::create('server_users', function (Blueprint $table) {
            $table->id();
            $table->foreignIdFor(Server::class)->constrained()->cascadeOnDelete()->cascadeOnUpdate();
            $table->foreignIdFor(User::class)->constrained()->cascadeOnUpdate()->cascadeOnDelete();
            $table->boolean('spam')->nullable();
            $table->boolean('inbox')->nullable();
            $table->timestamps();
        });

i want to convert this raw sql query to laravel eloquent or query builder?

if ($request->wantsJson()) {
  $servers = DB::select('
  SELECT 
    s.name,
    s.id,
    s.ip,
    (SELECT count(id) 
      FROM server_user_cancellations 
      where server_id = s.id and user_id=?
    ) as exist
  FROM servers s
  WHERE s.id IN
    (SELECT  distinct server_id from server_users  where user_id=?)
    AND s.is_active=true
    AND s.is_installed=true
    AND s."deleted_at" is null', 
  [auth()->id(), auth()->id()]
);

CodePudding user response：

from query i assume Server can belong to many Users and User can have many Servers, and both has many ServerUserCancellation, so you need servers for current user with cancellations existence flag

first define relations that fits your database schema

// Server model
public function cancellations(){
  return $this->hasMany(ServerUserCancellation::class);
}

public function users(){
  return $this->belongsToMany(User::class);
}

// User model
public function servers(){
  return $this->belongsToMany(Server::class);
}

now you can count cancellations and filter servers which has current user according to users relation

$baseFilters = [
  'is_active' => true,
  'is_installed' => true,  
];
$result = Server::where($basicFilters)
  ->whereNull('deleted_at')
  ->whereHas('users', function($query){
      $query->where('user_id', auth()->id());
    })
  ->withCount(['cancellations as exists' => function($query){
      $query->where('user_id', auth()->id();
    }])
  ->select(['id', 'ip', 'name'])
  ->get();

//result strucure will be looking like this
array(
  [
    'id': 1,
    'ip': '127.0.0.1',
    'name': 'server 1 name',
    'exists': 0  
  ], 
  ...
) 

to make controller cleaner there is mechanism of soft deleting to skip whereNull('deleted_at') for each Server query and scopes to make code easy to read

// Server model
class Server extends Model {
  use SoftDeletes;

  public function scopeActive($query){
    return $query->where('is_active', true);
  }

  public function scopeInstalled($query){
    return $query->where('is_installed', true);
  }

  // ... other model code
}

and code will look like this

$result = Server::active()
  ->installed()
  ->whereHas('users', function($query){
      $query->where('user_id', auth()->id());
    })
  ->withCount(['cancellations as exists' => function($query){
      $query->where('user_id', auth()->id();
    }])
  ->select(['id', 'ip', 'name'])
  ->get();