Laravel Query Builder Where Exists Example (ok)

https://www.itsolutionstuff.com/post/laravel-5-query-builder-where-exists-exampleexample.html

Laravel Query Builder Where Exists Example

By Hardik Savani March 7, 2016 Category : PHP Laravel MySqlPauseUnmuteLoaded: 2.17%Fullscreen

you use sql where exists clause in laravel. whereExists through you can use sql where exists clause in your laravel project. It is very easy to use and you can easily undestand. You can give SELECT statment in where condition. you can see bellow example and you can learn how to use whereExists in your app.

SQL Query

SELECT *FROM `items`WHERE EXISTS    (SELECT `items_city`.`id`     FROM `items_city`     WHERE items_city.item_id = items.id)

Using Laravel Query Builder

DB::table('items')    ->whereExists(function ($query) {        $query->select("items_city.id")              ->from('items_city')              ->whereRaw('items_city.item_id = items.id');    })    ->get();

Create Migrate

C:\xampp\htdocs\wpclidemo\routes\web.php

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\HomeController;
/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/

Route::get('/', function () {
    return view('welcome');
});

Auth::routes();

Route::get('/home', [HomeController::class, 'index'])->name('home');
Route::get('/test', [HomeController::class, 'test'])->name('test');

C:\xampp\htdocs\wpclidemo\app\Http\Controllers\HomeController.php

<?php
namespace App\Http\Controllers;
use App\Models\User;
Use DB;
class HomeController extends Controller {
  /**
   * Create a new controller instance.
   *
   * @return void
   */
  public function __construct() {
    // $this->middleware('auth');
  }
  /**
   * Show the application dashboard.
   *
   * @return \Illuminate\Contracts\Support\Renderable
   */
  public function index() {
    return view('home');
  }
  public function test() {
    $search = 'a';
    // $tests   = User::where('name', 'LIKE', "%{$search}%")->get();
    // $tests = $users = DB::table('users')->where('name', 'LIKE', "%{$search}%")->get();
    // return view('tests')->with(compact('tests'));
    $tests = DB::table('users')->whereExists(function ($query) {
        $query->select("users_city.id")
        ->from('users_city')
        ->whereRaw('users_city.user_id = users.id');
    })
    ->get();
    return view('tests')->with(compact('tests'));
  }
}

Hoặc

<?php
namespace App\Http\Controllers;
use App\Models\User;
Use DB;
class HomeController extends Controller {
  /**
   * Create a new controller instance.
   *
   * @return void
   */
  public function __construct() {
    // $this->middleware('auth');
  }
  /**
   * Show the application dashboard.
   *
   * @return \Illuminate\Contracts\Support\Renderable
   */
  public function index() {
    return view('home');
  }
  public function test() {
    $search = 'a';
    // $tests   = User::where('name', 'LIKE', "%{$search}%")->get();
    // $tests = $users = DB::table('users')->where('name', 'LIKE', "%{$search}%")->get();
    // return view('tests')->with(compact('tests'));
    $tests = DB::table('users')->whereExists(function ($query) {
        $query
        ->from('users_city')
        ->whereRaw('users_city.user_id = users.id');
    })
    ->get();
    return view('tests')->with(compact('tests'));
  }
}

C:\xampp\htdocs\wpclidemo\database\migrations\2022_08_10_085942_create_users_city_table.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateUsersCityTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users_city', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('user_id');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users_city');
    }
}

C:\xampp\htdocs\wpclidemo\database\factories\UsercityFactory.php

<?php

namespace Database\Factories;
use App\Models\Usercity;
use Illuminate\Database\Eloquent\Factories\Factory;

class UsercityFactory extends Factory
{
    protected $model = Usercity::class;
    /**
     * Define the model's default state.
     *
     * @return array
     */
    public function definition()
    {
         return [
          'name'            => $this->faker->text,
          'user_id' => $this->faker->numberBetween(1, 20),
        ];
    }
}

C:\xampp\htdocs\wpclidemo\app\Models\Usercity.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Usercity extends Model
{
    use HasFactory;
    protected $table = 'users_city';
}

C:\xampp\htdocs\wpclidemo\resources\views\tests.blade.php

@extends('layouts.app')
@section('content')
<div class="container">
  <div class="row justify-content-center">
    <div class="col-md-8">
      <div class="card">
        <div class="card-header">Dashboard</div>
        <div class="card-body">
          @foreach ($tests as $i => $test)
            <p><span>{{ $i + 1 }}</span> {{ $test->name }}</p>
          @endforeach
        </div>
      </div>
    </div>
  </div>
</div>
@endsection