Laravel 5.7 - Advanced where() query inside a when() function

Question

I am having problems with a query that uses values from multiple form inputs, and each one is optional.

The idea is to find the applications of an ISP (ie. technical services, installations, etc) assigned to a technician.

Due to the inputs being optional, I'm using a ->when() function to avoid queries with NULL values.

But also I need to find the applications using the ID of the technician, this ID is stored in a pivot table with the related application ID.

This is the code in the controller

$finalizadas = Solicitud::whereHas('tecnicos')
  ->when($desde, function ($query) use ($desde, $hasta) {
      return $query->whereBetween('sol_fecha_finalizada', [$desde, $hasta])->where('sol_estado', 4);
  })
  ->when($tipo, function ($query) use ($tipo) {
      return $query->where('sol_tipo_solicitud', $tipo)->where('sol_estado', 4);
  })
  ->when($tecnico, function ($query) use ($tecnico) {
      return $query->where('tecnico_tec_id', $tecnico)->where('sol_estado', 4);
  })
  ->when($cliente, function ($query) use ($cliente) {
      return $query->where('sol_cliente', $cliente)->where('sol_estado', 4);
  })->get();

return view('solicitudes.listar_finalizadas')->with('finalizadas', $finalizadas);

sol_estado = 4 stands for application finished.

tecnico_tec_id is the ID of the technician in the pivot table solicitud_tecnico

The problem is when I try to search apps by technician, it gives the next error.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tec_id' in 'where clause' (SQL: SELECT * FROM solicitudes WHERE EXISTS (SELECT* FROM tecnicos INNER JOIN solicitud_tecnico ON tecnicos.tec_id = solicitud_tecnico.tecnico_tec_id WHERE solicitudes.sol_id = solicitud_tecnico.solicitud_sol_id) AND tec_id = 8 AND sol_estado = 4)

This statement, altought is inside the relationship, it doesn't work

->when($tecnico, function ($query) use ($tecnico) {
    return $query->where('tecnico_tec_id', $tecnico)->where('sol_estado',4);
})

but this one works like a charm

$finalizadas = Solicitud::whereHas('tecnicos', function ($query) use ($tecnico) {
    $query->where('tecnico_tec_id', $tecnico)->where('sol_estado', 4);
})->get();

Model Solicitud (Application)

<?php

namespace OPyME2;

use Illuminate\Database\Eloquent\Model;

class Solicitud extends Model
{
   // Nombre de la tabla
    protected $table = 'solicitudes';

    // Primary key
    protected $primaryKey = 'sol_id';

    // Marcas de fecha
    public $timestamps = false; 

    // Columnas
    protected $fillable = ['sol_id','sol_fecha_creacion','sol_fecha_traslado','sol_fecha_retiro','sol_fecha_finalizada','sol_horario','sol_cliente','sol_estructura', 'sol_plan', 'sol_escalera', 'sol_tecnico_asignado', 'sol_estado', 'sol_motivo', 'sol_zona_gps', 'sol_telefono_2', 'sol_domicilio_traslado', 'sol_creacion', 'sol_tipo_solicitud', 'sol_pedido_material
    '];

    // Pivot
    public function tecnicos()
    {
        return $this->belongsToMany('\OPyME2\Tecnico', 'solicitud_tecnico')
        ->withPivot('solicitud_sol_id');
    }
}

Model Tecnico (Technician)

<?php

namespace OPyME2;

use Illuminate\Database\Eloquent\Model;

class Tecnico extends Model
{
    // Nombre de la tabla
    protected $table = 'tecnicos';

    // Primary key
    protected $primaryKey = 'tec_id';

    // Marcas de fecha
    public $timestamps = false; 

    // Columnas
    protected $fillable = ['tec_id', 'tec_nombre', 'tec_activo', 'tec_movil'];

    // Pivot
    public function solicitudes()
    {
        return $this->belongsToMany('\OPyME2\Solicitud', 'solicitud_tecnico')
        ->withPivot('tecnico_tec_id');
    }

    public function moviles()
    {
        return $this->belongsToMany('\OPyME2\Movil', 'movil_tecnico')
        ->withPivot('tecnico_tec_id');
    }
}

I can't figure out what the error is.

You can't constraint the relationship query outside of the relationship, so you need to have the constraint nested like the code you have working — Salim Djerbouh, Oct 16 '19 at 22:21
I tried to nest the working code (relationship) inside the code that gives error (when).. and it works fine when I search a tech., but if I search a tech + from/to date or any other search condition it returns an emtpy query. — pmpato, Oct 16 '19 at 22:42

IGP · Answer 1 · 2019-10-17T14:26:17.780

0

I think this might be caused because the tecnico_tec_id field is part of the pivot table. Have you tried querying it inside the whereHas closure?

$finalizadas = Solicitud::where('sol_estado', 4)
->when($tecnico, function ($query) use ($tecnico) {
    return $query->whereHas('tecnicos', function ($query) use ($tecnico) {
        $query->where('tecnico_tec_id', $tecnico);
    });
}, function ($query) {
    return $query->has('tecnicos');        
})
->when($desde, function ($query) use ($desde, $hasta) {
    return $query->whereBetween('sol_fecha_finalizada', [$desde, $hasta]);
})
->when($tipo, function ($query) use ($tipo) {
    return $query->where('sol_tipo_solicitud', $tipo);
})
->when($tecnico, function ($query) use ($tecnico) {
    return $query->where('tecnico_tec_id', $tecnico);
})
->when($cliente, function ($query) use ($cliente) {
    return $query->where('sol_cliente', $cliente);
})
->get();

edited Oct 17 '19 at 14:26

answered Oct 17 '19 at 13:03

IGP

14,160
4
26
43

It returns ````Call to undefined method OPyME2\Solicitud::tecnico()```` On ````return $query->has('tecnico');```` – pmpato Oct 17 '19 at 14:15
`tecnicos`, mistyped the relationship method – IGP Oct 17 '19 at 14:26
Same error: ````Column not found tecnico_tec_id```` The relationship query ````AND tecnico_tec_id = 8````, its outside of the main query, thats why it cant find the column, idk how to place inside of it :( – pmpato Oct 17 '19 at 15:00

score 0 · Answer 2 · answered Oct 17 '19 at 16:02

Finally the next did the trick

$finalizadas = Solicitud::whereHas('tecnicos')
          ->when($tecnico, function ($query) use ($tecnico) {
              return Solicitud::whereHas('tecnicos', function ($query) use ($tecnico) {
                     return $query->where('tecnico_tec_id', $tecnico)->where('sol_estado', 4);
                    });
          })
          ->when($cliente, function ($query) use ($cliente) {
              return $query->where('sol_cliente', $cliente)->where('sol_estado', 4);
          })
          ->when($desde, function ($query) use ($desde, $hasta) {
              return $query->whereBetween('sol_fecha_finalizada', [$desde, $hasta])->where('sol_estado', 4);
          })
          ->when($tipo, function ($query) use ($tipo) {
              return $query->where('sol_tipo_solicitud', $tipo)->where('sol_estado', 4);
          })
          ->get();

I've nested the relationship query inside the main query, in that way and it works, even using all search conditions. PD: technicians (tecnicos) relationship has to be always first, otherwise the query excludes the dates, type of application and/or client. Thank you @IGP

Laravel 5.7 - Advanced where() query inside a when() function

2 Answers2