Skip to main content

Sql

Astra TechnoAbout 3 min

This provides a comprehensive explanation of the DataForge SQL class architecture, offering developers a structured and modular way to manage SQL queries in their projects. By isolating the data layer into dedicated SQL classes, the library promotes cleaner business logic and reusable query definitions.

1. Overview

The DataForge SQL Library is a query-based structure that allows developers to define SQL queries in modular classes. This ensures reusability, clarity, and separation of concerns in application development.

Key Features

  • Modular SQL Classes: Dedicated SQL classes for each module (e.g., Product, Order).
  • Reusable Query Definitions: Multiple select fields for various use cases.
  • Flexible Data Fetching: Various fetch methods to retrieve data in desired formats.
  • Separation of Data Layer: Encapsulation of query logic within SQL classes, keeping business logic clean.

2. Class Structure

SQL Class

The Sql class, part of the DataForge vendor package, serves as the foundation for all module-specific SQL classes.

Key Characteristics

  1. SQL methods must return a Query object.
  2. Queries are defined modularly within each method.
  3. SQL class files are stored in the following directory.
app/DataForge/Sqls

Example: Product SQL Class

namespace App\DataForge\Sql;

use DataForge\Sql;

class Product extends Sql
{
    public function list(&$data)
    {
        $query = Query('ProductList');

        // Define select fields for various use cases
        $query->select('list', 'p.id, p.name, p.description, p.price, p.stock, c.name AS category');
        $query->select('item', 'p.id, p.name, p.description, p.price, p.stock, c.name AS category, p.image_url');
        $query->select('entity', 'p.id, p.name, p.description, p.price, p.stock, p.category_id, p.image_url ');
        $query->select('category_product_list', 'p.id, p.name, p.description, p.price, p.stock');
        $query->select('total', 'COUNT(DISTINCT p.id) AS total');
        $query->select('dropdown', 'p.id, p.name');

        // Define base table and joins
        $query->from('products AS p');
        $query->inner('categories AS c ON p.category_id = c.id');

        // Define mandatory filters
        $query->filter('p.status = 1');
        
        // Define optional filters
        $query->filterOptional('p.id = {id}');
        $query->filterOptional('p.category_id = {category_id}');
        $query->filterOptional('p.id != {id_skip}');
        $query->filterOptional('p.name LIKE {%request.keyword%}');
        $query->filterOptional('p.price BETWEEN {request.min_price} AND {request.max_price}');

        // Define anyone required filter to avoid data leaks.
        $query->filterAnyOneRequired('CategoryOrId', [
            'p.category_id = {request.categoryId}',
            'p.id = {request.productId}'
        ]);

        // Group by clause
        $query->group('p.id');

        // Sorting and ordering
        $query->order('{request.sort}', '{request.order}');

        return $query;
    }
	
	public function discountDetails(&$data)
    {
        $query = Query('DiscountDetails');

        // Select fields for discount details
        $query->select('list', 'd.product_id, d.discount_percentage, d.start_date, d.end_date');

        // Define base table and join
        $query->from('discounts AS d');
        $query->inner('products AS p ON d.product_id = p.id');

        // Mandatory filter to ensure valid product ID
        $query->filter('d.product_id = {id}');

        // Optional filter for active discounts
        $query->filterOptional('d.start_date <= CURDATE()');
        $query->filterOptional('d.end_date >= CURDATE()');

        // Group by clause to avoid duplicate records
        $query->group('d.product_id');

        return $query;
    }
}

Query Class

The Query helper function defined in the DataForge vendor package, is used to construct SQL queries. SQL class methods return instances of the Query object.

Key Methods

  • select: Define fields to select.
  • from: Define the base table.
  • filter: Define mandatory WHERE conditions.
  • filterOptional: Add optional WHERE conditions.
  • filterAnyOneRequired: Ensure at least one condition is applied.
  • order: Define ordering dynamically.
  • group: Add GROUP BY clauses.

3. Fetch Methods in SQL Class

The Sql class provides several methods to execute queries and retrieve results in various formats:

  1. fetchColumn

    • Returns the first row's first column value.

  2. fetchColumnList

    • Returns the first column values from all matched rows as an array.

  3. fetchRow

    • Returns the first row as an object or array (default: object).

  4. fetchRowList

    • Returns all records as an associative object list or array list.

4. Calling SQL Class Methods

Developers can invoke SQL class methods using the Sql helper function and retrieve results in the desired format.

Syntax

$result = Sql('Module:Method', $options)->FetchMethod();

Parameters

  • Module: The name of the SQL class (e.g., Product).
  • Method: The method name within the SQL class (e.g., list).
  • $options:
    • select: Specifies the query definition to use (e.g., list, item, total).
  • Fetch Methods: Choose the format of the result:
    • fetchRowList(): Returns an associative object list.
    • fetchRow(): Returns a single record as an object or array.
    • fetchColumn(): Returns the first row's first column value.
    • fetchColumnList(): Returns an array of first column values.

5. Examples

5.1 Fetching Multiple Records (List)

$products = Sql('Product:list', ['select' => 'list'])->fetchRowList();
  • Action: Calls the list() method in the Product SQL class.
  • Select Type: 'list' (for a list of products).
  • Result: Returns all matching rows as an associative object list.

5.2 Fetching a Single Record (Item)

$product = Sql('Product:list', ['select' => 'item'])->fetchRow();
  • Action: Calls the list() method in the Product SQL class.
  • Select Type: 'item' (for a single product).
  • Result: Returns the first matching row as an object.

5.3 Fetching Count of Records (Total)

$productCount = Sql('Product:list', ['select' => 'total'])->fetchColumn();
  • Action: Calls the list() method in the Product SQL class.
  • Select Type: 'total' (for the total count).
  • Result: Returns the count of matching rows as a single value.

6. Benefits of Using SQL Classes

  1. Modularity: Each module has a dedicated SQL class, making query management easier.
  2. Reusability: Queries can be defined once and reused dynamically with select types.
  3. Separation of Concerns: Isolates query logic, reducing clutter in business logic.
  4. Dynamic Filtering: Supports optional and required filters to enhance precision.
  5. Error Logging: Named queries aid in debugging by providing meaningful logs without exposing SQL details.
  6. Security: Ensures input sanitization and structured query building to prevent SQL injection.

7. Best Practices

  • SQL Class Naming: Use clear and descriptive names (e.g., Product, Order).
  • Method Naming: Define meaningful method names (e.g., list, details).
  • Error Handling: Name queries during Query instantiation for easier debugging.
  • Reusable Queries: Utilize select types for flexibility across different use cases.
  • Future Extensions: Consider multi-database support for scalability.

This guide serves as the official documentation for the DataForge SQL Library. For further details, refer to the library's repository or contact the development team.

Developed by Astra Tech | MIT Licensed
Copyright © 2025 Astra Techno