Sunday, June 25, 2017

Laravel 5: Use of Laravel Middleware WEB | API | THROTTLE

You have to create a middleware first. Create a php file named "ApiFilter.php" in "app/Http/Middleware" folder with following contents:


<?php
namespace App\Http\Middleware;

use Closure;
use Illuminate\Contracts\Auth\Guard;
use Illuminate\Http\Request as HttpRequest;

class ApiFilter
{
    protected $auth;

    public function __construct(Guard $auth)
    {
        $this->auth = $auth;
    }

    public function handle(HttpRequest $request, Closure $closure)
    {
        if ($this->auth->guest()) {
            if ($request->ajax()) {
                return response('Unauthorized.', 401);
            }
            else {
                return redirect()->guest('login');
            }
        }
        return $closure($request);
    }
}


Now you have to register this middleware to your project. Need to open "App\Http\Kernel.php" file. If you want to register this middleware for global scope then you have to add the below line as follows:


protected $middleware = [
    \App\Http\Middleware\ApiFilter::class
];


So when you browse any url in your project scope, this middleware can handle them.

But if you want to the middleware for specific area you have to add the middleware to "$middlewareGroups" as follows:


protected $middlewareGroups = [
    'web' => [
        .....
    ],

    'api' => [
        'throttle:3,1',
        \App\Http\Middleware\ApiFilter::class
    ],
];


Now add the following lines to "app\Http\routes.php":


Route::group(["middleware" => "api", "prefix" => "api"], function() {
    Route::get('action_name', 'ApiController@action_name');
});


If you browse "www.domain.com/api/action_name" it will pass through ApiFilter middleware bacause of prefix "api" used. But if you only browse "www.domain.com/action_name" it will pass through another middleware.



These middleware may be assigned to groups or used individually


protected $routeMiddleware = [
    'my_middleware' => \App\Http\Middleware\Authenticate::class
];

And can be used as below:

Route::group(['middleware' => 'my_middleware'], function() {
    Route::get('landing-page', 'MyController@landingPage');
});


We can use "throttle" which will restrict request overloading. Here we used "throttle:3,1" means per minute 3 request can be granted, if more request send request rejected as below image:

We need to add below line to "routeMiddleware" for "throttle" action:


protected $routeMiddleware = [
    .........,
    'throttle' => \Illuminate\Routing\Middleware\ThrottleRequests::class,
];




Laravel 5: How to Select from Sub-query using Laravel Query Builder

Laravel 5: How to Select from Sub-query using Laravel Query Builder. It's very easy to use Sub-Query as Select query in Laravel. Below is a sample code snippet:



$query = DB::table("employee")->whereIn("id", array(1, 2, 3, 4));
$result = DB::table(DB::raw("(" . $query->toSql() . ") as res"))
    ->mergeBindings($query)
    ->whereIn("res.id", array(2, 3))->get();

Which will generate below SQL:

select * from (select * from `employee` where `id` in (1, 2, 3, 4)) as res 
where `res`.`id` in (2, 3))

Friday, June 23, 2017

Laravel 5.X: How Do I Get Raw SQL Query As String With Binders From Query Builder | Query Builder To Raw SQL

Laravel 5.X: How Do I Get Raw SQL Query As String With Binders From Query Builder. Its very easy and now few steps from here. It's interesting.


use Illuminate\Support\Facades\DB;

$model = DB::table('employee')
    ->where("employee.id", "<>", 0)
    ->whereIn("employee.id", array(1, 2))
    ->where("employee.type", "PERMANENT")
    ->where(function ($q) {
        $q->where("employee.id", 1)->orWhere("employee.id", 2);
    })
    ->limit(10)->offset(0)
    ->groupBy("employee.id")
    ->select("employee.id");

$replace = function ($sql, $bindings) {
    $pos = 0;
    $needle = '?';
    foreach ($bindings as $replace) {
        if (is_string($replace)) {
            $replace = DB::connection()->getPdo()->quote($replace);
        }
        $pos = strpos($sql, $needle, $pos);
        if ($pos !== false) {
            $sql = substr_replace($sql, $replace, $pos, strlen($needle));
            $pos = $pos + strlen($replace);
        }
    }
    return $sql;
};

$sql = $replace($model->toSql(), $model->getBindings());
echo "SQL=" . $sql;

And output would be like below:

SELECT `employee`.`id`
FROM `employee`
WHERE `employee`.`id` <> 0 AND `employee`.`id` IN (1, 2) AND
      `employee`.`type` = 'PERMANENT' AND (`employee`.`id` = 1 OR `employee`.`id` = 2)
GROUP BY `employee`.`id`
LIMIT 10 OFFSET 0

Laravel 5.X: Use of SubQuery | Sub Query in Where Condition

Laravel 5.X: Use of Sub-query | Sub Query in Where Condition. It's very important. Below is an sample example how we can use sub-query in our application.



$test_data = Employee::from((new Employee())->getTable() . " AS entity")

OR

$test_data = DB::table((new Employee())->getTable() . " AS entity")
    ->whereIn("entity.id", function($q) {
        $q->select("entity.id");
        $q->from((new Employee())->getTable() . " AS entity");
        $q->join((new Supervisor())->getTable() . " AS user", "user.id", "=", "entity.sup_id");
        $q->whereNotNull("entity.sup_id");
        $q->where("user.first_name", "like", "%E%");
    })
    ->select("entity.id", "entity.type")
    ->limit(5)
    ->get();


And will generate below SQL:


SELECT
  `entity`.`id`,
  `entity`.`type`
FROM `employee` AS `entity`
WHERE `entity`.`id` IN (
  SELECT `entity`.`id`
  FROM `employee` AS `entity` INNER JOIN `users` AS `user` ON `user`.`id` = `entity`.`sup_id`
  WHERE `entity`.`sup_id` IS NOT NULL AND `user`.`first_name` LIKE '%E%'
)
LIMIT 5

Responsive CSS: Responsive Table Data Representation

We can easily display our tables in table view for normal browser and list view for mobile device easily. Below is a code snippet:



<meta name="viewport" content="width=device-width, initial-scale=1"/>
<style type="text/css">
    .responsive {
        width: 100%;
        font-size: 18px;
    }
    .responsive thead td {
        padding: 7px;
        background-color: #8b91a2;
    }
    .responsive tbody td {
        padding: 7px;
    }
    .responsive .visible-xs {
        display: none;
    }
    @media screen and (min-width: 0px) and (max-width: 768px) {
        .responsive td {
            font-size: 18px;
        }
        .responsive .hidden-xs {
            display: none !important;
        }
        .responsive .visible-xs {
            display: table-row !important;
        }
        .responsive tbody td {
            display: block;
            width: 100%;
            border: 0 !important;
            box-sizing: border-box !important;
            padding: 0 10px !important;
            font-size: 24px;
        }
        .responsive .separator {
            display: inline-block !important;
            background-color: moccasin;
            width: 100%;
            height: 7px
        }
    }
</style>
<table class="responsive">
    <thead>
    <tr class="hidden-xs">
        <td>NAME</td>
        <td>ROLL</td>
        <td>STATUS</td>
        <td>GPA</td>
    </tr>
    </thead>
    <tbody>
    <tr><td class="visible-xs separator" colspan="4"><div>&nbsp;</div></td></tr>
    <tr>
        <td><span class="visible-xs">NAME:</span>Pritom</td>
        <td><span class="visible-xs">ROLL:</span>00303</td>
        <td><span class="visible-xs">STATUS:</span>PASSED</td>
        <td><span class="visible-xs">GPA:</span>4.50</td>
    </tr>
    <tr><td class="visible-xs separator" colspan="4"><div>&nbsp;</div></td></tr>
    <tr>
        <td><span class="visible-xs">NAME:</span>Pritom</td>
        <td><span class="visible-xs">ROLL:</span>00304</td>
        <td><span class="visible-xs">STATUS:</span>PASSED</td>
        <td><span class="visible-xs">GPA:</span>4.51</td>
    </tr>
    <tr><td class="visible-xs separator" colspan="4"><div>&nbsp;</div></td></tr>
    <tr>
        <td><span class="visible-xs">NAME:</span>Pritom</td>
        <td><span class="visible-xs">ROLL:</span>00305</td>
        <td><span class="visible-xs">STATUS:</span>PASSED</td>
        <td><span class="visible-xs">GPA:</span>4.52</td>
    </tr>
    <tr><td class="visible-xs separator" colspan="4"><div>&nbsp;</div></td></tr>
    <tr>
        <td><span class="visible-xs">NAME:</span>Pritom</td>
        <td><span class="visible-xs">ROLL:</span>00306</td>
        <td><span class="visible-xs">STATUS:</span>PASSED</td>
        <td><span class="visible-xs">GPA:</span>4.53</td>
    </tr>
    <tr><td class="visible-xs separator" colspan="4"><div>&nbsp;</div></td></tr>
    <tr>
        <td><span class="visible-xs">NAME:</span>Pritom</td>
        <td><span class="visible-xs">ROLL:</span>00307</td>
        <td><span class="visible-xs">STATUS:</span>PASSED</td>
        <td><span class="visible-xs">GPA:</span>4.54</td>
    </tr>
    <tr><td class="visible-xs separator" colspan="4"><div>&nbsp;</div></td></tr>
    <tr>
        <td><span class="visible-xs">NAME:</span>Pritom</td>
        <td><span class="visible-xs">ROLL:</span>00308</td>
        <td><span class="visible-xs">STATUS:</span>PASSED</td>
        <td><span class="visible-xs">GPA:</span>4.55</td>
    </tr>
    <tr><td class="visible-xs separator" colspan="4"><div>&nbsp;</div></td></tr>
    </tbody>
</table>




Thursday, June 22, 2017

Laravel 5:Redirect back to the same page where the request comes from with input and messages

In your controller:

use Illuminate\Support\Facades\Redirect;

$error[] = "SOME ERRORS";
return Redirect::back()
    ->withErrors(compact('error'))
    ->with("reason1", "SOME REASON 1")
    ->with("reason2", "SOME REASON 2")
    ->withInput();


And in your view file:

<div>Reason1: {{ session('reason1') }}</div>

<div>Reason1: {{ session('reason2') }}</div>

Option "withInput" will fill back up input fields with previous data:

{!! Form::label('name', 'Name:') !!}

Wednesday, June 21, 2017

MySQL Update Table Using Join Of Other Tables | MySQL Join Table On Update

UPDATE table1 AS t1 LEFT JOIN table2 AS t2 ON (t1.t2_id = t2.id)
SET t1.some_field = t1.some_field + 1 
WHERE t1.id in (1,2,3) AND t2.some_field = "SOME MATCH VALUE"