Question posted in 
Below is my query in laravel project.In this query purchase_return_products.purchase amount actually exists but this show error
‘[2024-07-14 16:28:02] local.ERROR: Errors: SQLSTATE[42S22]: Column
not found: 1054 Unknown column
‘purchase_return_products.purchase_amount’ in ‘field list’
(Connection: mysql, SQL: select count(*) as aggregate from (select
IFNULL(SUM(purchase_products.quantity), 0) as purchase_quantity,
IFNULL(SUM(purchase_products.purchase_amount), 0) as purchase_amount,
purchase_products.product_id, IFNULL(SUM(sale_products.quantity),
0) as sale_quantity, IFNULL(SUM(sale_products.sale_purchase_amount),
0) as sale_purchase_amount,
IFNULL(SUM(purchase_return_products.quantity), 0) as
purchase_return_quantity,
IFNULL(SUM(purchase_return_products.purchase_amount), 0) as
purchase_return_amount fromproductsleft join (SELECT product_id,
SUM(purchase_products.quantity) as quantity,
SUM(purchase_products.purchase_amount) as purchase_amount FROM
purchase_products GROUP BY product_id) as purchase_products on
purchase_products.product_id=products.identityleft join
(SELECT product_id, SUM(sale_products.quantity) as quantity,
SUM(sale_products.sale_purchase_amount) as sale_purchase_amount FROM
sale_products GROUP BY product_id) as sale_products on
sale_products.product_id=products.identityleft join (SELECT
product_id, SUM(purchase_return_products.quantity) as quantity,
SUM(purchase_return_products.purchase_amount) as
purchase_return_amount FROM purchase_return_products GROUP BY
product_id) as purchase_return_products on
purchase_return_products.product_id=products.identitywhere
products.store_id= 2024061225851482 group by
purchase_products.product_idhaving (purchase_quantity –
(purchase_return_quantity + sale_quantity)) > 0) as
aggregate_table)’
Please help me solving this problem.
$data = $this->helper_object->getModel('Product')::select(
DB::raw('IFNULL(SUM(purchase_products.quantity), 0) as purchase_quantity'),
DB::raw('IFNULL(SUM(purchase_products.purchase_amount), 0) as purchase_amount'),
'purchase_products.product_id',
DB::raw('IFNULL(SUM(sale_products.quantity), 0) as sale_quantity'),
DB::raw('IFNULL(SUM(sale_products.sale_purchase_amount), 0) as sale_purchase_amount'),
DB::raw('IFNULL(SUM(purchase_return_products.quantity), 0) as purchase_return_quantity'),
DB::raw('IFNULL(SUM(purchase_return_products.purchase_amount), 0) as purchase_return_amount')
)
->leftJoin(DB::raw('(SELECT product_id, SUM(purchase_products.quantity) as quantity, SUM(purchase_products.purchase_amount) as purchase_amount FROM purchase_products GROUP BY product_id) as purchase_products'), function($join) {
$join->on('purchase_products.product_id', '=', 'products.identity');
})
->leftJoin(DB::raw('(SELECT product_id, SUM(sale_products.quantity) as quantity, SUM(sale_products.sale_purchase_amount) as sale_purchase_amount FROM sale_products GROUP BY product_id) as sale_products'), function($join) {
$join->on('sale_products.product_id', '=', 'products.identity');
})
->leftJoin(DB::raw('(SELECT product_id, SUM(purchase_return_products.quantity) as quantity, SUM(purchase_return_products.purchase_amount) as purchase_return_amount FROM purchase_return_products GROUP BY product_id) as purchase_return_products'), function($join) {
$join->on('purchase_return_products.product_id', '=', 'products.identity');
})
->where('products.store_id', $store_id)
->when($query, function ($queryBuilder) use ($query) {
$queryBuilder->where(function ($innerQueryBuilder) use ($query) {
$innerQueryBuilder->where('products.name', 'like', "%$query%")
->orWhere('products.sku_code_number', 'like', "%$query%");
});
})
->when($brand, function ($queryBuilder) use ($brand) {
$queryBuilder->where(function ($innerQueryBuilder) use ($brand) {
$innerQueryBuilder->where('products.brand', $brand);
});
})
->when($category, function ($queryBuilder) use ($category) {
$queryBuilder->where(function ($innerQueryBuilder) use ($category) {
$innerQueryBuilder->where('products.category', $category);
});
})
->groupBy('purchase_products.product_id')
->havingRaw('(purchase_quantity - (purchase_return_quantity + sale_quantity)) > 0')
->orderBy('products.sku_code_number', 'asc')
->paginate(30);
This is my purchase_return_products table schema :-
Schema::create('purchase_return_products', function (Blueprint
$table) {
$table->id();
$table->string('identity',16)->unique()->index();
$table->string('purchase_identity',16)->index();
$table->string('unique_identity',16)->index();
$table->string('invoice_id',16)->index();
$table->string('warehouse_id',16)->index();
$table->string('store_id',16)->index();
$table->string('parent_id',16)->index();
$table->string('party_id',16)->index();
$table->string('product_id',16)->index();
$table->string('variant_id',16)->index();
$table->string('serial',120)->nullable()->index();
$table->decimal('quantity', 25, 3)->index();
$table->decimal('purchase_price', 25, 3)->index();
$table->decimal('purchase_amount', 25, 3)->index();
$table->decimal('unit_offer', 25, 3)->nullable()->index();
$table->decimal('total_offer', 25, 3)->nullable()->index();
$table->timestamps();
$table->date('date')->index();
$table->string('status',10)->default('Pending')->index();
$table->string('added_by',16)->index();
$table->string('updated_by',16)->nullable();
This is my sale_products table schema :-
Schema::create('sale_products', function (Blueprint $table) {
$table->id();
$table->string('identity',16)->unique()->index();
$table->string('warehouse_id',16)->index();
$table->string('store_id',16)->index();
$table->string('parent_id',16)->index();
$table->string('party_id',16)->index();
$table->string('purchase_identity',16)->index();
$table->string('product_id',16)->index();
$table->string('variant_id',16)->index();
$table->string('invoice_id',16)->index();
$table->string('serial',120)->nullable()->index();
$table->string('sale_type',10)->default('Sale')->index();
$table->decimal('quantity', 25, 3)->index();
$table->decimal('purchase_price', 25, 3)->index();
$table->decimal('sale_purchase_amount', 25, 3)->index();
$table->decimal('sale_price', 25, 3)->index();
$table->decimal('sale_amount', 25, 3)->index();
$table->decimal('discount_percent', 25, 3)->nullable()->index();
$table->decimal('total_discount', 25, 3)->nullable()->index();
$table->string('offer_id',16)->nullable()->index();
$table->decimal('unit_offer', 25, 3)->nullable()->index();
$table->decimal('total_offer', 25, 3)->nullable()->index();
$table->decimal('profit', 25, 3)->nullable()->index();
$table->date('warranty_date')->nullable()->index();
$table->timestamps();
$table->date('date')->index();
$table->string('status',10)->default('Pending')->index();
$table->string('added_by',16)->index();
$table->string('updated_by',16)->nullable();
This is my purchase_products table schema :-
Schema::create('purchase_products', function (Blueprint $table) {
$table->id();
$table->string('identity',16)->unique()->index();
$table->string('warehouse_id',16)->index();
$table->string('store_id',16)->index();
$table->string('parent_id',16)->index();
$table->string('party_id',16)->index();
$table->string('product_id',16)->index();
$table->string('variant_id',16)->index();
$table->string('invoice_id',16)->index();
$table->string('sale_status',9)->default('Running')->index();
$table->string('serial',120)->nullable()->index();
$table->string('purchase_type',10)->index();
$table->decimal('quantity', 25, 3)->index();
$table->decimal('purchase_price', 25, 3)->index();
$table->decimal('purchase_amount', 25, 3)->index();
$table->decimal('unit_offer', 25, 3)->nullable()->index();
$table->decimal('total_offer', 25, 3)->nullable()->index();
$table->date('expire_date')->nullable()->index();
$table->timestamps();
$table->date('date')->index();
$table->string('status',10)->default('Pending')->index();
$table->string('added_by',16)->index();
$table->string('updated_by',16)->nullable();
2
Answers
for fix the issue:
Check Table Schema: Double-check your purchase_return_products table schema using a tool like phpMyAdmin or Laravel’s built-in schema dumper. Verify if the purchase_amount column actually exists.
Typo in Query: If the column exists but is misspelled in the query, correct the typo. Ensure you’re using the exact name of the column, including case sensitivity.
Missing Migration: If the purchase_amount column was recently added to the table, you might need to run a new migration to reflect the schema change in your Laravel application.
1- Use tools like Laravel Tinker to test your queries in isolation before integrating them into your application.
2- Consider using a query builder like Laravel’s Query Builder or Eloquent ORM for a more maintainable and readable way to construct your queries.
In the subquery for `purchase_return_products’ you already renamed/aliased purchase_amount as purchase_return_amount :
But then on the main query you still refer to it as purchase_amount, as in:
Change that to
IFNULL(SUM(purchase_return_products.purchase_return_amount ), 0) as purchase_return_amount