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; }
Advertisement
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.