Laravel-datatables: Eager loading, search always returns empty set

[ryanrca]

Summary of problem or feature request

I hope this is just a coding issue and not a bug. I am using yajra/laravel-datatables successfully in many other locations in my site and have had good results. Whats different is, I am eager loading 4 tables.

I've followed the patterns in both:
https://datatables.yajrabox.com/eloquent/relationships
https://datatables.yajrabox.com/eloquent/joins

Both behave the same. No matter what I type in the search, I get 0 results and the datatable empties. Clearing search box brings back all results.

Code snippet of problem

Controller API endpoint:

    public function index()
    {
        $user = \Auth::user();
        $owned_items = OwnedItem::with(['product', 'license', 'collection', 'library'])
                                ->select('owned_items.*')
                                ->where('user_id', $user->id);
         return Datatables::of($owned_items)->make(true);
    }

Datatables

   var ownedtable = $('table.owned-items-data-table').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": "/api/account/owneditems",
        "fnDrawCallback": function( oSettings ) {
            addClickBindings();
        },
        order: [[ 4, "desc" ]],   // Sort on Date column, most recent first.
        columns: [
            { 
                render: function(data, type, row) {
                        return '<a href="javascript:void(0)" class="btn__play" data-player-play="' + row.product.id + '"></a>';
                    },
               searchable: false,
               sortable: false
            },
            { data: 'product.upc',  
                render: function(data, type, row) {
                        return '<a href="/royalty-free-music'+row.product.url_slug+'" class="upc" >'+row.product.upc+'</a>';
                    }
            },
            { data: 'product.name',
                 render: function(data, type, row) {
                        return '<a href="/royalty-free-music'+row.product.url_slug+'" >'+row.product.name+'</a>';
                    }
            },
            { data: 'license.title' },
            { data: 'created_at' },
            { data: 'num_downloads', searchable: false },
            { data: 'product',  searchable: false, 
                render: function(data, type, row) {
                        var baseLink = '/files/'+data.upc;
                        var output = '<div class="text-center txt_orange"> ' +
                                     '  <i class="txt_orange">Download</i></p> ' +
                                     '    <a href="'+baseLink+'/wav" title="Download '+data.name+'"> ' +
                                     '      <i class="txt_orange">.WAV</i> | ' +
                                     '    </a> ' +
                                     '    <a href="'+baseLink+'/mp3" title="Download '+data.name+'"> ' +
                                     '      <i class="txt_orange">.MP3</i> | ' +
                                     '    </a> ' +
                                     '    <a href="'+baseLink+'/aiff" title="Download '+data.name+'"> ' +
                                     '      <i class="txt_orange">.AIFF</i> ' +
                                     '    </a> ' +
                                     '</div> ';
                        return output;
                    }
            },
            { data: 'collection.name',
                render: function(data, type, row) {
                     var output = "";
                     if (row.pu_collection != null) {
                         output = '<a class="searchlinkcollection" href="#">'+row.collection.name+'</a> <br /> ';
                     }
                     return output;
                  }
            },
            { data: 'library.name',
                render: function(data, type, row) {
                     var output = "";
                     if (row.pu_library != null) {
                         output = '<a class="searchlinklibrary" href="#">'+row.library.name+'</a> <br /> ';
                     }
                     return output;
                  }
            },
            { render: function(data, type, row) {
                      return '<a href="#" id="cueSheetInfo" class="cueInfoPopup" data-rel="popup">Cue Sheet Info</a>';
                  },
               searchable: false   

            }
        ]
    });

What am I doing wrong?

System details

• Operating System
  ubuntu 14.04
• PHP Version
  5.6
• Laravel Version
  5.1
• Laravel-Datatables Version
  1.10.12

[ryanrca]

I suspect there might be an issue eager loading 4 additional tables?

[yajra]

Have you tried inspecting the ajax response and checking the generated sql?

[maengkom]

Hi @yajra , I tried this demo

https://datatables.yajrabox.com/eloquent/relationships

and trying to search relation user.name with value 'Oren', expected record with id: 2 as output, but no matching records found.

[ryanrca]

Yes, I think the demo is behaving similar. Try searching for "yahoo". There are many email addresses with yahoo in them, but as soon as I hit "y", I get no matching records and an empty table.

[yajra]

Confirmed this as a bug, seems like exact search is being done on relations instead of wildcard search. Will try to look at this again later when I had the chance. Thanks!

[ryanrca]

glad to know we aren't crazy :+1:

In the meantime, I have worked around the problem by creating a mysql view with all the eager loaded tables. This makes it appear as one table. laravel-datatables works quite well with this approach.

[maengkom]

Very nice. Thank you so much, running well here... 👍

[underdpt]

I'm not sure if this is the same, but I'm hitting this same issue.
This is my Eloquent datatable query:

        $query= Model::query()
            ->with(['model2.model3'])
            ->select('model.*');

And this are some of my columns:

return [
    ...
    'model2name' => ['name' => 'model2.name', 'data' => 'model2.name'],
    'model3name' => ['name' => 'model3.name', 'data' => 'model3.name'],
    ...
];

When I try to do a search, I receive the next error

QueryException in Connection.php line 761:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'model2.model3_id' in 'where clause' (SQL: select count(*) as aggregate from (select '1' as row_count from model1 and (LOWER(email_fecha) LIKE %ab%
or
(select count(1) from model2 where model1.model2_id = model2.id and LOWER(name) LIKE %ab%) >= 1
or
(select count(1) from model3 where model2.model3_id = model3.id and LOWER(name) LIKE %ab%) >= 1
or
LOWER(correcto) LIKE %ab% or LOWER(revisado) LIKE %ab% or LOWER(asunto) LIKE %ab%)) count_row_table)

I think it's missing some from clauses on the subqueries.
I've changed the model names for clarification.

[nessroman]

I was going to use DataTables 1.10.13 but due to this bug I could not do it. Currently still unresolved...