How can I use Eager Loading with Yajra Datatables and Dimsav Translatable in Laravel 5.2

Question

I am using yajra/laravel-datatables and dimsav/laravel-translatable to create a table of roles.

The datatable structure is as follows. Roles table migration:

Schema::create('roles', function (Blueprint $table) {
    $table->increments('id');
    $table->softDeletes();
    $table->timestamps();
});

Role translations table migration:

Schema::create('role_translations', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('role_id')->unsigned();
    $table->string('name')->index();
    $table->string('locale')->index();

    $table->unique(['role_id', 'name', 'locale']);
    $table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');
});

Now I am doing this at the controller...

public function indexData()
{
    $roles = Role::join('role_translations', 'roles.id', '=', 'role_translations.role_id')
        ->select(['roles.id', 'role_translations.name', 'roles.created_at', 'roles.updated_at'])
        ->groupBy('roles.id');

...and this at the view (Datatable initialization and general settings are done in a common js file and particular settings are passed as HTML attributes)...

<table class="table table-striped table-bordered" data-table data-ajax="{{ url('/admin/role/index-data') }}" data-responsive="true">
    <thead>
        <tr>
            <th data-priority="1">{{ trans('messages.name') }}</th>
            <th>{{ trans('messages.created') }}</th>
            <th>{{ trans('messages.modified') }}</th>
            <th data-priority="1" data-sortable="false" data-class-name="actions">{{ trans('messages.actions') }}</th>
        </tr>
    </thead>
</table>

It works, but I feel uncomfortable with having all those joins on the query and I would like to do something like

$roles = Role::with('translation')->select(['roles.id', 'role_translations.name', 'roles.created_at', 'roles.updated_at'])

But I am not having any luck.

score 0 · Answer 1 · answered Mar 21 '16 at 18:24

Here is my working example with eager loading. I hope that might help you

//controller query

$medicine  = Medicine::with(['manufacturer','doseageForm','measureUnit','supplier'])
            ->select(['id','product_name','generic_name','product_class','manufacturer_id', 
                      'doseage_form_id','measure_unit_id','strenght','status']);

return Datatables::of($medicine)
        ->editColumn('status', function($medicine){
            return (($medicine->status == 1)?"Active":"Deactive");
        })
        ->editColumn('manufacturer_id', function($medicine){

            $manufacturer_name   =   $medicine->manufacturer->name;
            return $manufacturer_name;
        })
        ->editColumn('product_name', function($medicine){
            return 
                $medicine->product_name.", ".
                    $medicine->doseageForm->name.", ".
                    $medicine->strenght.$medicine->measureUnit->name;
        })
        ->addColumn('supplier',function($medicine){

            if($medicine->supplier->count() > 0){
                return $medicine->supplier->first()->qualified_person;
            }else{
                return '---';
            }
        })
        ->addColumn('actions', function($medicine){

            $edit_route =   route('medicine-edit',['id'=>$medicine->id ]);
            $del_route  =   route("ajax-delete",["type"=>"medicine","id"=>$medicine->id ]);

            $status     =   (($medicine->status == 1)?
                                '<a href="" class="btn btn-xs btn-warning"><i class="fa fa-eye"></i></a>'
                                :
                                '<a href="" class="btn btn-xs btn-warning"><i class="fa fa-eye-slash"></i></a>'
                            );

            $html       =   '<div class="btn-group">
                                '.$status.'
                                <a href="'.$edit_route.'" class="btn btn-xs btn-primary" alt="edit"><i class="fa fa-pencil"></i></a>
                                <a href="'.$del_route.'" data-target="#ajax_delete" alt="delete" data-toggle="modal" class="btn btn-xs btn-danger">
                                    <i class="fa fa-trash-o"></i>
                                </a>
                            </div>';

            return $html;
        })
        ->make(true);

my view code is

<table class="table table-bordered table-striped table-condensed flip-content" id="medicine">
    <thead class="flip-content">
        <tr>
            <th>Medicine</th>
            <th>Generic</th>
            <th>Class</th>
            <th>Manufacturer</th>
            <th>Supplier</th>
            <th>Actions</th>
        </tr>
    </thead>
</table>

My JS script for Datatables

<script type="text/javascript">
    var oTable;

    $(document).ready(function() {
        oTable = $('#medicine').DataTable({
            "responsive": true,
            "processing": true,
            "serverSide": true,
            "ajax": "{!!route('medicine-data')!!}",
            "columns": [
                {data: 'product_name',       name: 'product_name'},
                {data: 'generic_name',       name: 'generic_name'},
                {data: 'product_class',       name: 'product_class'},
                {data: 'manufacturer_id',         name: 'manufacturer_id'},
                {data: 'supplier',           name: 'supplier'},
                {data: 'actions',            name: 'actions'},
            ]
        });                        
    });
</script>

I know this works, but I am not being able to make something similiar working with dimsav translatable package. This package establishes a one to many relationship. In this case one role has many translations of its name field. What I want to do is to eager load the translated name (in the fallback locale), avoiding all the joins. — Harley Fuagras, Mar 30 '16 at 11:28
I didn't get your above comment? can you explain more? can you show me your Role relationships and how you want to display them? — Qazi, Mar 30 '16 at 11:37
I meant that I am using another package, dimsav translatable (https://github.com/dimsav/laravel-translatable), to manage the translations of my models. The relationships stablished with that package are not explicit on the model. In my example above, at the controller with all the joins, you can see the database structure that dimsav translatable works with. I would like to eager load the translation of the role model with the fallback locale instead of using all those nasty joins. — Harley Fuagras, Mar 31 '16 at 11:02
please update your table structure, add screen shot or columns name in your question — Qazi, Mar 31 '16 at 12:00
I updated the question with the roles and role translations migrations and deleted the previous messages. — Harley Fuagras, Mar 31 '16 at 13:39

score 0 · Answer 2 · answered Mar 31 '16 at 15:25

Create relation method in Role Model
public function translation(){ return $this->hasMany("\App\Translation"); }

Then query in your RoleController like this $roles = Role::with('translation')->get();

Also, you can add conditions in translation $roles = Role::with('translation',function($query){ return $query->where('locale','en'); })->get();

Or follow this Laravel Doc https://laravel.com/docs/5.2/eloquent-relationships

score 0 · Answer 3 · edited May 23 '17 at 12:31

0

Maybe it can be done by passing a closure function in with().

See this answer https://stackoverflow.com/a/19921418/1061663

edited May 23 '17 at 12:31

Community

1
1

answered Apr 14 '16 at 10:35

aleixfabra

1,075
3
11
24

How can I use Eager Loading with Yajra Datatables and Dimsav Translatable in Laravel 5.2

3 Answers3