2 years ago
#18809
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