Laravel version is 5.4
Here I put the link of the project to download (Includes database to import. It is in App/filtros_laravel.sql) https://www.dropbox.com/s/or78bfgg71xmlmp/filtros.zip?dl=0
I will give a description of the problem. Let's start with the tables first.
create table clientes
(
idcliente int primary KEY AUTO_INCREMENT NOT NULL,
nombre varchar(35) not null,
telefono varchar(30) null
);
create table pagos
(
idpago int primary KEY AUTO_INCREMENT NOT NULL,
nombre_pago varchar(35) not null,
idcliente int not null,
idestado int not null
);
CREATE TABLE estado_pagos
(
idestado int primary KEY AUTO_INCREMENT NOT NULL,
nombre_pago varchar(30) not null
);
ALTER TABLE pagos ADD FOREIGN KEY(idcliente) REFERENCES clientes(idcliente);
ALTER TABLE pagos ADD FOREIGN KEY(idestado) REFERENCES estado_pagos(idestado);
It basically says that a customer has many payments. A payment belongs to a status_payment(pending, paid). Example:
idcliente:1
nombre:Carlos
telefono: 391929
Pagos de carlos:
idpago:1,nombre_pago:pago1,idcliente:1,idestado:1,nombre_pago:Pendiente
idpago:2,nombre_pago:pago2,idcliente:1,idestado:1,nombre_pago:Pendiente
idpago:3,nombre_pago:pago3,idcliente:1,idestado:3,nombre_pago:Pagado
idcliente:2
nombre:Juliana
telefono: 122222
Pagos:
idpago:4,nombre_pago:pago julio,idcliente:1,idestado:1,nombre_pago:pendiente
idpago:5,nombre_pago:pago agosto,idcliente:1,idestado:3,nombre_pago:pagado
idpago:6,nombre_pago:pago promociones,idcliente:1,idestado:3,nombre_pago:pagado
tabla estados
idestado:1
nombre_pago:Pendiente
idestado:2
nombre_pago:No valido
idestado:3
nombre_pago:Pagado
I need you to bring me the client with all their payments with status paid. In other words, you should not bring clients with a pending payment status. Only the paid ones. I mean you should bring it like this
idcliente:1
nombre:Carlos
telefono: 391929
Pagos:
idpago:3,nombre_pago:pago1,idcliente:1,idestado:3,nombre_pago:Pagado
idcliente:2
nombre:Juliana
telefono: 122222
Pagos:
idpago:2,nombre_pago:pago1,idcliente:1,idestado:3,nombre_pago:Pagado
idpago:3,nombre_pago:pago1,idcliente:1,idestado:3,nombre_pago:Pagado
The models in Laravel are like this
Model Clients
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Cliente extends Model
{
protected $table = 'clientes';
protected $primaryKey = 'idcliente';
public $timestamps = false;
public function pagos()
{
return $this->hasMany('App\Pago', 'idcliente');
}
}
Payment Model
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Pago extends Model
{
protected $table = 'pagos';
protected $primaryKey = 'idpago';
public $timestamps = false;
public function cliente()
{
return $this->belongsTo('App\Cliente', 'idcliente');
}
public function estado()
{
return $this->belongsTo('App\EstadoPago', 'idestado');
}
}
Status_payments model
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class EstadoPago extends Model
{
protected $table = 'estado_pagos';
protected $primaryKey = 'idestado';
public $timestamps = false;
public function pagos()
{
return $this->hasMany('App\Pago', 'idestado');
}
}
The controller is like this
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Cliente;
use App\Pago;
use App\EstadoPago;
class ClienteController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
$clientes = cliente::with('pagos.estado')->whereHas('pagos.estado', function ($query) {
$query->where('nombre_pago', '=', 'pagado');
})->get();
return json_encode($clientes);
}
}
How do I make that filter? I am dealing with Eloquent. I have tried with whereHas and "with" Neither works. The whereHas doesn't work for me. It keeps showing me all payments with pending and paid status. I just want it to show me payments with status paid as mentioned in the example above. It should be like this in the example. This: whereHas('pagos.estado')
It simply goes to the Customer model, then it goes to the Payment Model, and then it goes to the Payment_Status model. Then this filters in Payment_Status
$query->where('nombre_pago', '=', 'pagado');
but it doesn't work, how can I do that filter? It's with whereHas or another because it doesn't work for me either with with.
public function index()
{
$clientes = cliente::with('pagos.estado')->with(['pagos.estado' => function ($query) {
$query->where('nombre_pago', '=', 'pagado');
}])->get();
return json_encode($clientes);
}
I am returning it in a json because that way I see the data better. I look at the result of the json on this page
http://www.jsoneditoronline.org/
I see the client with their payments and payment status. All in the same object.
that shows me in an object. to understand it much more clearly.
I need to perform filters in this way. This can be done by filtering by idstate = 3, but that's not what I need. The filter is done is by name. This is because I have queries of this type, filter to other tables. I can't do the filter by id. I want and need by name. Help please on this. I have searched a lot on videos, stackoverflow and other parts. In English and Spanish and I can't find anything. I really have tried very hard. I tried very hard to make this question very detailed and clear. I'm stuck with this it really helps. With join it can be done but that's not what I'm looking for. I'm just looking for filter with eloquent. No joining.
Well, although what you want to do may be a bit particular and extreme from the point of view of the way Laravel works and the relationships that work as dynamic properties, here is the solution: