Using Query Builder and Relationships in Laravel with Sample Project

Prerequisites:

this article assuming you have read and practice my other article :

Mastering Laravel’s Eloquent ORM: A Comprehensive Guide with Sample Code

Introduction to Laravel Query Builder and Relationships

Laravel’s Query Builder and Eloquent ORM simplify database interactions and relationship management, enhancing the efficiency and flexibility of application development. This article explores practical examples using Query Builder and relationships within a sample Laravel project.

Setting Up the Sample Project

Assume we have a Laravel project set up with a documents table in PostgreSQL, as defined previously. Ensure your environment variables and database configuration (config/database.php) are correctly set up.

Using Query Builder in Laravel

Laravel’s Query Builder provides a fluent interface for constructing SQL queries, which is useful for dynamic and complex queries.

Example: Querying Documents with Query Builder (DocumentController.php)

// File: app/Http/Controllers/DocumentController.php
use Illuminate\Support\Facades\DB;
public function index()
{
    // Retrieve all documents with their authors
    $documents = DB::table('documents')
                    ->leftJoin('users', 'documents.author_id', '=', 'users.id')
                    ->select('documents.*', 'users.name as author_name')
                    ->get();
    return view('index', compact('documents'));
}

Managing Relationships with Eloquent ORM

Eloquent ORM simplifies the management of database relationships with expressive syntax.

Example: Defining and Querying Relationships (Document.php and User.php)

Document.php

// File: app/Models/Document.php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Document extends Model
{
    // Define inverse one-to-many relationship with user
    public function author()
    {
        return $this->belongsTo(User::class, 'author_id');
    }
}

User.php

// File: app/Models/User.php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class User extends Model
{
    // Define one-to-many relationship with documents
    public function documents()
    {
        return $this->hasMany(Document::class, 'author_id');
    }
}

Example: Querying Documents with Authors (DocumentController.php)

// File: app/Http/Controllers/DocumentController.php
use App\Models\Document;
public function index()
{
    // Retrieve all documents with their authors using Eloquent
    $documents = Document::with('author')->get();
    return view('documents.index', compact('documents'));
}

Testing in Browser

To test the added code in your Laravel application:

1. Route Definition: Ensure routes are defined in routes/web.php:

Route::get('/documents', [DocumentController::class, 'index']);

2. View Creation: Create a view to display documents (resources/views/documents/index.blade.php):

<!-- File: resources/views/documents/index.blade.php -->

<h1>Documents</h1>
<ul>
    @foreach ($documents as $document)
        <li>
            <strong>Title:</strong> {{ $document->title }} <br>
            <strong>Description:</strong> {{ $document->description }} <br>
            <strong>Author:</strong> {{ $document->author->name }} <br>
            <strong>Price:</strong> ${{ $document->price }} <br>
            <strong>File Path:</strong> {{ $document->file_path }} <br>
            <strong>Created At:</strong> {{ $document->created_at }}
        </li>
    @endforeach
</ul>

3. Accessing in Browser: Navigate to http://localhost:8000/documents (adjust port as per your setup) to see the list of documents with their authors displayed.

Conclusion

Laravel’s Query Builder and Eloquent ORM provide powerful tools for managing database queries and relationships. Integrating these features into your Laravel projects enhances productivity and ensures efficient data management. By following the examples provided, developers can leverage Laravel’s capabilities to build robust and scalable applications.

Summary

This refined article and sample code demonstrate practical uses of Laravel’s Query Builder and relationships within a sample project, specifically focusing on querying documents and managing relationships with authors. By integrating these examples into your Laravel application, you can streamline database interactions and enhance the functionality of your projects.