Laravel 8 pagination Error – Column ‘is_deleted’ in where clause is ambiguous

I am new to Laravel and have stumbled upon a problem. Would you mind advising the right way of doing it if I am doing it incorrectly? For example, I have 3 tables: product, product_category, product_image, as below.

CREATE TABLE `product` (
  `id` int(11) NOT NULL,
  `product_category_id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `short_description` text DEFAULT NULL,
  `specification` text DEFAULT NULL,
  `size` text DEFAULT NULL,
  `is_deleted` char(1) NOT NULL DEFAULT 'n',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `product_category` (
  `id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `image` varchar(150) DEFAULT NULL,
  `is_deleted` char(1) NOT NULL DEFAULT 'n',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `product_image` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `image` varchar(150) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Question 1

I am paginating my data on the frontend, but I am getting the following error with the below code.

Column ‘is_deleted’ in where clause is ambiguous.

$objData = DB::table('product')->join('product_category', 'product_category.id',
    'product.product_category_id')
    ->where($arrWhere)
    ->where(function ($query) {
        global $request;

        // If search sent
        if ($request->query('search')) {
            $query->orWhere('name', 'like', '%' . $request->query('search') . '%')
                ->orWhere('short_description', 'like', '%' . $request->query('search') . '%')
                ->orWhere('specification', 'like', '%' . $request->query('search') . '%')
                ->orWhere('size', 'like', '%' . $request->query('search') . '%');
        }
    })->orderBy($request->query('sortBy'), $strSortType)->select('product.*, product_category.*, 
        product.is_deleted as product_is_deleted, product_category.is_deleted as product_category_is_deleted')
    ->paginate($request->query('rowsPerPage'));

Question 2

I have a product_image table, and I want to attach the product images with each product as an array of $product->images. Currently, I am doing the below, it is working, but I am not sure it’s the right way. Can someone please advise a cleaner Laravel way of doing this?

// Get product_ids
$arrProductId = array();
foreach ($objData as $objProduct) {
    $arrProductId[] = $objProduct->id;
}

// Get product images
$arrProductImage = ProductImage::whereIn('product_id', $arrProductId)->get();

// Bind the product images to the rows to return
$arrRows = array();
foreach ($objData->items() as $objProduct) {

    $objProduct->images = array();

    foreach ($arrProductImage as $objProductImage) {
        if ($objProductImage->product_id == $objProduct->id) {
            $objProduct->images[] = $objProductImage->image;
        }
    }
    $arrRows[] = $objProduct;
}

Answer

First of all

Remake your migration and put

$table->boolean('is_deleted')->default(0);

Second of all, try and rething your relationship between tables. You can accomplish the same thing with Eloquent.

Class Product
{
    public function category(){
        return $this->belongsTo(Category::class)->select(['column1', 'column2']);
    }
}

Class Category
{
    public function products(){
    return $this->hasMany(Product::class);
}

Query example:

$products = Product::with('category')->where('some_field', $filter['field']);

if ($filter['someOtherField']) {
   $products->where('some_other_field', $filter['someOtherField']);
}

$products->paginate(15);

in blade do

{{$products->links}} 

to show the default pagination.