How to write this SQL query in Laravel?

Question

I want to write this query in laravel,

My table structures are

product table

Product_Id int(10)

Product_Name varchar(100)

Product_Description varchar(300)

Category_ID int(11)

Brand_ID int(11)

2.Stock_IN

stock_ID int(10)

Product_ID int(11)

Product_Count int(11)

Order_No int(11)

Supplier_ID int(11)

Order_date text

stock_out

stock_out_id int(10)

Product_ID int(11)

Product_Count int(11)

Requisition_No int(11)

Requisition_By varchar(100)

Recipient varchar(100)

My Query:

SELECT X.Product_ID,X.Product_Name,X.Count_StockIN,IFNULL(Y.Count_StockOut,0) Count_StockOut,(X.Count_StockIN-IFNULL(Y.Count_StockOut,0)) Balance FROM ( SELECT M.product_ID,M.Product_Name, SUM(Product_Count) AS Count_StockIN from (SELECT A.*,B.Product_Name FROM `stock_in` A, `products` B where A.Product_ID=B.Product_ID )M group by Product_ID,Product_Name ) X LEFT JOIN ( SELECT product_ID, SUM(Product_Count) AS Count_StockOUT from `stock_Out`group by Product_ID ) Y On X.Product_ID=Y.Product_ID

The Output is:

How to write this query in laravel 4.2 . Please help me.

So... Are you using eloquent or query builder? Have you tried anything? — Jerodev
– Jerodev, Commented Dec 18, 2015 at 9:51
you can user query builder laravel.com/docs/4.2/queries#raw-expressions ,with you can use count like count(1) to get group_by records count — Pedram marandi
– Pedram marandi, Commented Dec 18, 2015 at 9:54
try laravel.com/docs/4.2/queries#joins or laravel.com/docs/4.2/database#running-queries — Soorajlal K G
– Soorajlal K G, Commented Dec 18, 2015 at 10:08
Thank you, everybody, for your potential efforts. The problem is solved. — Bappa sarkar
– Bappa sarkar, Commented Dec 18, 2015 at 12:03

Ashok Adhikari · Accepted Answer · 2015-12-18 10:29:27Z

4

You can use migration to create table in laravel. And you can use MODEL to query into the corresponding table. Create a new migration to create table using following command on terminal inside your project root folder:

php artisan make:migration create_product_table --create="product"

You will get a new migration file in database/migration folder. And you will see a class with two methods: up and down. Up method is to create/modify table and down method is to rollback that table or action that you wrote in up method. You can use following codes in up method:

public function up()
    {
        Schema::create('product', function (Blueprint $table) {
            $table->increments('product_id');
            $table->string('product_name',100);
            $tale->longText('product_description',300);
            $table->integer('category_id');
            $table->integer('brand_id');
            $table->timestamps();
        });
    }

Similarly, you can create other table and you can modify existing table also. Then you should run the migrations using following command:

php artisan migrate

Please refer official documentation of laravel for more detail.

answered Dec 18, 2015 at 10:29

Ashok Adhikari

2031 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anush Shrestha Over a year ago

well explained answer!

Bappa sarkar Over a year ago

Thank you, everybody, for your potential efforts. The problem is solved.

Collectives™ on Stack Overflow

How to write this SQL query in Laravel?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related