How to generate a dynamic mysql query in laravel?

Question

I would like the users to choose which fields they want to see and which ones they don't.This is my API URI which is a get request.

/api/v1/admin/users/1?fields=email,id

This is my code :

if (isset($request->fields)) {
            $temp = null;
            $items = explode(',', $request->fields);
            foreach ($items as $item) {
                $temp = $temp . "'" . $item . "',";
            }
            $fields = trim($temp, ',');

            $query = User::query();
            $query->select($resourceOptions['fields']);
            $query->get();
        }

but I got this error:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column ''email','id'' in 'field list' (SQL: select `'email','id'` from `users` where `users`.`deleted_at` is null)"

Any help (or if someone can point me towards a good tutorial) would be greatly appreciated! Thanks

Vikash Pathak · Accepted Answer · 2019-06-10 08:56:00Z

2

In laravel you can add query with where() clause. But in your case. It's not required.

Just run this.

if (isset($request->fields)) {
        $users = User::select(DB::raw($request->fields))->get();
        dd($users);
}

edited Jun 10, 2019 at 8:56

answered Jun 10, 2019 at 8:53

Vikash Pathak

3,6121 gold badge22 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vikash Pathak Over a year ago

@Maryam let me know if it helps.

Maryam Over a year ago

I tested and it is not true

Vikash Pathak Over a year ago

@Maryam Try now.

abhishek chopda Over a year ago

i think he is passing request wrong way. 'email','id' look at this.

MohammadReza Abbasi · Accepted Answer · 2019-06-10 08:57:07Z

0

import

use Illuminate\Support\Facades\DB;

and

if (isset($request->fields)) {
    $query = User::select(DB::raw($request->fields))->get();
}

answered Jun 10, 2019 at 8:57

MohammadReza Abbasi

5003 silver badges13 bronze badges

Comments

Kenrro · Accepted Answer · 2019-06-10 09:29:24Z

0

Where does $resourceOptions['fields'] comes from?

You can use the array of items in the select function, like this:

if (isset($request->fields)) {
    $items = explode(',', $request->fields);
    $query = User::query();
    $query->select($items);
    $query->get();
}

Or like this

if (isset($request->fields)) {
    $query = User::query()->select(explode(',', $request->fields))->get();
}

answered Jun 10, 2019 at 9:29

Kenrro

463 bronze badges

Collectives™ on Stack Overflow

How to generate a dynamic mysql query in laravel?

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related