Laravel-datatables: [Bug] Error when searching self join relationships.

[coolflames]

Summary of problem or feature request

I have a problem when search data

Code snippet of problem

My Model

class Appl_menu extends Model
{
    protected $table = 'appl_menus';
    public $timestamps = false;

    protected $fillable = ['menu_id', 'menu_name', 'menu_module_id', 'menu_file_name', 'menu_id_parent',
        'menu_seqn', 'menu_enable_sts', 'created_by', 'created_date', 'updated_by', 'updated_date'];

    protected $primaryKey = 'menu_id';

    public function nullRelation()
    {
        return null;
    }

    public function menus ()
    {
        return $this->belongsTo(Appl_menu::class,'menu_id_parent');
    }

    public function menuss ()
    {
        return $this->hasMany(Appl_menu::class,'menu_id_parent','menu_id');
    }
}

my controller

public function index(Request $request, Builder $htmlBuilder)
    {
        if ($request->ajax()){
            $menuss = Appl_menu::with('modules', 'menus');
            //->orderBy('module_id');
            return Datatables::of($menuss)
                ->addColumn('action', function ($menu) {
                    return view('datatable._action', [
                        'model' => $menu,
                        'form_url' => route('menus.destroy', $menu->menu_id),
                        'edit_url' => route('menus.edit', $menu->menu_id),
                        'confirm_message' => 'Yakin mau menghapus menu ' . $menu->menu_name . '?'
                    ]);
                })->make(true);
        }

        $html = $htmlBuilder
            ->addColumn(['data' => 'menu_id', 'name' => 'menu_id', 'title' => 'ID'])
            ->addColumn(['data' => 'menu_name', 'name' => 'menu_name', 'title' => 'Nama Menu'])
            ->addColumn(['data' => 'modules.module_name', 'name' => 'modules.module_name', 'title' => 'Module'])
            ->addColumn(['data' => 'menus.menu_name', 'name' => 'menus.menu_name', 'title' => 'Parent', 'defaultContent' => ''])
            ->addColumn(['data' => 'action', 'name' => 'action', 'title' => '', 'orderable' => false, 'searchable' => false]);

        return view('menus.index')->with(compact('html'));
    }

Error output when search

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "as"
LINE 1: ...ST("appl_menus" as "laravel_reserved_0.menu_name" as TEXT)) ...
^ (SQL: select count(*) as aggregate from (select '1' as "row_count" from "appl_menus" where (LOWER(CAST("appl_menus"."menu_id" as TEXT)) LIKE %se% or LOWER(CAST("appl_menus"."menu_name" as TEXT)) LIKE %se% or (select count(1) from "appl_modules" where "appl_menus"."menu_module_id" = "appl_modules"."module_id" and LOWER(CAST("appl_modules"."module_name" as TEXT)) LIKE %se%) >= 1 or (select count(1) from "appl_menus" as "laravel_reserved_0" where "laravel_reserved_0"."menu_id" = "appl_menus"."menu_id_parent" and LOWER(CAST("appl_menus" as "laravel_reserved_0.menu_name" as TEXT)) LIKE %se%) >= 1)) count_row_table)

javascript output

<script type="text/javascript">(function(window,$){window.LaravelDataTables=window.LaravelDataTables||{};window.LaravelDataTables["dataTableBuilder"]=$("#dataTableBuilder").DataTable({"serverSide":true,"processing":true,"ajax":"","columns":[{"data":"menu_id","name":"menu_id","title":"ID","orderable":true,"searchable":true},{"data":"menu_name","name":"menu_name","title":"Nama Menu","orderable":true,"searchable":true},{"data":"modules.module_name","name":"modules.module_name","title":"Module","orderable":true,"searchable":true},{"data":"menus.menu_name","name":"menus.menu_name","title":"Parent","defaultContent":"","orderable":true,"searchable":true},{"data":"action","name":"action","title":"","orderable":false,"searchable":false}]});})(window,jQuery);
</script>

What is this error and why that is occurred?

System details

• Operating System windows10
• PHP Version 5.6.25
• Laravel Version 5.2
• Laravel-Datatables Version 6.22

[yajra]

@coolflames can you try returning the query if it works? I think I've seen this issue before and this might be related to self join handling of the DB driver.

return $menuss = Appl_menu::with('modules', 'menus')->get();

If this works, then there must be a bug in the package when compiling self join tables. Thanks for reporting.

[coolflames]

the query work well the first time populate data, problems appear when it starts input search

[lk77]

Hello,

i think i have the same issue on a n.n relation.

i have a count columns that works on init but when searching the query string is interpreted as a column name in the like clause, and it end up with a sql error.

I don't want that column to be searchable anyway, the searchable parameter is false when i'm doing a dump of $request->query. I don't understand why searchableColumnIndex() still return that column.

i'm on 6.24.4, do i need to update laravel to 5.4 and datatables to 7.*?

thanks

[tad3j]

I have another example of failing self joins, where email is having recovery email (one to many) and forwarding to emails (many to many).

Here are the relevant parts of the Email model (relations):

    public function recoveryEmail()
    {
        return $this->belongsTo(EmailAccount::class);
    }

    public function forwardingToEmailAccounts()
    {
        return $this->belongsToMany(
            EmailAccount::class,
            'emails_have_forwarding_emails',
            'forwarding_to_email_id',
            'forwarded_from_email_id'
        );
    }

Here is the failing query, where recoveryEmail is referencing email_accounts table and I'm querying base table with the same name:

EmailAccount::select('email_accounts.*')
    ->with(['recoveryEmail' => function ($query) {
        $query->select(['id', 'email']);
    }]);

And the exception + failing MySQL query:

Exception Message:??SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'email_accounts' (SQL: 
select `email_accounts`.* 
from `email_accounts` 
left join `email_accounts` on `email_accounts`.`recovery_email_id` = `email_accounts`.`id` 
order by `email_accounts`.`email` desc limit 10 offset 0
)

The problem is similar with forwardingToEmails.

I guess this is more of a problem with Eloquent not allowing aliasing, that's why DataTables package doesn't know how to order the data (since both tables -base and joined use the same name).

Was wondering if there may be a workaround for it?

[yajra]

Fixed via #2051.

[dhcmega]

Hi, I'm using v9.4.1 but still getting the unique table error on self joining for parent relation.
Should I have to modify my code?

My datatables index method and the relation:

    public function query(Topic $model)
    {
        $model = $model->newQuery();

        $model->with('parent')->select('topics.*');

        return $model;
    }

    public function parent()
    {
        return $this->belongsTo('App\Models\Topic', 'parent_id');
    }

Thanks!

[yajra]

@dhcmega the PR was reverted as it causes another issue. Self join is currently not yet supported again. Thanks!

[rezasaberi]

So any suggestion on this yet? I do not even have a self-joined table, but I have same issue.