CODEWITHSUNDEEP
phparrayslaravelparameter-passingyajra-datatable
dbudimir
dbudimir

Reputation: 301

Laravel pass list parameters (columns) in select query

I use three table spareparts, categories and parameter and YajraDatatables.

My controller action is:

    public function anyData()
    {
        $spareparts_param_list = Sparepart::all();
        $list='';
        foreach ($spareparts_param_list as $value) {
            foreach ($value->category->parameter as $par_list) {
                $list .=  $par_list->Name.',';
            }
        }
        $spareparts = Sparepart::
            join('cars', 'spareparts.car_id', '=', 'cars.id')
            ->select(['spareparts.id', 'cars.Brend', 'spareparts.Model', $list]);
        $datatables = app('datatables')->of($spareparts);

        return $datatables->make();
    }

My array list $list print parameters such as color,type,tires,. How to pass $list array in select query?

Upvotes: 0

Views: 2535

Answers (2)

Mohammad Zare Moghadam
Mohammad Zare Moghadam

Reputation: 688

You can use whereIn:

public function anyData()
{
    $spareparts_param_list = Sparepart::all();
    $list = [];
    foreach ($spareparts_param_list as $value) {
        foreach ($value->category->parameter as $par_list) {
            $list[] = $par_list->Name;
        }
    }
    $spareparts = Sparepart::
        join('cars', 'spareparts.car_id', '=', 'cars.id')
        ->whereIn('Name', $list)
        ->select(['spareparts.id', 'cars.Brend', 'spareparts.Model']);
    $datatables = app('datatables')->of($spareparts);

    return $datatables->make();
}

You can run this raw Mysql command:

select sp.id as sp_id, sp.model as sp_model, c.brend as car_brend, json_arrayagg(p.name) as p_name
from spareparts as sp
join cars as c on sp.car_id=c.id
join categories as cat on sp.category_id=cat.id
join parameters as p on cat.id=p.category_id
group by sp.model;

Above command will give you a result like the following:

1 | Audi A6 door | Audi A6 | ["color", "window"]

The last column will be a json column of all parameters related to each category.

Upvotes: 1

cmac
cmac

Reputation: 3268

You can use whereIn(), something like this:

$users = DB::table('users')
                ->whereIn('id', [1, 2, 3])
                ->get();

Docs - https://laravel.com/docs/5.7/queries#where-clauses

Upvotes: 1

Related Questions