Laravel University CRUD System Documentation

1. SQL Joins Fundamentals

What are SQL Joins?

SQL Joins combine rows from two or more tables based on a related column between them. Essential for relational databases.

Sample Tables Structure

universities Table

+----+------------------+-------------+
| id | name             | location    |
+----+------------------+-------------+
| 1  | ABC University   | New York    |
| 2  | XYZ University   | California  |
| 3  | PQR University   | Texas       |
+----+------------------+-------------+

colleges Table

+----+------------------+---------------+
| id | name             | university_id |
+----+------------------+---------------+
| 1  | Engineering      | 1             |
| 2  | Business         | 1             |
| 3  | Medical          | 2             |
| 4  | Arts             | NULL          |
+----+------------------+---------------+

Types of SQL Joins

Join Type	Description	SQL Syntax	Result Data
INNER JOIN	Returns only matching rows from both tables	`SELECT * FROM universities INNER JOIN colleges ON universities.id = colleges.university_id`	Universities that have colleges + their colleges
LEFT JOIN	Returns all rows from left table + matching rows from right table	`SELECT * FROM universities LEFT JOIN colleges ON universities.id = colleges.university_id`	All universities + their colleges (NULL if no college)
RIGHT JOIN	Returns all rows from right table + matching rows from left table	`SELECT * FROM universities RIGHT JOIN colleges ON universities.id = colleges.university_id`	All colleges + their universities (NULL if no university)
FULL OUTER JOIN	Returns all rows when there's a match in either table	`SELECT * FROM universities FULL OUTER JOIN colleges ON universities.id = colleges.university_id`	All universities and all colleges
CROSS JOIN	Returns Cartesian product of both tables	`SELECT * FROM universities CROSS JOIN colleges`	Every university combined with every college

SQL Join Examples with Results

INNER JOIN Result:

+------------------+-------------+------------------+
| university_name  | location    | college_name     |
+------------------+-------------+------------------+
| ABC University   | New York    | Engineering      |
| ABC University   | New York    | Business         |
| XYZ University   | California  | Medical          |
+------------------+-------------+------------------+

LEFT JOIN Result:

+------------------+-------------+------------------+
| university_name  | location    | college_name     |
+------------------+-------------+------------------+
| ABC University   | New York    | Engineering      |
| ABC University   | New York    | Business         |
| XYZ University   | California  | Medical          |
| PQR University   | Texas       | NULL             |
+------------------+-------------+------------------+

RIGHT JOIN Result:

+------------------+-------------+------------------+
| university_name  | location    | college_name     |
+------------------+-------------+------------------+
| ABC University   | New York    | Engineering      |
| ABC University   | New York    | Business         |
| XYZ University   | California  | Medical          |
| NULL             | NULL        | Arts             |
+------------------+-------------+------------------+

2. Core PHP + MySQL Implementation

Understanding the Foundation

Before Laravel, understand how PHP directly interacts with MySQL using mysqli or PDO. Laravel's Eloquent and Query Builder abstract these operations.

Core PHP CRUD Operations

Database Connection (config.php)

<?php
// Core PHP MySQL Connection
$host = 'localhost';
$user = 'root';
$password = '';
$database = 'university_db';

$connection = mysqli_connect($host, $user, $password, $database);

if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}
?>

CREATE - Insert University (Core PHP)

<?php
// Core PHP - Create University
include 'config.php';

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $name = mysqli_real_escape_string($connection, $_POST['name']);
    $location = mysqli_real_escape_string($connection, $_POST['location']);
    
    $sql = "INSERT INTO universities (name, location) VALUES ('$name', '$location')";
    
    if (mysqli_query($connection, $sql)) {
        echo "University created successfully!";
    } else {
        echo "Error: " . mysqli_error($connection);
    }
}
?>

<!-- HTML Form -->
<form method="POST">
    <input type="text" name="name" placeholder="University Name" required>
    <input type="text" name="location" placeholder="Location" required>
    <button type="submit">Create University</button>
</form>

READ - Select with JOIN (Core PHP)

<?php
// Core PHP - Read Universities with Colleges (INNER JOIN)
include 'config.php';

$sql = "SELECT 
            u.name as university_name, 
            u.location, 
            c.name as college_name 
        FROM universities u 
        INNER JOIN colleges c ON u.id = c.university_id 
        ORDER BY u.name";

$result = mysqli_query($connection, $sql);

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "University: " . $row['university_name'] . "<br>";
        echo "Location: " . $row['location'] . "<br>";
        echo "College: " . $row['college_name'] . "<br><hr>";
    }
} else {
    echo "No records found";
}
?>

UPDATE - Modify University (Core PHP)

<?php
// Core PHP - Update University
include 'config.php';

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $id = $_POST['id'];
    $name = mysqli_real_escape_string($connection, $_POST['name']);
    $location = mysqli_real_escape_string($connection, $_POST['location']);
    
    $sql = "UPDATE universities SET name='$name', location='$location' WHERE id=$id";
    
    if (mysqli_query($connection, $sql)) {
        echo "University updated successfully!";
    } else {
        echo "Error: " . mysqli_error($connection);
    }
}
?>

DELETE - Remove University (Core PHP)

<?php
// Core PHP - Delete University
include 'config.php';

if (isset($_GET['delete_id'])) {
    $id = $_GET['delete_id'];
    
    $sql = "DELETE FROM universities WHERE id=$id";
    
    if (mysqli_query($connection, $sql)) {
        echo "University deleted successfully!";
    } else {
        echo "Error: " . mysqli_error($connection);
    }
}
?>

Core PHP Limitations

Manual SQL injection protection needed (mysqli_real_escape_string)
No built-in ORM or relationships
Manual connection management
No query builder for complex queries
No automatic timestamps or soft deletes

3. Laravel Models & Relationships

What are Laravel Models?

Models in Laravel represent database tables and use Eloquent ORM for object-relational mapping. They provide an ActiveRecord implementation.

University Model

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;

class University extends Model
{
    protected $fillable = ['name', 'location'];
    
    // One-to-Many relationship: University has many Colleges
    public function colleges() { 
        return $this->hasMany(College::class); 
    }
    
    // Accessor for formatted name
    public function getNameAttribute($value) {
        return ucwords($value);
    }
    
    // Query Scope for active universities
    public function scopeActive($query) {
        return $query->where('is_active', true);
    }
}

College Model

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;

class College extends Model
{
    protected $fillable = ['name', 'university_id'];
    
    // Many-to-One relationship: College belongs to University
    public function university() { 
        return $this->belongsTo(University::class); 
    }
    
    // One-to-Many relationship: College has many Departments
    public function departments() { 
        return $this->hasMany(Department::class); 
    }
}

Department Model

<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;

class Department extends Model
{
    protected $fillable = ['name', 'college_id'];
    
    // Many-to-One relationship: Department belongs to College
    public function college() { 
        return $this->belongsTo(College::class); 
    }
}

Lazy Loading vs Eager Loading

Lazy Loading (N+1 Problem):

// This creates N+1 queries (inefficient)
$universities = University::all();
foreach($universities as $university) {
    echo $university->colleges->count(); // Separate query for each university
}

Eager Loading (Optimized):

// This creates only 2 queries (efficient)
$universities = University::with('colleges')->get();
foreach($universities as $university) {
    echo $university->colleges->count(); // No additional queries
}

4. Eloquent ORM CRUD Operations

Eloquent ORM Advantages

Automatic SQL injection protection
Built-in relationships
Automatic timestamps
Query scopes and accessors
Clean, object-oriented syntax

UniversityEloquentController

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\University;
use Illuminate\Support\Facades\Validator;

class UniversityEloquentController extends Controller
{
    // CREATE - Show form
    public function create() { 
        return view('universities.create'); 
    }

    // CREATE - Store data
    public function store(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'name' => 'required|string|max:255',
            'location' => 'required|string|max:255'
        ]);
        
        if($validator->fails()) {
            return redirect()->back()->withErrors($validator)->withInput();
        }
        
        University::create([
            'name' => $request->name,
            'location' => $request->location
        ]);
        
        return redirect()->route('universities.view')->with('success', 'University added successfully.');
    }

    // READ - View all universities with relationships
    public function view() { 
        return view('universities.view', [
            'universities' => University::with('colleges.departments')->orderBy('id','desc')->get()
        ]); 
    }

    // UPDATE - Show edit form
    public function edit($id) { 
        return view('universities.edit', [
            'university' => University::findOrFail($id)
        ]); 
    }

    // UPDATE - Process update
    public function update(Request $request, $id)
    {
        $validator = Validator::make($request->all(), [
            'name' => 'required|string|max:255',
            'location' => 'required|string|max:255'
        ]);
        
        if($validator->fails()) {
            return redirect()->back()->withErrors($validator)->withInput();
        }
        
        University::findOrFail($id)->update([
            'name' => $request->name,
            'location' => $request->location
        ]);
        
        return redirect()->route('universities.view')->with('success', 'University updated successfully.');
    }

    // DELETE - Remove university
    public function destroy($id)
    {
        University::findOrFail($id)->delete();
        return redirect()->back()->with('success', 'University deleted successfully.');
    }

    // JOIN Example with Eloquent
    public function joinUniversitiesColleges()
    {
        return University::join('colleges', 'universities.id', '=', 'colleges.university_id')
                         ->select('universities.name as university_name', 'colleges.name as college_name')
                         ->get();
    }
}

Eloquent Relationship Usage Examples

// Create university with college
$university = University::create(['name' => 'Tech University', 'location' => 'Boston']);
$college = $university->colleges()->create(['name' => 'Engineering']);

// Eager loading with nested relationships
$universities = University::with(['colleges' => function($query) {
    $query->where('name', 'like', '%Engineering%');
}])->get();

// Accessing relationships
foreach($universities as $university) {
    echo $university->name;
    foreach($university->colleges as $college) {
        echo $college->name;
        foreach($college->departments as $department) {
            echo $department->name;
        }
    }
}

// WhereHas for filtering by relationship
$engineeringUniversities = University::whereHas('colleges', function($query) {
    $query->where('name', 'like', '%Engineering%');
})->get();

5. Query Builder & Advanced Joins

When to Use Query Builder

Complex joins that Eloquent can't handle easily
Aggregate functions and complex calculations
Raw expressions and database-specific features
Performance-critical operations
Dynamic query building

UniversityQueryBuilderController

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;

class UniversityQueryBuilderController extends Controller
{
    // CREATE
    public function store(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'name' => 'required|string|max:255',
            'location' => 'required|string|max:255'
        ]);
        
        if($validator->fails()) {
            return redirect()->back()->withErrors($validator)->withInput();
        }
        
        DB::table('universities')->insert([
            'name' => $request->name,
            'location' => $request->location,
            'created_at' => now(),
            'updated_at' => now()
        ]);
        
        return redirect()->route('qb.universities.view')->with('success', 'University added successfully.');
    }

    // READ
    public function view() { 
        return view('universities.view', [
            'universities' => DB::table('universities')->orderBy('id','desc')->get()
        ]); 
    }

    // UPDATE
    public function update(Request $request, $id)
    {
        $validator = Validator::make($request->all(), [
            'name' => 'required|string|max:255',
            'location' => 'required|string|max:255'
        ]);
        
        if($validator->fails()) {
            return redirect()->back()->withErrors($validator)->withInput();
        }
        
        DB::table('universities')->where('id', $id)->update([
            'name' => $request->name,
            'location' => $request->location,
            'updated_at' => now()
        ]);
        
        return redirect()->route('qb.universities.view')->with('success', 'University updated successfully.');
    }

    // DELETE
    public function destroy($id) { 
        DB::table('universities')->where('id', $id)->delete(); 
        return redirect()->back()->with('success', 'University deleted successfully.'); 
    }

    // RIGHT JOIN Example (not easily possible with Eloquent)
    public function rightJoinUniversitiesColleges()
    {
        return DB::table('universities')
            ->rightJoin('colleges', 'universities.id', '=', 'colleges.university_id')
            ->select('universities.name as university_name', 'colleges.name as college_name')
            ->get();
    }

    // Aggregation with GROUP BY
    public function universityCollegeCount()
    {
        return DB::table('universities')
            ->join('colleges', 'universities.id', '=', 'colleges.university_id')
            ->select('universities.name', DB::raw('COUNT(colleges.id) as total_colleges'))
            ->groupBy('universities.name')
            ->get();
    }

    // Complex Multiple Joins
    public function universityCollegeDepartmentJoin()
    {
        return DB::table('universities')
            ->join('colleges', 'universities.id', '=', 'colleges.university_id')
            ->leftJoin('departments', 'colleges.id', '=', 'departments.college_id')
            ->select(
                'universities.name as university_name',
                'colleges.name as college_name', 
                'departments.name as department_name'
            )
            ->get();
    }
}

Query Builder Join Examples

INNER JOIN:

// Equivalent to: SELECT * FROM universities INNER JOIN colleges ON universities.id = colleges.university_id
$results = DB::table('universities')
    ->join('colleges', 'universities.id', '=', 'colleges.university_id')
    ->select('universities.name as university_name', 'colleges.name as college_name')
    ->get();

LEFT JOIN:

// Get all universities even if they have no colleges
$results = DB::table('universities')
    ->leftJoin('colleges', 'universities.id', '=', 'colleges.university_id')
    ->select('universities.name as university_name', 'colleges.name as college_name')
    ->get();

Complex Query with Aggregation:

// Count colleges per university with having clause
$results = DB::table('universities')
    ->join('colleges', 'universities.id', '=', 'colleges.university_id')
    ->select('universities.name', DB::raw('COUNT(colleges.id) as college_count'))
    ->groupBy('universities.id', 'universities.name')
    ->having('college_count', '>', 1)
    ->get();

6. Laravel Routes

<?php
// routes/web.php

use App\Http\Controllers\UniversityEloquentController;
use App\Http\Controllers\UniversityQueryBuilderController;

// Eloquent ORM Routes
Route::prefix('eloquent')->group(function() {
    Route::get('universities/create', [UniversityEloquentController::class, 'create'])->name('universities.create');
    Route::post('universities/store', [UniversityEloquentController::class, 'store'])->name('universities.store');
    Route::get('universities', [UniversityEloquentController::class, 'view'])->name('universities.view');
    Route::get('universities/edit/{id}', [UniversityEloquentController::class, 'edit'])->name('universities.edit');
    Route::put('universities/update/{id}', [UniversityEloquentController::class, 'update'])->name('universities.update');
    Route::delete('universities/delete/{id}', [UniversityEloquentController::class, 'destroy'])->name('universities.destroy');
});

// Query Builder Routes
Route::prefix('query')->group(function() {
    Route::get('universities/create', [UniversityQueryBuilderController::class, 'create'])->name('qb.universities.create');
    Route::post('universities/store', [UniversityQueryBuilderController::class, 'store'])->name('qb.universities.store');
    Route::get('universities', [UniversityQueryBuilderController::class, 'view'])->name('qb.universities.view');
    Route::get('universities/edit/{id}', [UniversityQueryBuilderController::class, 'edit'])->name('qb.universities.edit');
    Route::put('universities/update/{id}', [UniversityQueryBuilderController::class, 'update'])->name('qb.universities.update');
    Route::delete('universities/delete/{id}', [UniversityQueryBuilderController::class, 'destroy'])->name('qb.universities.destroy');
});

// API Routes for mobile apps
Route::prefix('api')->group(function() {
    Route::apiResource('universities', UniversityApiController::class);
});

Route Naming Conventions

universities.create - Show create form
universities.store - Store new record (POST)
universities.view - List all records
universities.edit - Show edit form
universities.update - Update record (PUT)
universities.destroy - Delete record (DELETE)

7. Blade Views with Tailwind CSS

Create University Form

<!-- resources/views/universities/create.blade.php -->
@extends('layouts.app')

@section('title', 'Add University')

@section('content')
<div class="container mx-auto px-4 py-8">
    <div class="max-w-2xl mx-auto">
        <h1 class="text-3xl font-bold text-gray-800 mb-6">Add New University</h1>
        
        @if ($errors->any())
            <div class="bg-red-100 border border-red-400 text-red-700 px-4 py-3 rounded mb-4">
                <ul>
                    @foreach ($errors->all() as $error)
                        <li>{{ $error }}</li>
                    @endforeach
                </ul>
            </div>
        @endif

        <form action="{{ route('universities.store') }}" method="POST" class="bg-white shadow-md rounded px-8 pt-6 pb-8 mb-4">
            @csrf
            
            <div class="mb-4">
                <label class="block text-gray-700 text-sm font-bold mb-2" for="name">
                    University Name
                </label>
                <input class="shadow appearance-none border rounded w-full py-2 px-3 text-gray-700 leading-tight focus:outline-none focus:shadow-outline" 
                       id="name" name="name" type="text" placeholder="Enter university name" value="{{ old('name') }}" required>
            </div>

            <div class="mb-6">
                <label class="block text-gray-700 text-sm font-bold mb-2" for="location">
                    Location
                </label>
                <input class="shadow appearance-none border rounded w-full py-2 px-3 text-gray-700 leading-tight focus:outline-none focus:shadow-outline" 
                       id="location" name="location" type="text" placeholder="Enter location" value="{{ old('location') }}" required>
            </div>

            <div class="flex items-center justify-between">
                <button class="bg-blue-500 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded focus:outline-none focus:shadow-outline" type="submit">
                    Create University
                </button>
                <a href="{{ route('universities.view') }}" class="inline-block align-baseline font-bold text-sm text-blue-500 hover:text-blue-800">
                    Back to List
                </a>
            </div>
        </form>
    </div>
</div>
@endsection

Universities List View

<!-- resources/views/universities/view.blade.php -->
@extends('layouts.app')

@section('title', 'Universities List')

@section('content')
<div class="container mx-auto px-4 py-8">
    <div class="flex justify-between items-center mb-6">
        <h1 class="text-3xl font-bold text-gray-800">Universities</h1>
        <a href="{{ route('universities.create') }}" class="bg-green-500 hover:bg-green-700 text-white font-bold py-2 px-4 rounded">
            Add New University
        </a>
    </div>

    @if(session('success'))
        <div class="bg-green-100 border border-green-400 text-green-700 px-4 py-3 rounded mb-4">
            {{ session('success') }}
        </div>
    @endif

    <div class="bg-white shadow-md rounded">
        <table class="min-w-full">
            <thead>
                <tr class="bg-gray-200 text-gray-600 uppercase text-sm leading-normal">
                    <th class="py-3 px-6 text-left">ID</th>
                    <th class="py-3 px-6 text-left">Name</th>
                    <th class="py-3 px-6 text-left">Location</th>
                    <th class="py-3 px-6 text-left">Colleges</th>
                    <th class="py-3 px-6 text-center">Actions</th>
                </tr>
            </thead>
            <tbody class="text-gray-600 text-sm font-light">
                @forelse($universities as $university)
                <tr class="border-b border-gray-200 hover:bg-gray-100">
                    <td class="py-3 px-6 text-left whitespace-nowrap">{{ $university->id }}</td>
                    <td class="py-3 px-6 text-left">{{ $university->name }}</td>
                    <td class="py-3 px-6 text-left">{{ $university->location }}</td>
                    <td class="py-3 px-6 text-left">
                        @foreach($university->colleges as $college)
                            <span class="bg-blue-100 text-blue-800 text-xs font-medium mr-2 px-2.5 py-0.5 rounded">
                                {{ $college->name }}
                            </span>
                        @endforeach
                    </td>
                    <td class="py-3 px-6 text-center">
                        <div class="flex item-center justify-center">
                            <a href="{{ route('universities.edit', $university->id) }}" class="w-4 mr-2 transform hover:text-purple-500 hover:scale-110">
                                <i class="fas fa-edit"></i>
                            </a>
                            <form action="{{ route('universities.destroy', $university->id) }}" method="POST" class="inline">
                                @csrf
                                @method('DELETE')
                                <button type="submit" class="w-4 mr-2 transform hover:text-red-500 hover:scale-110" onclick="return confirm('Are you sure?')">
                                    <i class="fas fa-trash-alt"></i>
                                </button>
                            </form>
                        </div>
                    </td>
                </tr>
                @empty
                <tr>
                    <td colspan="5" class="py-4 px-6 text-center">No universities found.</td>
                </tr>
                @endforelse
            </tbody>
        </table>
    </div>
</div>
@endsection

8. Interview Preparation Notes

Common Interview Questions

What's the difference between Eloquent and Query Builder?
How do you prevent N+1 query problem?
What are Laravel relationships and types?
How do you handle form validation in Laravel?
What's the difference between PUT and POST?
How do you implement soft deletes?
What are accessors and mutators?
How do you use query scopes?

Key Points to Remember

Eloquent: Best for standard CRUD, relationships, clean code
Query Builder: Best for complex queries, aggregations, performance
Eager Loading: Use with() to prevent N+1 queries
Mass Assignment: Use $fillable or $guarded
Validation: Always validate user input
RESTful Routes: Follow naming conventions

SQL Joins in Laravel - Complete Guide

Can we use Joins with Eloquent ORM?

YES! But with limitations:

What works with Eloquent:

Basic INNER JOIN (using join() method)
LEFT JOIN (using leftJoin())
Simple relationship-based queries
Eager loading with constraints

What's difficult with Eloquent:

RIGHT JOIN and FULL OUTER JOIN
Complex multiple table joins
Advanced aggregate functions
Database-specific features

Eloquent vs Query Builder for Joins:

// Eloquent JOIN (limited)
University::join('colleges', 'universities.id', '=', 'colleges.university_id')
    ->select('universities.name', 'colleges.name')
    ->get();

// Query Builder JOIN (full control)
DB::table('universities')
    ->rightJoin('colleges', 'universities.id', '=', 'colleges.university_id')
    ->leftJoin('departments', 'colleges.id', '=', 'departments.college_id')
    ->select('universities.name', DB::raw('COUNT(departments.id) as dept_count'))
    ->groupBy('universities.id')
    ->get();

Performance Tips

Use Eager Loading (with()) to avoid N+1 queries
Use Query Builder for complex reports and analytics
Add indexes on foreign keys and frequently searched columns
Use chunk() method for large datasets
Implement caching for frequently accessed data
Use select() to fetch only required columns

Learning Chronology

Documentation Sections

1. SQL Joins Fundamentals

What are SQL Joins?

Sample Tables Structure

universities Table

colleges Table

Types of SQL Joins

SQL Join Examples with Results

INNER JOIN Result:

LEFT JOIN Result:

RIGHT JOIN Result:

2. Core PHP + MySQL Implementation

Understanding the Foundation

Core PHP CRUD Operations

Database Connection (config.php)

CREATE - Insert University (Core PHP)

READ - Select with JOIN (Core PHP)

UPDATE - Modify University (Core PHP)

DELETE - Remove University (Core PHP)

Core PHP Limitations

3. Laravel Models & Relationships

What are Laravel Models?

University Model

College Model

Department Model

Lazy Loading vs Eager Loading

Lazy Loading (N+1 Problem):

Eager Loading (Optimized):

4. Eloquent ORM CRUD Operations

Eloquent ORM Advantages

UniversityEloquentController

Eloquent Relationship Usage Examples

5. Query Builder & Advanced Joins

When to Use Query Builder

UniversityQueryBuilderController

Query Builder Join Examples

INNER JOIN:

LEFT JOIN:

Complex Query with Aggregation:

6. Laravel Routes

Route Naming Conventions

7. Blade Views with Tailwind CSS

Create University Form

Universities List View

8. Interview Preparation Notes

Common Interview Questions

Key Points to Remember

SQL Joins in Laravel - Complete Guide

Can we use Joins with Eloquent ORM?

What works with Eloquent:

What's difficult with Eloquent:

Eloquent vs Query Builder for Joins:

Performance Tips