Consult intermediate table with datatable in Laravel 5.2

Question

Consult intermediate table with datatable in Laravel 5.2

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have the product table and the customer table, both with the columns:

id
name.

And also, an intermediate table called clientes_productos with the columns:

client_id
product_id
date_changing

Models and relationships are already realized. What I need to show the products that I am exchanging, a certain client next to their respective exchange date. I need to show these data in datatable .

I would appreciate it if you can help me.

I'm doing it in Laravel 5.2

laravel-5

asked by Pablo Rivera Madrazo 13.03.2017 в 23:00

source

2 answers

Because when I try to debug in visual studio 2010, does not the APP stop me? [closed] "replaceAll ()" in java does not work correctly

score 1 · Answer 1

Client Model:

public function productos(){

        return $this->belongsToMany('App\Producto','clientes_productos','cliente_id','producto_id');
    }

Model Products:

public function clientes(){

        return $this->belongsToMany('App\Cliente','clientes_productos','producto_id','cliente_id');
    }

ProductControllerCustomer:

public function anyData()
{
$prodCli = DB::ClienteProducto()
    ->where('cliente_id', 1)
    ->orderBy('fecha_canje', 'desc')
    ->take(10)
    ->get();

return Datatables::of($prodCli)
    ->where('cliente_id', 1)
    ->removeColumn('producto_id')
    ->addColumn('fecha_canje')
    ->addColumn('operaciones', '
            <ul class="icons-list">
                <li class="dropdown">
                    <a href="#" class="dropdown-toggle" data-toggle="dropdown">
                        <i class="icon-menu9"></i>
                    </a>
                    <ul class="dropdown-menu dropdown-menu-right">
                        <li><a href="{{ URL::route( \'clienteproducto.editar\', array( $id )) }}"><i class="icon-pencil"></i> Editar</a></li>
                        <li><a href="#" onclick="eliminar({{ $id  }})"><i class="icon-trash"></i> Eliminar</a></li>
                    </ul>
                </li>
            </ul>')
    ->removeColumn('id')
    ->make(true);
}

ProductList.blade.php:

@section('scripts')
    <script type="text/javascript">
        window.onload = function datos() {

            // Basic initialization
            $('#producto-table').DataTable({
                autoWidth: false,
                responsive: true,
                dom: '<"datatable-header"fl><"datatable-scroll"t><"datatable-footer"ip>',
                language: {
                    search: '<span>Filtrar:</span> _INPUT_',
                    lengthMenu: '<span>Mostrar:</span> _MENU_',
                    paginate: { 'primera': 'Primera', 'ultima': 'Ultima', 'siguiente': '→', 'previa': '←' }
                },
                drawCallback: function () {
                    $(this).find('tbody tr').slice(-3).find('.dropdown, .btn-group').addClass('dropup');
                },
                preDrawCallback: function() {
                    $(this).find('tbody tr').slice(-3).find('.dropdown, .btn-group').removeClass('dropup');
                },
                processing: true,
                serverSide: true,//evita que la columna con botones sea un parametro en la consulta sql
                "aoColumnDefs": [{ 'bSortable': false,"bSearchable": false, 'aTargets': [ 2 ] }],
                ajax: '{!! route('producto.datatable') !!}',
                columns: [
                    {data: 'nombre', name: 'nombre'},
                    {data: 'puntos_producto', name: 'puntos_producto'},
                    {data: 'operaciones', name: 'operaciones'}
                ]
            });
        }
</script>
@endsection

Product Customer Migration

public function up()
    {
        Schema::create('clientes_puntos', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('cliente_id')->unsigned();
            $table->foreign('cliente_id')
                ->references('id')->on('clientes');

            $table->integer('punto_id')->unsigned();
            $table->foreign('punto_id')
                ->references('id')->on('puntos');

            $table->date('fecha_carga');
            $table->integer('puntos_cargados');

            $table->timestamps();
        });
    }

score 0 · Answer 2

You must create a model that manages the table, and let Eloquent do its magic.

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class ProductosCliente extends Model
{
    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = 'clientes_productos';
    public function Cliente()
    {
        return $this->belongsTo('App\Cliente');
    }
    public function Producto()
    {
        return $this->belongsTo('App\Producto');
    }
}

then you can filter as you like:

$prodCli= App\ProductosCliente::where('cliente_id', 1)
               ->orderBy('fecha_canje', 'desc')
               ->take(10)
               ->get();

You can also make relationships in the Products or Customers model to access from there.