2 years ago

#18809

test-img

Joseph Aguilar

Laravel hasMany with json field

I've been trying to relate two models with laravel eloquent, the services model and the invoices model, in the invoices model I declare a column where the id's of the services are stored as array, for example:

Services table:

id_service  | name  | ... other columns
    1       | name1 | ... other columns for services 1
    2       | name2 | ... other columns for services 2
    3       | name3 | ... other columns for services 3
    4       | name4 | ... other columns for services 4

And Invoices table:

 id_invoice | name_invoice   | id_services | ... other columns
    1       | name invoice 1 |    [1, 3]   | ... other columns for invoice 1
    2       | name invoice 2 |      [2]    | ... other columns for invoice 2

And in my models the code is:

Invioces:

<?php

namespace cTransporte;

use Illuminate\Database\Eloquent\Model;

class Invoices extends Model
{
    use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;
    protected $table = 'db_invoices';
    protected $primaryKey = 'id_invoice';
    protected $fillable = ['id_estatus', 'id_cuenta', 'id_client', 'clave_sap', 'sdl', 'folio_prefactura', 'folio_factura', 'id_servicios',
                            'is_facturado', 'usuario_factura', 'fecha_facturado', 'id_cobro_servicios','is_cobrado', 'usuario_cobra', 'fecha_cobro', 'fecha_revision_factura',
                            'serie', 'forma_pago', 'condiciones_pago', 'subtotal', 'moneda', 'tipo_cambio', 'total', 'tipo_comprobante', 'metodo_pago', 'lugar_expedicion', 'uuid',
                            'adenda', 'referencia', 'orden_compra', 'expediente', 'sucursal', 'xml','rfc', 'razon_social', 'uso_cfdi', 'conceptos', 'impuestos', 'tipo_relacion',
                            'id_factura_relacionada', 'uuid_relacionado', 'is_timbrado', 'motivo_cancelacion'
                        ];
    protected $casts = [
        'id_servicios' => 'json',
    ];

    public function orderServices()
    {
        return $this->hasMany(Services::class, 'id_relacion_factura', 'id_invoice');
    }

    public function client()
    {
        return $this->belongsTo(Client::class, 'id_client', 'id_client');
    }

    public function pagoCliente()
    {
        return $this->hasOne(CobrosServicios::class, 'id_cobro_servicios', 'id_cobro_servicios');
    }

    public function estatus()
    {
        return $this->hasOne(EstatusServicio::class, 'id_estatus_servicio', 'id_estatus');
    }

    public function cuenta(){
        return $this->hasOne(Cuentas::class, 'id_cuenta', 'id_cuenta');
    }

    public function services()
    {
        return $this->belongsToJson(Services::class, 'id_servicios');

    }

}


Services:


<?php

namespace cTransporte;

use Illuminate\Database\Eloquent\Model;

class Services extends Model
{
    use \Staudenmeir\EloquentJsonRelations\HasJsonRelationships;
    protected $table = "db_servicices";
    protected $primaryKey = "id_service";
    protected $fillable = [
        'id_cuenta', 'is_monitoreo', 'is_gastos', 'is_facturacion', 'id_estatus', 'km_inicio', 'km_fin',
        'id_usuario', 'no_embarque', 'id_operador', 'id_unidad', 'id_client', 'id_centro_distribucion_cliente', 'id_tienda', 'no_guia', 'id_tienda_2',
        'no_guia_2', 'is_devolucion', 'no_embarque_devolucion', 'no_guia_devolucion', 'id_estado_origen', 'ciudad_origen',
        'del_mun_origen', 'calle_no_origen', 'cp_origen', 'id_estado_destino', 'ciudad_destino', 'del_mun_destino', 'calle_no_destino', 'propietario_unidad',
        'cp_destino', 'calle_no_origen_02', 'cp_origen_02', 'id_estado_destino_02', 'ciudad_destino_02', 'del_mun_destino_02', 'calle_no_destino_02', 'fecha_hora_servicio', 'fecha_hora_carga', 'fecha_hora_iniciar', 'fecha_hora_en_ruta', 'fecha_hora_en_tienda', 'fecha_hora_descargando', 'fecha_hora_salida_tienda', 'fecha_hora_fin',
        'cp_destino_02','direccion_destino', 'bitacora_seguimiento', 'proveedor_unidad', 'tipo_unidad', 'accesorios', 'comentarios',
        'id_relacion_factura', 'folio_iave', 'is_documentos','carta_porte', 'usuario_documentos',
        'is_facturado', 'is_carta_porte', 'is_precios', 'usuario_precios', 'precio_viaje', 'iva', 'retencion', 'total', 'precio_accesorios', 'iva_accesorios', 'retencion_accesorios', 'total_accesorios', 'is_prefactura', 'usuario_prefactura', 'folio_prefactura_servicio', 'fecha_prefacturado', 'is_facturado', 'id_relacion_factura',
        'usuario_factura', 'folio_factura_servicio', 'fecha_revision_factura', 'fecha_facturado', 'id_cobro_servicios', 'is_cobrado', 'usuario_cobra', 'fecha_cobro', 'fecha_facturado', 'is_factura_individual',
        'is_pagado_operador', 'id_semana_nomina', 'id_nomina', 'monto_operador', 'fecha_hora_pago_operador', 'is_pagado_proveedor', 'monto_proveedor', 'monto_accesorios_proveedor', 'subtotal_proveedor', 'iva_proveedor', 'retencion_proveedor', 'total_proveedor', 'fecha_hora_pago_proveedor',
        'iva_accesorios_proveedor', 'retencion_accesorios_proveedor', 'total_accesorios_proveedor', 'cantidad_casetas_iave', 'total_casetas_iave', 'mes_pago_iave', 'costo_no_autorizado_iave', 'is_cancelar_pago', 'motivo_cancelacion_pago', 'modo_servicio', 'lugar_carga', 'cliente_final', 'is_marcado', 'fecha_liberacion',
        'transpinternac', 'totaldistrec', 'ubicaciones', 'PesoBrutoTotal', 'UnidadPeso', 'NumTotalMercancias', 'mercancias'
    ];

    public function cliente(){
        return $this->hasOne(Clientes::class, 'id_client', 'id_client');
    }
    public function operador(){
        return $this->hasOne(Operador::class, 'id_operador', 'id_operador');
    }
    public function unidad(){
        return $this->hasOne(Unidades::class, 'id_unidad', 'id_unidad');
    }
    public function destino(){
        return $this->hasOne(TiendaCliente::class, 'id_tienda_cliente', 'id_tienda');
    }
    public function estadoDestino(){
        return $this->hasOne(Estados::class, 'id_estado', 'id_estado_destino');
    }
    public function destino02(){
        return $this->hasOne(TiendaCliente::class, 'id_tienda_cliente', 'id_tienda_2');
    }
    public function origen(){
        return $this->hasOne(CentroDistribucion::class, 'id_centro_distribucion_cliente', 'id_centro_distribucion_cliente');
    }
    public function estadoOrigen(){
        return $this->hasOne(Estados::class, 'id_estado', 'id_estado_origen');
    }
    public function factura(){
        return $this->hasOne(RelacionFacturacion::class, 'id_relacion_facturacion', 'id_relacion_factura');
    }

    public function pagoCliente()
    {
        return $this->hasOne(CobrosServicios::class, 'id_cobro_servicios', 'id_cobro_servicios');
    }

    public function invoices()
    {
        return $this->hasManyJson(Invioces::class, 'id_servicios');
    }
}


As you can see I'm using the staudenmeir / eloquent-json-relations laravel package and it seems to work with one little detail, when I list the invoices with services relation:

$invoices = Invoices::with('services')->get();

I don't get any error, but the services property in the invoice record is empty, example:

 invoices = [
        {
            id_invoice: 1,
            name_invoice: "name invoice 1",
            id_services: "[1, 3]",
            services: [] **<-- here is the detail this invoices has 2 services related but shows 0**
        },
        {
            id_invoice: 1,
            name_invoice: "name invoice 1",
            id_services: "[2]",
            services: [] **<-- here is the detail this invoices has 1 services related but shows 0**
        }
 ]

And my migrations are declarated as:

For Servicies:

Schema::create('db_services', function (Blueprint $table) {
$table->bigIncrements('id_service');
.
.
.
(others services columns) 
}); 

For Invoices

Schema::create('db_invoices', function (Blueprint $table) { 
$table->bigIncrements('id_invoice'); 
$table->json('id_services'); 
.
.
.
(others services columns) 
}); 

This is te log queries:

select * from `db_invoices` where `id_estatus` = 7
select * from `db_services` where `db_services`.`id_service` in (0)
select * from `db_clients` where `db_clients`.`id_client` in (1, 2)

Is important to specify that in this call I include other relation (the client) relation, but client relation is working fine.

The Response for dd($invoices[0]->getAttributes()); is the entire list of the fillable attributes from the model:

id_invoice: 657,
id_status: 7,
id_client: 1,
pay_format: null,
id_services: [7982, 6782,3762],
created_at: "2021-12-22 17:52:21",
updated_at: "2021-31-22 12:02:11",
.
.
. etc

Please hope someone could help me, plis...!

json

laravel

eloquent

laravel-6

relation

0 Answers

Your Answer

Accepted video resources