CODEWITHSUNDEEP
phpmysqllaravel-4
Bappa sarkar
Bappa sarkar

Reputation: 326

How to write this SQL query in Laravel?

I want to write this query in laravel,

My table structures are

  1. 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

  1. 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:

enter image description here

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

Upvotes: 2

Views: 125

Answers (1)

Ashok Adhikari
Ashok Adhikari

Reputation: 203

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.

Upvotes: 4

Related Questions