Laravel 5 collection issue: Where not equal to

Question

I am currently working on a modal where a user can insert an excel file. The task of the system is to upload and/or add a new database record if the records are new or identical to what exists in the database. BUT it also needs a delete function for getting rid of those records where the slug column is not identical to the name column.

At the moment I am using Laravel 5.3, and this is my controller as it is now:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Product;
use App\Http\Requests;
use Illuminate\Support\Facades\DB;
use Input;
use Maatwebsite\Excel\Facades\Excel;

class ProductsController extends Controller {

public function importExcel(Request $request) {
    if (Input::hasFile('productFile')) {
        $path = Input::file('productFile')->getRealPath();
        $checkbox = Input::get('productCheckbox');
        $data = Excel::load($path, function($reader) {
        })->get();

        if (!empty($data) && $data->count()) {
            foreach ($data as $key => $value) {
                $product = Product::all()->where('slug', $value->slug)->first();
                $product_false = Product::all()->where('slug', '!=' , 'name')->get();

                if ($product_false !== null){
                    //delete row if slug does not matches name
                    dd($product_false);
                }

The dd above returns all products, so the collection query is not working properly (see below for the raw SQL that I am trying to run in this collection)

                if ($product !== null) {
                    //update row if exist
                    $product->name = $value->name;
                    $product->description = $value->description;
                    $product->price = $value->price;
                    $product->save();
                } else {
                    //add new row if not exist
                    $product = new Product;
                    $product->slug = $value->slug;
                    $product->name = $value->name;
                    $product->description = $value->description;
                    $product->price = $value->price;
                    $product->save();
                }

            }
            header("Location: /products");
        }
    }
}

}

This is the Product model:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{

/**
 * The attributes that are mass assignable.
 *
 * @var array
 */
protected $fillable = [
    'slug', 'name', 'description', 'price',
];
}

Here is the PHPMyAdmin raw SQL (which works) that I basically am looking for to use in the collection:

SELECT * FROM `products` WHERE `slug` != `name`

I hope someone can help me out from this pit. I have been sailing the waves of the internet for about 12 hours now just to get this done.

~ nitsuJ

Replacing '!=' with '<>' resulted in: http://prntscr.com/cili89 — Justin Boxem, Sep 16 '16 at 07:44
Isn't this wrong `$product_false = Product::all()->where('slug', '!=' , 'name')->get();` ? Shouldn't be `$product_false = Product::all()->where('slug', '!=' , $value->slug)->get();` — KuKeC, Sep 16 '16 at 08:09
Hey thanks, but, it was not working. Instead I got: http://prntscr.com/cimdm8 — Justin Boxem, Sep 16 '16 at 09:13

score 2 · Answer 1 · answered Aug 14 '19 at 12:12

Collections, eloquent and query builder are not the same. Collection provide a bunch of methods to work on arrays, rather then on the database or model.

In collection context whereNot() is not available.

but the same function can be achieved through whereNotIn('key', [value])

collect([
    [
      'name' => 'foo',
      'rank' => 2
    ],[
      'name' => 'bar',
      'rank' => 3
    ],[
      'name' => 'foobar',
      'rank' => 4
    ],
 ])->whereNotIn('rank', [4])

same as where rank not in (4)

Leguam · Answer 2 · 2016-09-16T07:59:23.467

1

Change

$product = Product::all()->where('slug', $value->slug)->first();
$product_false = Product::all()->where('slug', '!=' , 'name')->get();

Into

$product = Product::where('slug', $value->slug)->first();
$product_false = Product::where('slug', '!=' , 'name')->get();

edited Sep 16 '16 at 07:59

answered Sep 16 '16 at 07:43

Leguam

1,202
2
14
35

I tried that before. It dd's all records; including the ones where slug and name are actually equal: http://prntscr.com/cilj0n – Justin Boxem Sep 16 '16 at 07:47
That's probably because the third argument is parsed as a string, try using products.slug and products.name instead of the column names only. You can also use, maybe a better way: $products_false = Product::whereRaw('slug != name'); – Jan Willem Sep 16 '16 at 08:04
product.slug / product.name result in: http://prntscr.com/cily1l ur whereRaw suggestion results in: http://prntscr.com/cilyjg for future reference, I just need to run a test on the product model and grab the records where the 'slug' column is NOT equal to the 'name' column. – Justin Boxem Sep 16 '16 at 08:30
Thats because, when using whereRaw, you need to append ->get() to it, as now just the query builder is returned. – Jan Willem Sep 16 '16 at 11:37

score 0 · Answer 3 · answered Sep 16 '16 at 09:15

0

Try this

$product = Product::where('slug', $value->slug)->first();
$product_false = Product::whereRaw('slug != name')->get();

Simple where won't work as it compares products.slug with "name"(string).

answered Sep 16 '16 at 09:15

linuxartisan

2,396
3
21
40

score 0 · Accepted Answer · answered Sep 16 '16 at 13:26

I managed to solve it.

$data = Excel::load($path, function($reader) {

            $importedSlugs = $data->select(array('slug'))->toArray();
                    //collection of imported slugs
                    $collectionOfImportedSlugs = collect($importedSlugs)->flatten()->all();

                    //get all product slugs
                    $productSlugs = Product::all()->pluck('slug');

                    //get all different slugs!
                    $diffSlugsArray = $productSlugs->diff($collectionOfImportedSlugs)->all();
                    //dd($diffSlugsArray);

                    foreach ($diffSlugsArray as $diffSlug) {
                        $product_false = Product::all()->where('slug',     $diffSlug)->first();

                        echo $product_false->slug . 'has been deleted!';

                        $product_false->delete();
                    }
        })->get();

Laravel 5 collection issue: Where not equal to

4 Answers4