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))

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>

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:') !!}

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"

JavaScript | jQuery: Manual Ajax Form Submit Using XMLHttpRequest | XMLHttpRequest to Post HTML Form | Upload File Ajax | Submit FormData

It's easy to submit an form using JavaScript ajax request. We also can upload files using ajax form submit. Below is a simple code snippet:

<form name="content_info">
    <input type="file" name="file"/>
    <button type="button" onclick="doPost()">Ajax Submit</button>
</form>

<script type="text/javascript">
    function doPost() {
        var form = document.forms.namedItem("content_info");
        var formData = new FormData(form);
        //or formData = new FormData();
        formData.append("custom_param", "custom_param_value");

        var request = new XMLHttpRequest();
        request.open("POST", "submit.php", true);
        request.responseType = "json";
        request.onload = function(oEvent) {
            console.log(request.status);
            console.log(request);
            console.log(request.response);
            alert(request.status);
        };
        request.onerror = function (error) {
            console.log(error);
        };
        request.setRequestHeader("Custom-Code", "EKKODJIU");
        request.send(formData);
    }
</script>