rakesh kumar

Posted on Dec 13, 2024 • Updated on Dec 22, 2024

How to Centralizing Queries Using a Business Logic Layer in Laravel

Advantage of using Bussiness Layer
How to centralize queries in Bussiness Layer
7 way to seprate bussiness layer query from presentation layer

Advantage of using Bussiness Layer

Clean and Maintainable Code
Separation of Concerns: Keeps the database logic separate from the controller, resulting in cleaner and more maintainable code.
Readability: Controllers focus on handling HTTP requests, while the business layer handles the logic, making both easier to understand.
Reusability
Centralized Logic: Common queries and business rules are defined in one place, making them reusable across multiple controllers or services.
Dynamic Flexibility: The business layer can dynamically handle different models and queries based on parameters.
Scalability
Easier to Scale: When your application grows, you can add more functionality to the business layer without impacting the controllers.
Modular Design: Changes to database queries or logic only affect the business layer, leaving the rest of the application unaffected.
Improved Testing
Unit Testing: The business layer can be tested independently, making it easier to validate query logic and permissions.
Mocking: Testing controllers becomes simpler as you can mock the business layer instead of dealing directly with the database.
Enhanced Security
Permission Management: Integrating user permission checks in the business layer ensures that only authorized users can access or modify data.
Consistency: Centralizing permission logic prevents duplication and minimizes the risk of inconsistent security rules across the application.
Better Debugging and Maintenance
Centralized Error Handling: Errors in the query logic can be easily traced back to the business layer.
Reduced Code Duplication: Common query patterns, such as filtering or joins, are defined once and reused, reducing redundancy.
Flexibility for Advanced Features
Dynamic Query Handling: The business layer can dynamically generate queries based on runtime parameters, making it suitable for complex or multi-tenant applications.
Caching and Optimization: Query caching and optimizations can be implemented in one place without scattering logic across the application.
Alignment with Best Practices
Domain-Driven Design (DDD): Encapsulates database interactions in a layer that aligns with your domain logic.
Adherence to SOLID Principles:
Single Responsibility: Controllers and queries have distinct responsibilities.
Open/Closed Principle: The business layer can be extended without modifying existing code.
Easier Collaboration
Role Clarity: Developers working on the API layer and database logic can work independently without stepping into each other's domains.
Standardization: Provides a consistent way to handle queries across the application, making onboarding new developers easier.
Integration of Cross-Cutting Concerns
Auditing and Logging: Add auditing, logging, or monitoring for queries at the business layer without touching controllers.
Validation and Transformation: Pre-query validation or post-query transformation can be centralized.

How to centralize queries in Bussiness Layer

my laravel code

 public function index($id)
    {
        // Get all the PhoneNumber from db
        $PhoneNumbers = PhoneNumber::where('slug_id',$id)->get();   
            $response = [
                'success' => true,
                'data' => PhoneNumberResource::collection($PhoneNumbers),
                'message' => 'PhoneNumber retrieved successfully.',
                'count' => count($PhoneNumbers)];

            return response()->json($response, 200);
    }

After seprating into bussiness layer

Create a Business Layer
The business layer will handle the dynamic logic for querying the database. Use a Service or Repository pattern.

Example Service: DynamicQueryService

namespace App\Services;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\App;

class DynamicQueryService
{
    /**
     * Retrieve data dynamically from a given model.
     *
     * @param string $modelClass
     * @param string $resourceClass
     * @param string $column
     * @param mixed $value
     * @return array
     */
    public function fetchData(string $modelClass, string $resourceClass, string $column, $value): array
    {
        // Ensure the model exists
        if (!class_exists($modelClass)) {
            throw new \Exception("Model class {$modelClass} not found.");
        }

        // Ensure the resource class exists
        if (!class_exists($resourceClass)) {
            throw new \Exception("Resource class {$resourceClass} not found.");
        }

        // Fetch data from the database
        $data = App::make($modelClass)::where($column, $value)->get();

        // Return the response
        return [
            'success' => true,
            'data' => $resourceClass::collection($data),
            'message' => "{$modelClass} retrieved successfully.",
            'count' => count($data),
        ];
    }
}

Inject Service in the Controller
Use Dependency Injection to inject the service into the controller.


namespace App\Http\Controllers;

use App\Services\DynamicQueryService;
use Illuminate\Http\Request;

class PhoneNumberController extends Controller
{
    protected $dynamicQueryService;

    public function __construct(DynamicQueryService $dynamicQueryService)
    {
        $this->dynamicQueryService = $dynamicQueryService;
    }

    public function index($id)
    {
        try {
            // Define the dynamic model, resource, and column
            $modelClass = \App\Models\PhoneNumber::class;
            $resourceClass = \App\Http\Resources\PhoneNumberResource::class;
            $column = 'slug_id';

            // Fetch data using the service
            $response = $this->dynamicQueryService->fetchData($modelClass, $resourceClass, $column, $id);

            return response()->json($response, 200);
        } catch (\Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }
}

Explanation of Key Components
Service (DynamicQueryService):

Encapsulates the logic to query any model dynamically.
Accepts the model, resource, column, and value as parameters to fetch data.
Controller (PhoneNumberController):

Delegates the database fetching logic to the service.
Keeps the controller clean and focused on handling the HTTP layer.
Dynamic Parameters:

$modelClass: Represents the model class, e.g., \App\Models\PhoneNumber.
$resourceClass: Represents the resource class, e.g., \App\Http\Resources\PhoneNumberResource.
$column: The column name used for the where condition, e.g., slug_id.
$value: The dynamic value passed, e.g., $id.

Another Examples

Create a Business Logic Layer
The business logic can be encapsulated in a Service or a Repository pattern. This helps you centralize your queries and related logic.

Create a Service or Repository
Use Laravel's artisan command to create a service:

php artisan make:service QueryService

OR, create a repository (depending on your preference):

php artisan make:repository QueryRepository

Define the logic in the service/repository class. For example:

namespace App\Services;

use Illuminate\Support\Facades\DB;

class QueryService
{
    protected $user;

    public function __construct($user)
    {
        $this->user = $user;
    }

    public function fetchRecords(string $tableName, array $parameters = [])
    {
        // Check user permissions
        if (!$this->userHasPermission($tableName)) {
            throw new \Exception("User does not have permission to access this data.");
        }

        // Build and execute the query
        return DB::table($tableName)
            ->where($parameters)
            ->get();
    }

    private function userHasPermission(string $tableName): bool
    {
        // Implement your logic to check user permissions for the table
        // Example: Check a role or a permission table
        return $this->user->canAccessTable($tableName); // Assuming a `canAccessTable` method
    }
}

Inject the Service Layer
You can inject this service into your controllers or other parts of your application.

Example Controller Usage:

namespace App\Http\Controllers;

use App\Services\QueryService;
use Illuminate\Http\Request;

class QueryController extends Controller
{
    protected $queryService;

    public function __construct(QueryService $queryService)
    {
        $this->queryService = $queryService;
    }

    public function getRecords(Request $request)
    {
        $tableName = $request->input('table_name');
        $parameters = $request->except('table_name');

        try {
            $records = $this->queryService->fetchRecords($tableName, $parameters);
            return response()->json($records);
        } catch (\Exception $e) {
            return response()->json(['error' => $e->getMessage()], 403);
        }
    }
}

Define User Permissions Logic
The userHasPermission method in the service layer can connect to a roles or permissions table to verify if the user has access to a specific table.

Example Model Method for User Permissions:

namespace App\Models;

use Illuminate\Foundation\Auth\User as Authenticatable;

class User extends Authenticatable
{
    public function canAccessTable(string $tableName): bool
    {
        // Example: Fetch user roles/permissions from a pivot table
        return $this->permissions()->where('table_name', $tableName)->exists();
    }

    public function permissions()
    {
        return $this->belongsToMany(Permission::class);
    }
}

Make Queries Table and Parameter Dynamic
Ensure that the query-building process dynamically uses parameters from the request or predefined configurations. This will keep your service layer reusable.

Example of Dynamic Query Building:

public function fetchRecords(string $tableName, array $parameters = [])
{
    $query = DB::table($tableName);

    foreach ($parameters as $key => $value) {
        $query->where($key, $value);
    }

    return $query->get();
}

7 way to seprate bussiness layer query from presentation layer

Data Access Layer (DAL)
csharp
Copy code
// DataAccess.cs
public class DataAccess
{
private string connectionString = "YourConnectionString";

public List<User> GetUsers()
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string query = "SELECT * FROM Users";
        SqlCommand command = new SqlCommand(query, connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        List<User> users = new List<User>();

        while (reader.Read())
        {
            users.Add(new User
            {
                Id = (int)reader["Id"],
                Name = reader["Name"].ToString()
            });
        }

        return users;
    }
}

}

// BusinessLayer.cs
public class BusinessLayer
{
private DataAccess dataAccess = new DataAccess();

public List<User> GetAllUsers()
{
    return dataAccess.GetUsers();
}

}

Repository Pattern csharp Copy code // IUserRepository.cs public interface IUserRepository { User GetUserById(int id); }

// UserRepository.cs
public class UserRepository : IUserRepository
{
public User GetUserById(int id)
{
// SQL query logic here
string query = "SELECT * FROM Users WHERE Id = @Id";
// Execute the query and map the result to a User object
}
}

// BusinessLayer.cs
public class BusinessLayer
{
private IUserRepository userRepository;

public BusinessLayer(IUserRepository repository)
{
    userRepository = repository;
}

public User FetchUser(int id)
{
    return userRepository.GetUserById(id);
}

}

Stored Procedures
sql
Copy code
-- Stored Procedure in SQL Server
CREATE PROCEDURE sp_GetUsers
AS
BEGIN
SELECT * FROM Users
END
csharp
Copy code
// DataAccess.cs
public List GetUsers()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("sp_GetUsers", connection);
command.CommandType = CommandType.StoredProcedure;
```
connection.Open();
SqlDataReader reader = command.ExecuteReader();
List<User> users = new List<User>();

while (reader.Read())
{
    users.Add(new User
    {
        Id = (int)reader["Id"],
        Name = reader["Name"].ToString()
    });
}

return users;
```
}
}
Object-Relational Mapping (ORM)
csharp
Copy code
// Using Entity Framework
public class ApplicationDbContext : DbContext
{
public DbSet Users { get; set; }
}

// BusinessLayer.cs
public class BusinessLayer
{
private ApplicationDbContext dbContext = new ApplicationDbContext();

public List<User> GetAllUsers()
{
    return dbContext.Users.ToList(); // SQL is generated dynamically
}

}

Query Builders javascript Copy code // Using Knex.js for SQL query building (Node.js) const knex = require('knex')({ client: 'mysql', connection: { host: '127.0.0.1', user: 'your_user', password: 'your_password', database: 'your_db' } });

// Query
knex.select('*').from('Users').where('isActive', true).then(users => {
console.log(users);
});

Service-Oriented Architecture (SOA) csharp Copy code // UserService.cs (Microservice) [ApiController] [Route("api/users")] public class UserService : ControllerBase { [HttpGet("{id}")] public User GetUser(int id) { // Fetch user from DB } }

// BusinessLayer.cs
public class BusinessLayer
{
private HttpClient httpClient = new HttpClient();

public async Task<User> FetchUserFromService(int id)
{
    string url = $"http://userservice/api/users/{id}";
    var response = await httpClient.GetAsync(url);
    response.EnsureSuccessStatusCode();
    return JsonConvert.DeserializeObject<User>(await response.Content.ReadAsStringAsync());
}

}

Data Transfer Objects (DTOs) with Query Classes csharp Copy code // DTOs public class UserDTO { public int Id { get; set; } public string Name { get; set; } }

// Query Class
public class GetUsersQuery
{
private string connectionString = "YourConnectionString";

public List<UserDTO> Execute()
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string query = "SELECT Id, Name FROM Users";
        SqlCommand command = new SqlCommand(query, connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        List<UserDTO> users = new List<UserDTO>();

        while (reader.Read())
        {
            users.Add(new UserDTO
            {
                Id = (int)reader["Id"],
                Name = reader["Name"].ToString()
            });
        }

        return users;
    }
}

}

// BusinessLayer.cs
public class BusinessLayer
{
public List GetAllUsers()
{
var query = new GetUsersQuery();
return query.Execute();
}
}
Each of these examples illustrates a way to decouple SQL logic from the business layer, enhancing maintainability, testability, and scalability of your application. Choose the approach that best fits your project's complexity and technology stack.

You said:
in laravel
ChatGPT said:
ChatGPT
Here’s how you can separate SQL queries from the business layer in Laravel with code examples:

Data Access Layer (DAL) Create a service or a separate class for handling database interactions.

// app/Services/UserService.php
namespace App\Services;

use App\Models\User;

class UserService
{
    public function getAllUsers()
    {
        return User::all(); // Using Eloquent to fetch all users
    }

    public function findUserById($id)
    {
        return User::find($id);
    }
}

// In Controller
use App\Services\UserService;

class UserController extends Controller
{
    private $userService;

    public function __construct(UserService $userService)
    {
        $this->userService = $userService;
    }

    public function index()
    {
        $users = $this->userService->getAllUsers();
        return response()->json($users);
    }
}

Repository Pattern

// app/Repositories/UserRepository.php
namespace App\Repositories;

use App\Models\User;

class UserRepository
{
    public function getAllUsers()
    {
        return User::all();
    }

    public function getUserById($id)
    {
        return User::find($id);
    }
}

// In Controller
use App\Repositories\UserRepository;

class UserController extends Controller
{
    protected $userRepository;

    public function __construct(UserRepository $userRepository)
    {
        $this->userRepository = $userRepository;
    }

    public function index()
    {
        $users = $this->userRepository->getAllUsers();
        return response()->json($users);
    }
}

Using Stored Procedures Define the stored procedure in the database:

CREATE PROCEDURE GetUsers()
BEGIN
    SELECT * FROM users;
END

Call the stored procedure in Laravel:

use Illuminate\Support\Facades\DB;

class UserService
{
    public function getAllUsers()
    {
        return DB::select('CALL GetUsers()');
    }
}

// Controller
use App\Services\UserService;

class UserController extends Controller
{
    protected $userService;

    public function __construct(UserService $userService)
    {
        $this->userService = $userService;
    }

    public function index()
    {
        $users = $this->userService->getAllUsers();
        return response()->json($users);
    }
}

Object-Relational Mapping (ORM) Use Laravel's Eloquent ORM to abstract SQL queries.

// In Controller
use App\Models\User;

class UserController extends Controller
{
    public function index()
    {
        $users = User::where('is_active', 1)->get(); // SQL generated dynamically
        return response()->json($users);
    }
}

Query Builders Use Laravel’s query builder for dynamic SQL queries.

use Illuminate\Support\Facades\DB;

class UserService
{
    public function getActiveUsers()
    {
        return DB::table('users')->where('is_active', 1)->get();
    }
}

// Controller
use App\Services\UserService;

class UserController extends Controller
{
    protected $userService;

    public function __construct(UserService $userService)
    {
        $this->userService = $userService;
    }

    public function index()
    {
        $users = $this->userService->getActiveUsers();
        return response()->json($users);
    }
}

Service-Oriented Architecture (SOA) Separate business logic into services that call the repository or database.

// app/Services/UserService.php
namespace App\Services;

use App\Repositories\UserRepository;

class UserService
{
    protected $userRepository;

    public function __construct(UserRepository $userRepository)
    {
        $this->userRepository = $userRepository;
    }

    public function getAllUsers()
    {
        return $this->userRepository->getAllUsers();
    }
}

// Controller
use App\Services\UserService;

class UserController extends Controller
{
    private $userService;

    public function __construct(UserService $userService)
    {
        $this->userService = $userService;
    }

    public function index()
    {
        $users = $this->userService->getAllUsers();
        return response()->json($users);
    }
}

Data Transfer Objects (DTOs) Use DTOs to transfer data between layers, separating the logic.

// app/DTOs/UserDTO.php
namespace App\DTOs;

class UserDTO
{
    public $id;
    public $name;

    public function __construct($id, $name)
    {
        $this->id = $id;
        $this->name = $name;
    }
}

// app/Services/UserService.php
namespace App\Services;

use App\Models\User;
use App\DTOs\UserDTO;

class UserService
{
    public function getAllUsers()
    {
        $users = User::all();
        return $users->map(function ($user) {
            return new UserDTO($user->id, $user->name);
        });
    }
}

// Controller
use App\Services\UserService;

class UserController extends Controller
{
    private $userService;

    public function __construct(UserService $userService)
    {
        $this->userService = $userService;
    }

    public function index()
    {
        $users = $this->userService->getAllUsers();
        return response()->json($users);
    }
}

Summary
These approaches leverage Laravel's robust tools and frameworks to maintain a clean separation of concerns between SQL queries and the business logic, enhancing maintainability, scalability, and testability. You can choose the method based on the complexity and size of your project.

Debug School

How to Centralizing Queries Using a Business Logic Layer in Laravel

Advantage of using Bussiness Layer

How to centralize queries in Bussiness Layer

Another Examples

7 way to seprate bussiness layer query from presentation layer

Top comments (0)