JSON Data:
{"source_request_id":"FD6108C1-04AE-A0DF-8817-4C0BF0CE37E1","appl_no":"1579141","emp_code":"2452353","response_code":422,"response_desc":"INVAILD EMP CODE 2452353"}
Query:
Using JSON_EXTRACT method we can achive:
SELECT response_code AS sync_response_code,JSON_EXTRACT(emlap_response, '$.response_desc') As sync_response_msg FROM table_name WHERE lead_id = 1579141;
Expected Output: "INVAILD EMP CODE 2452353"
Laravel Sub Query:
$data = DB::table("products")
->select("products.*",
DB::raw("(SELECT SUM(products_stock.stock) FROM products_stock
WHERE products_stock.product_id = products.id
GROUP BY products_stock.product_id) as product_stock"),
DB::raw("(SELECT SUM(products_sell.sell) FROM products_sell
WHERE products_sell.product_id = products.id
GROUP BY products_sell.product_id) as product_sell"))
->get();
MySQL Sub Query:
SELECT
products.*,
(SELECT SUM(products_stock.stock) FROM products_stock
WHERE products_stock.product_id = products.id
GROUP BY products_stock.product_id) as product_stock,
(SELECT SUM(products_sell.sell) FROM products_sell
WHERE products_sell.product_id = products.id
GROUP BY products_sell.product_id) as product_sell
FROM `products`
*******************************************************
Another Way of getting stock counts:
$selfStocks=DB::table('stocks')
->select(DB::raw("COUNT(id) AS self_stock_count"))
->where('stock_cre_user_id','=',$user_id)
->get()->toArray();
******************************************************
How to print last query in laravel?
Before executing the below function please verify and give the permission 777 for these 2 folders "storage"(Mandatory) and "bootstrap"(Non Mandatory)
public function printQuery(){
$query = DB::getQueryLog();
$lastQuery = end($query);
print_r($lastQuery);
}
echo "<pre>"; print_r($this->printQuery());die;
****************************************************
