dataTables erreor Column not found: 1054 Unknown column

Level 58

The error message indicates that the column sections.nom_region is not found in the database. Double-check that the column exists and is spelled correctly.

If the column exists, it's possible that the search query is not properly formatted. Try modifying the search query to explicitly reference the table name for the nom_region column:

$instances = DB::table('sections')
    ->leftJoin('regions', 'sections.id_region', '=', 'regions.id')
    ->leftJoin('dioceses', 'sections.id_diocese', '=', 'dioceses.id')
    ->select('sections.nom_section', 'dioceses.nom_diocese', 'regions.nom_region')
    ->addSelect(DB::raw('COALESCE(regions.nom_region, "") as nom_region'))
    ->where(function($query) use ($search) {
        $query->where('sections.nom_section', 'LIKE', "%$search%")
              ->orWhere('regions.nom_region', 'LIKE', "%$search%")
              ->orWhere('dioceses.nom_diocese', 'LIKE', "%$search%");
    })
    ->paginate(30);

This modified query adds a where clause to search for the nom_region column explicitly. The $search variable should contain the search term entered by the user.

Note that this solution assumes that the nom_region column exists in the regions table. If it exists in a different table, modify the query accordingly.

Level 104

I don't see the constraints in the Query Builder; where do they come from?

where (
  LOWER(sections.nom_section) LIKE %k% 
  or LOWER(sections.nom_region) LIKE %k% 
  or LOWER(sections.nom_diocese) LIKE %k%)
)

Level 1

@tykus I didn't know that you had to write the search function explicitly. I correct and come back.

Level 104

@pkouadio no, you misunderstand me... I was saying that I cannot see in the PHP code where you add the constraints to the Query Builder - i.e. the is no where in the query:

$instances = DB::table('sections')
    ->leftJoin('regions', 'sections.id_region', '=', 'regions.id')
    ->leftJoin('dioceses', 'sections.id_diocese', '=', 'dioceses.id') 
    ->select('sections.nom_section', 'dioceses.nom_diocese', 'regions.nom_region')
    ->addSelect(DB::raw('COALESCE(regions.nom_region, "") as nom_region')) 
    ->paginate(30);

Level 1

@tykus After modification I have the following function but I still have the same error.

public  function  data(Request  $request)
{
$search = $request->input('search.value');
$instances = DB::table('sections')
->leftJoin('regions', 'sections.id_region', '=', 'regions.id')
->leftJoin('dioceses', 'sections.id_diocese', '=', 'dioceses.id')
->select('sections.nom_section', 'dioceses.nom_diocese', 'regions.nom_region')
->addSelect(DB::raw('COALESCE(regions.nom_region, "") as nom_region'))
->where(function ($query) use ($search) {
$query->where('sections.nom_section', 'LIKE', "%$search%")
->orWhere('regions.nom_region', 'LIKE', "%$search%")
->orWhere('dioceses.nom_diocese', 'LIKE', "%$search%");
});
return  Datatables::of($instances)->make(true);
}

Level 104

@pkouadio can you show how you setup the Datatable in your view?

Level 1

@tykus I see this query is automatically generated by dataTables to perform a search in the returned fields to build the table. The problem is that I have a join between three tables but dataTables does not seem to know the difference between them.

Level 104

@pkouadio right; this is why I asked to see how the datatable is set up in the view. Do you qualify the nom_region as regions.nom_region?

Level 1

@tykus there is

$(document).ready(function() { $('#instances-table').DataTable({ "processing": true, "serverSide": true, "ajax": "{{ route('instances.data') }}", "columns": [ {"data": "nom_section"}, {"data": "nom_region"}, {"data": "nom_diocese"}]}); });

Best Answer

Level 104

@pkouadio try to fully qualify the column, like this:

{ data: 'nom_region', name: 'regions.nom_region' },

1 like