PHP code example of michel / php-sql-mapper

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

michel / php-sql-mapper example snippets

use Michel\SqlMapper\QueryBuilder;

// Create a SELECT query
$query = QueryBuilder::select('name', 'email')
    ->from('users')
    ->where('status = "active"')
    ->orderBy('name')
    ->limit(10);

echo $query; // Outputs: SELECT name, email FROM users WHERE status = "active" ORDER BY name LIMIT 10

use Michel\SqlMapper\QueryBuilder;

// Create a SELECT query with INNER JOIN
$query = QueryBuilder::select('u.name', 'a.address')
    ->from('users u')
    ->innerJoin('addresses a ON u.id = a.user_id');

echo $query; // Outputs: SELECT u.name, a.address FROM users u INNER JOIN addresses a ON u.id = a.user_id

use Michel\SqlMapper\QueryBuilder;

// Create a SELECT query with LEFT JOIN
$query = QueryBuilder::select('u.name', 'a.address')
    ->from('users u')
    ->leftJoin('addresses a ON u.id = a.user_id');

echo $query; // Outputs: SELECT u.name, a.address FROM users u LEFT JOIN addresses a ON u.id = a.user_id

use Michel\SqlMapper\QueryBuilder;

// Create a SELECT query with RIGHT JOIN
$query = QueryBuilder::select('u.name', 'a.address')
    ->from('users u')
    ->rightJoin('addresses a ON u.id = a.user_id');

echo $query; // Outputs: SELECT u.name, a.address FROM users u RIGHT JOIN addresses a ON u.id = a.user_id

use Michel\SqlMapper\QueryBuilder;

// Create a SELECT query with DISTINCT using QueryBuilder
$query = QueryBuilder::select('name', 'email')
    ->distinct()
    ->from('users')
    ->where('status = "active"')
    ->orderBy('name')
    ->limit(10);

echo $query; // Outputs: SELECT DISTINCT name, email FROM users WHERE status = "active" ORDER BY name LIMIT 10

use Michel\SqlMapper\QueryBuilder;

// Create a SELECT query with GROUP BY using QueryBuilder
$query = QueryBuilder::select('category_id', 'COUNT(*) as count')
    ->from('products')
    ->groupBy('category_id');

echo $query; // Outputs: SELECT category_id, COUNT(*) as count FROM products GROUP BY category_id

use Michel\SqlMapper\QueryBuilder;

// Create a SELECT query with HAVING using QueryBuilder
$query = QueryBuilder::select('category_id', 'COUNT(*) as count')
    ->from('products')
    ->groupBy('category_id')
    ->having('COUNT(*) > 5');

echo $query; // Outputs: SELECT category_id, COUNT(*) as count FROM products GROUP BY category_id HAVING COUNT(*) > 5

use Michel\SqlMapper\QueryBuilder;

// Create an INSERT query
$query = QueryBuilder::insert('users')
    ->setValue('name', '"John Doe"')
    ->setValue('email', '"[email protected]"')
    ->setValue('status', '"active"');

echo $query; // Outputs: INSERT INTO users (name, email, status) VALUES ("John Doe", "[email protected]", "active")

use Michel\SqlMapper\QueryBuilder;

// Create an UPDATE query
$query = QueryBuilder::update('users')
    ->set('status', '"inactive"')
    ->where('id = 123');

echo $query; // Outputs: UPDATE users SET status = "inactive" WHERE id = 123

use Michel\SqlMapper\QueryBuilder;

// Create a DELETE query
$query = QueryBuilder::delete('users')
    ->where('status = "inactive"');

echo $query; // Outputs: DELETE FROM users WHERE status = "inactive"

use Michel\SqlMapper\QueryBuilder;
use Michel\SqlMapper\Expression\Expr;

// Example of a query with a custom expression
$whereClause = Expr::greaterThan('age', '18');
$query = QueryBuilder::select('name', 'email')
    ->from('users')
    ->where($whereClause);

echo $query; // Outputs: SELECT name, email FROM users WHERE age > 18

use Michel\SqlMapper\Expression\Expr;

// Example: Generate an equal comparison expression
$equalExpr = Expr::equal('age', '30');
echo "Equal Expression: $equalExpr"; // Outputs: Equal Expression: age = 30

use Michel\SqlMapper\Expression\Expr;

// Example: Generate a not equal comparison expression
$notEqualExpr = Expr::notEqual('status', '"active"');
echo "Not Equal Expression: $notEqualExpr"; // Outputs: Not Equal Expression: status <> "active"

use Michel\SqlMapper\Expression\Expr;

// Example: Generate a greater than comparison expression
$greaterThanExpr = Expr::greaterThan('salary', '50000');
echo "Greater Than Expression: $greaterThanExpr"; // Outputs: Greater Than Expression: salary > 50000

use Michel\SqlMapper\Expression\Expr;

// Example: Generate a greater than or equal comparison expression
$greaterThanEqualExpr = Expr::greaterThanEqual('points', '100');
echo "Greater Than or Equal Expression: $greaterThanEqualExpr"; // Outputs: Greater Than or Equal Expression: points >= 100

use Michel\SqlMapper\Expression\Expr;

// Example: Generate a lower than comparison expression
$lowerThanExpr = Expr::lowerThan('price', '50');
echo "Lower Than Expression: $lowerThanExpr"; // Outputs: Lower Than Expression: price < 50

use Michel\SqlMapper\Expression\Expr;

// Example: Generate a lower than or equal comparison expression
$lowerThanEqualExpr = Expr::lowerThanEqual('quantity', '10');
echo "Lower Than or Equal Expression: $lowerThanEqualExpr"; // Outputs: Lower Than or Equal Expression: quantity <= 10

use Michel\SqlMapper\Expression\Expr;

// Example: Generate an IS NULL expression
$isNullExpr = Expr::isNull('description');
echo "IS NULL Expression: $isNullExpr"; // Outputs: IS NULL Expression: description IS NULL

use Michel\SqlMapper\Expression\Expr;

// Example: Generate an IS NOT NULL expression
$isNotNullExpr = Expr::isNotNull('created_at');
echo "IS NOT NULL Expression: $isNotNullExpr"; // Outputs: IS NOT NULL Expression: created_at IS NOT NULL

use Michel\SqlMapper\Expression\Expr;

// Example: Generate an IN expression
$inExpr = Expr::in('category_id', [1, 2, 3]);
echo "IN Expression: $inExpr"; // Outputs: IN Expression: category_id IN (1, 2, 3)

use Michel\SqlMapper\Expression\Expr;

// Example: Generate a NOT IN expression
$notInExpr = Expr::notIn('role', ['"admin"', '"manager"']);
echo "NOT IN Expression: $notInExpr"; // Outputs: NOT IN Expression: role NOT IN ("admin", "manager")

use Michel\SqlMapper\QL\JoinQL;

// 1. Initialize JoinQL with your PDO connection
$pdo = new PDO('your_dsn', 'user', 'pass');
$joinQL = new JoinQL($pdo);

// 2. Build the query
$user = $joinQL
    // Start with the primary entity
    ->select('users', 'u', ['id', 'name'])
    
    // Join the related entity
    ->leftJoin(
        'u',                  // From table alias (the parent)
        'posts',              // To table (the child)
        'p',                  // To table alias
        ['u.id = p.user_id'], // Join condition
        true,                 // IS a one-to-many relationship
        'posts'               // The key for the nested posts array in the result
    )
    
    // Add conditions and parameters as usual
    ->where('u.id = :user_id')
    ->setParam('user_id', 1)
    
    // 3. Fetch the structured result
    ->getOneOrNullResult();

/*
The $user variable will contain a perfectly structured array:

[
    'id' => 1,
    'name' => 'John Doe',
    'posts' => [
        [
            'id' => 123,
            'title' => 'My First Post',
            'content' => '...'
        ],
        [
            'id' => 124,
            'title' => 'Another Post',
            'content' => '...'
        ]
    ]
]
*/

use Michel\SqlMapper\\QL\JoinQL;

$pdo = new PDO('your_dsn', 'user', 'pass');
$joinQL = new JoinQL($pdo);

$post = $joinQL
    ->select('posts', 'p', ['id', 'title', 'content'])
    ->leftJoin(
        'p',                  // From table alias
        'users',              // To table
        'u',                  // To table alias
        ['p.user_id = u.id'], // Join condition
        false,                // NOT a one-to-many relationship (it's one-to-one)
        'author'              // The key for the nested author object
    )
    ->where('p.id = :post_id')
    ->setParam('post_id', 123)
    ->getOneOrNullResult();

/*
The $post variable will look like this:

[
    'id' => 123,
    'title' => 'My First Post',
    'content' => '...',
    'author' => [
        'id' => 1,
        'name' => 'John Doe'
    ]
]
*/

bash
composer