PHP code example of centamiv / concrete

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

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

    

centamiv / concrete example snippets

use Concrete\Database;
use Concrete\Connection\MysqlDriver;

Database::init(new MysqlDriver(), '127.0.0.1', 'my_database', 'root', 'password');

use Concrete\Database;
use Concrete\Connection\SqliteDriver;

// Second parameter is the file path (or ':memory:' for in-memory)
Database::init(new SqliteDriver(), '/path/to/database.sqlite');

use Concrete\Database;
use Concrete\Connection\PostgresDriver;

Database::init(new PostgresDriver(), '127.0.0.1', 'my_database', 'postgres', 'password');

use Concrete\Database;
use Concrete\Connection\SqlServerDriver;

Database::init(new SqlServerDriver(), 'localhost', 'my_database', 'sa', 'password');

namespace App\Models;

use Concrete\Model;

class User extends Model
{
    public const TABLE = 'users';

    // Optional: column name constants for type-safe references
    public const COL_ID         = 'id';
    public const COL_NAME       = 'name';
    public const COL_EMAIL      = 'email';
    public const COL_AGE        = 'age';
    public const COL_ACTIVE     = 'active';
    public const COL_ROLE_ID    = 'role_id';
    public const COL_CREATED_AT = 'created_at';
}

class Order extends Model
{
    public const TABLE       = 'orders';
    public const PRIMARY_KEY = 'order_code'; // custom single key
}

class OrderItem extends Model
{
    public const TABLE       = 'order_items';
    public const PRIMARY_KEY = ['order_id', 'product_id']; // composite key
}

$user = new User();
$user->set(User::COL_NAME, 'Mario Rossi')
     ->set(User::COL_EMAIL, '[email protected]')
     ->save();

echo $user->get(User::COL_ID); // auto-incremented ID

// Single primary key
$user = User::find(1);
echo $user?->get(User::COL_NAME);

// Composite primary key
$item = OrderItem::find(['order_id' => 10, 'product_id' => 5]);

$user = User::find(1);
$user->set(User::COL_EMAIL, '[email protected]');
$user->save(); // only dirty attributes are sent to the database

$user = User::find(1);
$user?->delete();

$users = User::query()
    ->where(User::col(User::COL_ACTIVE), '=', 1)
    ->where(User::col(User::COL_AGE), '>', 18)
    ->get();

// Array literal
$users = User::query()
    ->whereIn(User::col(User::COL_ROLE_ID), [1, 2, 3])
    ->get();

// Subquery
$users = User::query()
    ->whereIn(
        User::col(User::COL_ID),
        Order::query()
            ->select('user_id')
            ->where(Order::col('status'), '=', 'paid')
    )
    ->get();

// NOT IN
$users = User::query()
    ->whereNotIn(User::col(User::COL_ID), [10, 20])
    ->get();

$users = User::query()
    ->whereExists(
        Order::query()
            ->whereColumn(Order::col('user_id'), '=', User::col(User::COL_ID))
            ->where(Order::col('status'), '=', 'paid')
    )
    ->get();

Order::query()->whereColumn(Order::col('user_id'), '=', User::col(User::COL_ID));

$users = User::query()
    ->orderBy(User::col(User::COL_CREATED_AT), 'DESC')
    ->get();

$users = User::query()
    ->select(User::col('*'), Role::colAs('name', 'role_name'))
    ->join(Role::TABLE, User::col(User::COL_ROLE_ID), '=', Role::col('id'))
    ->get();

// Left / right join
User::query()->leftJoin(Role::TABLE, User::col(User::COL_ROLE_ID), '=', Role::col('id'));
User::query()->rightJoin(Role::TABLE, User::col(User::COL_ROLE_ID), '=', Role::col('id'));

use Concrete\Query\When;

$users = User::query()
    ->select(
        User::col(User::COL_ID),
        User::col(User::COL_NAME),
        When::make()
            ->when(User::col(User::COL_ACTIVE), '=', 1)->then('Active')
            ->when(User::col(User::COL_ACTIVE), '=', 0)->then('Inactive')
            ->else('Unknown')
            ->as('status_label'),
        When::make()
            ->when(User::col(User::COL_AGE), '>=', 18)->then(1)
            ->else(0)
            ->as('is_adult')
    )
    ->get();

use Concrete\Query\Subquery;

$users = User::query()
    ->select(
        User::col(User::COL_ID),
        User::col(User::COL_NAME),
        Subquery::make(
            Order::query()
                ->select('COUNT(*)')
                ->whereColumn(Order::col('user_id'), '=', User::col(User::COL_ID))
        )->as('order_count')
    )
    ->get();
// → SELECT users.id, users.name,
//     (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
//   FROM users

$activeUsers = User::query()
    ->select(User::col(User::COL_ID), User::col(User::COL_NAME))
    ->where(User::col(User::COL_ACTIVE), '=', 1);

$adminUsers = User::query()
    ->select(User::col(User::COL_ID), User::col(User::COL_NAME))
    ->where(User::col(User::COL_ROLE_ID), '=', 99);

// Distinct rows
$result = $activeUsers
    ->union($adminUsers)
    ->orderBy(User::col(User::COL_NAME), 'ASC')
    ->take(20)
    ->get();

// Including duplicates
$result = $activeUsers->unionAll($adminUsers)->get();

$users = User::query()
    ->take(10)  // LIMIT 10
    ->skip(20)  // OFFSET 20
    ->get();

// Total count
$total = User::query()->where(User::col(User::COL_ACTIVE), '=', 1)->count();

// First matching record
$user = User::query()->where(User::col(User::COL_EMAIL), '=', '[email protected]')->first();

// Check existence
$exists = User::query()->where(User::col(User::COL_AGE), '<', 18)->exists();

// Update active flag for all inactive users
User::query()
    ->where(User::col(User::COL_ACTIVE), '=', 0)
    ->update([User::COL_ACTIVE => 1]);

// Delete underage users
User::query()
    ->where(User::col(User::COL_AGE), '<', 18)
    ->delete();

$rows = User::query()->where(User::col(User::COL_ACTIVE), '=', 1)->getRows();
foreach ($rows as $row) {
    echo $row['name'];
}

$row = User::query()->where(User::col(User::COL_EMAIL), '=', '[email protected]')->firstRow();
echo $row['email'] ?? 'not found';

$sql = User::query()
    ->where(User::col(User::COL_ACTIVE), '=', 1)
    ->sql();

// SELECT * FROM users WHERE users.active = :users_active0

use Concrete\Database;

$stmt = Database::getConnection()->query('SELECT COUNT(*) as n FROM users GROUP BY age');
$rows = $stmt->fetchAll();

use Concrete\Query\Builder;

$builder = new Builder();
$users = $builder->table(User::class)
    ->where(User::col(User::COL_ACTIVE), '=', 1)
    ->get();

// app/Providers/AppServiceProvider.php
use Concrete\Database;
use Concrete\Connection\MysqlDriver;
use Illuminate\Support\Facades\DB;

public function boot(): void
{
    Database::initFromPDO(
        DB::connection()->getPdo(),
        new MysqlDriver()
    );
}