CODEWITHSUNDEEP
phpsqllaravellaravel-5
Edwin Aquino
Edwin Aquino

Reputation: 219

How to use substring with laravel eloquent?

I have 2 related tables the problem is that in one that brings the clients of each user, but the client code is based mainly on 4 digits, the problem is that there are many users who have more than 4 digits 8 or 9 that this represents your task in the company. But I need to bring only the first 4 that I need to make a relationship with the table and be able to bring only the name of the company

I'm dealing with substring in sql like this

$client = DB::connection('dpnmwin')->table('nmtrabajador')->select('SUBSTRING'('COD_UND', 1, 4))->where('CONDICION', '=', 'A')->get()

but this marks me error from the same code editor.

How can I do it using substring or otherwise?

Model Employee

class Employee extends Model
{
    protected $connection = 'dpnmwin';

    protected $table = 'nmtrabajador';

    protected $primaryKey = 'CODIGO';

    public function client(){

        return $this->belongsTo('App\Client', 'COD_UND');

    }

    public function position(){

        return $this->belongsTo('App\Position', 'COD_CARGO');

    }

}

Model Client

class Client extends Model
{
    protected $connection = 'dpnmwin';

    protected $table = 'nmundfunc';

    protected $primaryKey = 'CEN_CODIGO';

    public function employee(){

        return $this->hasMany('App\Employee');

    }

}

File list.blade.php

 @foreach ($users as $user)
       <tr>
           <td>{{$user->CEDULA}}</td>
           <td>{{$user->NOMBRE}}</td>
           <td>{{$user->APELLIDO}}</td>
           <td>{{$user->EMAIL}}</td>
           <td>{{$user->position->CAR_DESCRI}}</td>
           <td>{{$user->client->CEN_DESCRI}}</td>
           <td><a href="{{ route('detailUser', ['user_id' => $user->CODIGO]) }}"><i class="fas fa-user"></i></a></td>
      </tr>
 @endforeach

Upvotes: 2

Views: 9002

Answers (1)

yrv16
yrv16

Reputation: 2275

Use \DB::raw() for select:

$client = DB::connection('dpnmwin')->
          table('nmtrabajador')->
          select(\DB::raw('SUBSTRING(COD_UND, 1, 4) as COD_UND'))->
          where('CONDICION', '=', 'A')->
          get()

Upvotes: 4

Related Questions