Problem with joins and searching with aliases

[kylescousin]

Data retrieval works:

public function getFichesDataTable() {
        $selection = DB::table('vouchers as v')
                            ->join('companies as c', 'v.company_id', '=', 'c.id')
                            ->select('v.id as id', 'v.name as name', 'c.name as company');

        $collection = Datatable::query($selection)
            ->showColumns('id', 'name', 'company')
            ->setSearchWithAlias()
            ->searchColumns('name', 'company', 'picture')
            ->make();
        return $collection;
    }

The problem is the ambiguous 'name' field, so I aliased the second name with 'company'. But now it searches "company LIKE %a%" - which won't work of course. How do you alias this correctly?

{"error":{"type":"Illuminate\Database\QueryException","message":"SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in where clause is ambiguous (SQL: select v.id as id, v.name as name, c.name as company, from fiches as v inner join companies as c on v.company_id = c.id where (name LIKE %a% or company LIKE %a%))"

[Chumper]

i will look into that a little closer

[lighta971]

up

[Chumper]

How about we extend the setSearchWithAlias() method like this:

->setSearchWithAlias(array(
    'c.name' => 'company'    
))

so we can run the query with the original name based on the alias?

[lighta971]

@Chumper Unfortunatly, there is ambiguous field problem with ORDER BY too. So I suggest somewhere to register column aliases and uses them when search and order by are executed. Just by replacing the normal column names by the aliases.

Or even better: No need to create new method to register these aliases, showColumns('posts.id', 'title')... would be great, so internaly detect prefixed columns and store these aliases to use later.

I looked into the source to make this happen but no luck for me on how to implement it. Surely you can do it much easily..

Thanks

[lighta971]

Suprised there is no much talk about this problem. It involve whatever query where you use joins and have same field names in used tables, like id or created_at.

[bhilleli]

Is there a solution for this or on how to set 'searchColumns(...)' for a 'join' column? i.e. I have a tracking table; each tracking row has a project_id, linking it to a project table...When I show the tracking datatable, I show the project.name and thats what I'd like to search.

In below I search project_id, but thats invisible to the user, I'd to search by the shown project.name

public function getDatatable()
{
    //XHR/AJAX To Grab Tracking Rows and Send to Table
    return Datatable::query(Tracking::with('project', 'task')->where('user_id',Auth::user()->id)->orderBy('id','desc'))
    ->showColumns('id')
    ->addColumn('project',function($model)
    {
        return array("id" => $model->project->id, "code" => $model->project->code, "name" => $model->project->name);
    })
    ->addColumn('task',function($model)
    {
        return array("id" => $model->task->id, "code" => $model->task->code, "name" => $model->task->name);
    })
    ->showColumns('time_start', 'time_end', 'notes')     
    ->searchColumns(array('id','project_id','task','time_start','time_end','notes'))
    ->orderColumns('id')
    //->orderColumns('id','proj')
    ->setAliasMapping()
    ->make();
}

[kylescousin]

Hey Chumper,

Any news about this? Or a workaround perhaps? Like for example a way to do this:

->searchColumns('table1.field', 'table2.field')

[chungth]

+1 for this !

I'm using Mysql 5.5 and facing with this problems. We need to using alias in HAVING clause instead of WHERE clause in MySQL.

Are there any ways to config havingColumns like searchColumns (using WHERE clause ) in Chumper/Datatable ?

[stevie5315]

Any updates on this issue? I really need a solution asap. Sombody please help!!

[kjdion84]

The problem is encapsulation. Remove the back quotes around the column names in the Datatable::query method and it works. You can not encapsulate column names which use aliases.