Posted By
Bajarangi soft ,
Posted On 23-09-2020
Subquery Where Clauses
Sometimes you may need to construct a where clause that compares the results of a subquery to a given value. You may accomplish this by passing a Closure and a value to the where method. For example, the following query will retrieve all course who have a recent "student" of a given type;
Example(1)
app/Http/Controllers/studentController.php
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;
class studentController extends Controller
{
function index()
{
$student = course::where(function ($query) {
$query->select('Student_name')
->from('student')
->whereColumn('course_id', 'course.course_id')
->orderByDesc('start_date')
->limit(1);
}, 'Pro')->get();
return view('student.index', ['student' => $student]);
}
}
?>
In above Example from course and student table retrieving data. all course who have a recent "student" that data return from controller.
resources/views/student/index.blade.php
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Use Sub Queries Where Clause In Laravel With Example</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<br />
<h3 align="center">How to Use Sub Queries Where Clause In Laravel With Example</h3>
<br />
<div class="table-responsive">
<table class="table table-bordered table-striped">
<thead>
<tr>
<th>name</th>
<th>id</th>
<th>address</th>
<th>gender</th>
<th>age</th>
</tr>
</thead>
<tbody>
@foreach($student as $row)
<tr>
<td>{{ $row->name }}</td>
<td>{{ $row->id }}</td>
<td>{{ $row->address }}</td>
<td>{{ $row->gender }}</td>
<td>{{ $row->age }}</td>
</tr>
@endforeach
</tbody>
</table>
</div>
</div>
</body>
</html>
routes/web.php
Route::get('student', 'studentController@index');