Laravel mysql select all except an array of values ?

Hello, I am developing an application using yajra-datatables where I need to display a datatable of a certain model except for certain values of an attribute, how can I use laravel eloquent to do that ?

Here is my code :

Controller :

function getMateriel()
    {
        $data = Materiel::join('Types', 'Materiels.type_id', '=', 'Types.type_id')->join('Users', 'Materiels.user_id', '=', 'Users.id')
            ->select('Materiels.id', 'Materiels.mat_code', 'Materiels.mat_designation', 'Materiels.mat_ns', 'Types.type_designation', 'Materiels.mat_description', 'Materiels.note', 'Users.username', 'Materiels.mat_date');
        return DataTables::of($data)
            ->addColumn('modifier', function ($data) {
                return '<a href="#" class="Modifier" id="' . $data->id . '"><i class="material-icons">edit</i></a>';
            })
            ->addColumn('supprimer', function ($sup) {
                return '<a href="#" class="Supprimer" id="' . $sup->id . '"><i class="material-icons">delete</i></a>';
            })
            ->addColumn('check', function ($check) {
                return '<input type="checkbox" required="required" name="check" class="check" id="' . $check->id . ' value="' . $check->id . '">';
            })
            ->rawColumns(['modifier', 'supprimer', 'check'])
            ->make(true);
    }

View :

$('#m_table').DataTable({
                "bProcessing": true,
                "sAjaxSource": "{{ route('Materiel.getMateriel') }}",
               ...
                "columns": [{
                        "data": "id"
                    },
                    {
                        "data": "mat_code"
                    },
                    {
                        "data": "mat_designation"
                    },
                    {
                        "data": "mat_ns"
                    },
                    {
                        "data": "type_designation"
                    },
                    {
                        "data": "mat_description"
                    },
                    {
                        "data": "note"
                    },
                    {
                        "data": "username"
                    },
                    {
                        "data": "mat_date"
                    },
                    {
                        "data": "check"
                    }
                ]
            });

rodrigo.pedra

6 years ago

Best Answer

Level 56

Can you use whereNotIn:

$data = Materiel::join('Types', 'Materiels.type_id', '=', 'Types.type_id')
    ->join('Users', 'Materiels.user_id', '=', 'Users.id')
    ->select('Materiels.id', 'Materiels.mat_code', 'Materiels.mat_designation', 'Materiels.mat_ns', 'Types.type_designation', 'Materiels.mat_description', 'Materiels.note', 'Users.username', 'Materiels.mat_date')
->whereNotIn('column', ['foo', 'bar', 'baz']);

In the example above replace column with the column name you want to filter, and the array with the values you don't want it to have.

PhoeniX5

5 years ago

Level 1

@rodrigo.pedra Hello thank you for your reply I have an issue trying to give whereNotIn parameter like this :

$mat_codes = [];
        if($request->mat_codes == null) {
            array_push($mat_codes, "");
        }
        else {
            array_push($mat_codes, $request->mat_codes);
        }

 $data = Materiel::join('Types', 'Materiels.type_id', '=', 'Types.type_id')->join('Users', 'Materiels.user_id', '=', 'Users.id')
            ->select('Materiels.id', 'Materiels.mat_code', 'Materiels.mat_designation', 'Materiels.mat_ns', 'Types.type_designation', 'Materiels.mat_description', 'Materiels.note', 'Users.username', 'Materiels.mat_date')->whereNotIn('Materiels.mat_code', $mat_codes);

But It keeps not working If I do :

dd($mat_codes);

array:1 [
  0 => "xxx,123"
]

Please help!

rodrigo.pedra

5 years ago

Level 56

It seems your frontend is sending mat_codes as an string with the codes concatenated by a string.

Try this:

$mat_codes = [];

if ($request->mat_codes !== null) {
    $mat_codes = explode(',', $mat_codes);
}

It doesn't seem that the mat_codes request variables can contain spaces between commas, but if that is a possibility, I would add:

$mat_codes = [];

if ($request->mat_codes !== null) {
    $mat_codes = array_map('trim', explode(',', $mat_codes));
}

Just to be sure if an element on the list contains a space, such as xxx, 123, it becomes the array ['xxx', '123']

1 like

Please or to participate in this conversation.