Apply filter in related tables laravel

1

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

link

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.

    
asked by semidf des 02.06.2017 в 05:22
source

1 answer

2

Well, although what you want to do may be a bit particular and extreme from the point of view of how Laravel works and the relationships that work as dynamic properties, here is the solution:

Cliente::with(['pagos' => function ($query) {
        $query->whereHas('estado', function ($query) { 
            $query->where('nombre_pago', 'pagado');
        });
    },
    'pagos.estado' => function ($query) {
        $query->where('nombre_pago', 'pagado');
    }])
    ->whereHas('pagos.estado', function ($query) {
        $query->where('nombre_pago', 'pagado');
    })
    ->get();
    
answered by 02.06.2017 / 05:55
source