Laravel's version is 5.4
Here I put the link of the project to download (Includes database to import, this in App / filtros_laravel.sql) link
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 payment_state (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 the client with all your payments with paid status. In other words, you should not bring clients with pending payment status. Only the paid. I mean you should bring it like that
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
Customer Model
<?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');
}
}
Payments 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');
}
}
Model_payments_state
<?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'm dealing with Eloquent. I have tried with whereHas and "with" neither of them works.
The whereHas does not work for me. It keeps showing me all payments with pending and paid status. I just want you to show me the payments with been paid as I mentioned in the previous example. It must be like this in the example.
This: whereHas('pagos.estado')
Simply goes to the Customer model, then goes to the Payment Model and then goes to the model State_payment. Then this filters in Payment_State
$query->where('nombre_pago', '=', 'pagado');
but it does not work how can I do that filter? is with whereHas or with another because it does not work with the with either.
public function index()
{
$clientes = cliente::with('pagos.estado')->with(['pagos.estado' => function ($query) {
$query->where('nombre_pago', '=', 'pagado');
}])->get();
return json_encode($clientes);
}
I'm returning it in a json because that way I see the data better. The result of the json I look at this page
I see the client with his payments and payment status. All in the same object.
that shows me in an object. to understand it much clearer.
I need to make filters in this way. This can be done filtering by idestado = 3, but that is not what I need. The filter is done by name. This is because I have queries of this type, filter to other tables. The filter can not do it by id. I want and I need by name. Help please in this. I've searched a lot in videos, stackoverflow and other parts. In English and Spanish and I can not find anything. I really tried hard. I try very hard to put this question very detailed and clear. I'm blocked with this really helps. With join it can be done but that's not what I'm looking for. I'm just looking for a filter with eloquent. No join.