1

I'm making a "simple" api for laravel. This api has to handle with filters, pagination and sorting the result. To make this I use laravel query builder. The problem is that it's making a select without a table name, for example:

select * order by `id` asc

My code:

public function index()
{
    $request = request();

    $query = DB::table('customers')->newQuery();

    // Orden
    if (request()->has('sort')) {

        // Multiorden
        $sorts = explode(',', request()->sort);
        foreach ($sorts as $sort) {
            list($sortCol, $sortDir) = explode('|', $sort);
            $query = $query->orderBy($sortCol, $sortDir);
        }
    } else {
        $query = $query->orderBy('id', 'asc');
    }

    //Filtros
    if ($request->exists('filter')) {
        $query->where(function($q) use($request) {
            $value = "%{$request->filter}%";
            $q->where('name', 'like', $value)
            ->orWhere('address', 'like', $value);
        });
    }

    $perPage = request()->has('per_page') ? (int) request()->per_page : null;
    $pagination = $query->get()->paginate($perPage);
    $pagination->appends([
        'sort' => request()->sort,
        'filter' => request()->filter,
        'per_page' => request()->per_page
    ]);

    return response()->json(
        $pagination
    );
}

Error:

Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1096 No tables used (SQL: select * order by id asc) in file C:\xampp\htdocs\iService\vendor\laravel\framework\src\Illuminate\Database\Connection.php on line 664

UPDATE:

return DB::table('customers')->get();

If i use this, the api works fine, I have more apis working. The problem is that I need Query Builder to handle filters, sort, etc...

enter image description here

3
  • are your database credentials correct? may I see a screenshot of it? Commented Apr 2, 2019 at 1:30
  • based on your error message, it seems your Query Builder was not able to find the table "customers" that's why you can't find the "id" column: (SQL: select * order by id asc) in file Commented Apr 2, 2019 at 1:53
  • @Noctis17 for me it's seems that I'm making a mistake on query builder, becaise with ->get() instead of ->newQuery() i get all the data back. If i have permissions error the other function will not work. Commented Apr 2, 2019 at 2:06

1 Answer 1

0

The problem was the way I instance a new query.

$query = DB::table('customers')->newQuery();

Correct:

$query = Model::query();

For my example:

$query = Customer::query();
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.