PHP code example of squirrelphp / entities

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

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

    

squirrelphp / entities example snippets

namespace Application\Entity;

use Squirrel\Entities\Attribute\Entity;
use Squirrel\Entities\Attribute\Field;

#[Entity("users")]
class User
{
    #[Field("user_id", autoincrement: true)]
    private int $userId;

    #[Field("active")]
    private bool $active;

    #[Field("street_name")]
    private ?string $streetName;

    #[Field("street_number")]
    private ?string $streetNumber;

    #[Field("city")]
    private string $city;

    #[Field("balance")]
    private float $balance;

    #[Field("picture_file", blob: true)]
    private ?string $picture;

    #[Field("visits")]
    private int $visitsNumber;
}

$repositoryConfig = new \Squirrel\Entities\RepositoryConfig(
    '', // connectionName, none defined
    'users', // tableName
    [   // tableToObjectFields, mapping table column names to object property names
        'user_id' => 'userId',
        'active' => 'active',
        'street_name' => 'streetName',
        'street_number' => 'streetNumber',
        'city' => 'city',
        'balance' => 'balance',
        'picture_file' => 'picture',
        'visits' => 'visitsNumber',
    ],
    [   // objectToTableFields, mapping object property names to table column names
        'userId' => 'user_id',
        'active' => 'active',
        'streetName' => 'street_name',
        'streetNumber' => 'street_number',
        'city' => 'city',
        'balance' => 'balance',
        'picture' => 'picture_file',
        'visitsNumber' => 'visits',
    ],
    \Application\Entity\User::class, // object class
    [   // objectTypes, which class properties should have which database type
        'userId' => 'int',
        'active' => 'bool',
        'streetName' => 'string',
        'streetNumber' => 'string',
        'city' => 'string',
        'balance' => 'float',
        'picture' => 'blob',
        'visitsNumber' => 'int',
    ],
    [   // objectTypesNullable, which fields can be NULL
        'userId' => false,
        'active' => false,
        'streetName' => true,
        'streetNumber' => true,
        'city' => false,
        'balance' => false,
        'picture' => true,
        'visitsNumber' => false,
    ],
    'user_id' // Table field name of the autoincrement column - if there is none this is an empty string
);

$users = $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
    ->select()
    ->where([
        'active' => true,
        'userId' => [5, 77, 186],
    ])
    ->orderBy([
        'balance' => 'DESC',
    ])
    ->getAllEntries();

foreach ($users as $user) {
    // Each $user entry is an instance of Application\Entity\User
}

$users = $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
    ->select()
    ->fields([
        'userId',
        'active',
        'city',
    ])
    ->where([
        'active' => true,
        'userId' => [5, 77, 186],
    ])
    ->orderBy([
        'balance' => 'DESC',
    ])
    ->getAllEntries();

foreach ($users as $user) {
    // Only 'userId', 'active' and 'city' have been populated in the entity instances
}

$userIds = $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
    ->select()
    ->fields([
        'userId',
    ])
    ->where([
        'active' => true,
        'userId' => [5, 77, 186],
    ])
    ->orderBy([
        'balance' => 'DESC',
    ])
    ->getFlattenedFields();

foreach ($userIds as $userId) {
    // Each $userId is an integer with the user ID
}

$userBuilder = $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
    ->select()
    ->where([
        'active' => true,
        'userId' => [5, 77, 186],
    ])
    ->orderBy([
        'balance' => 'DESC',
    ]);

foreach ($userBuilder as $user) {
    // The query is executed when the foreach loop starts,
    // and one entry after another is retrieved until no more results exist
}

$user = $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
    ->select()
    ->where([
        'userId' => 13,
    ])
    ->getOneEntry();

// $user is now either null, if the entry was not found,
// or an instance of Application\Entity\User

$user = $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
    ->select()
    ->where([
        'userId' => 13,
    ])
    ->blocking()
    ->getOneEntry();

$activeUsersNumber = $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
    ->count()
    ->where([
        'active' => true,
    ])
    ->getNumber();

// $activeUsersNumber is an integer
if ($activeUsersNumber === 0) {
    throw new \Exception('No users found!');
}

$newUserId = $userRepositoryWriteable // \Application\Entity\UserRepositoryWriteable instance
    ->insert()
    ->set([
        'active' => true,
        'city' => 'London',
        'balance' => 500,
    ])
    ->writeAndReturnNewId();

$foundRows = $userRepositoryWriteable // \Application\Entity\UserRepositoryWriteable instance
    ->update()
    ->set([
        'active' => false,
        'city' => 'Paris',
    ])
    ->where([
        'userId' => 5,
    ])
    ->writeAndReturnAffectedNumber();

$userRepositoryWriteable // \Application\Entity\UserRepositoryWriteable instance
    ->update()
    ->set([
        'active' => false,
    ])
    ->confirmNoWhereRestrictions()
    ->write();

$userRepositoryWriteable // \Application\Entity\UserRepositoryWriteable instance
    ->insertOrUpdate()
    ->set([
        'userId' => 5,
        'active' => true,
        'city' => 'Paris',
        'balance' => 500,
    ])
    ->index('userId')
    ->write();

$userRepositoryWriteable // \Application\Entity\UserRepositoryWriteable instance
    ->insertOrUpdate()
    ->set([
        'userId' => 5,
        'active' => true,
        'city' => 'Paris',
        'balance' => 500,
    ])
    ->index('userId')
    ->setOnUpdate([
        'balance' => 500,
    ])
    ->write();

$userRepositoryWriteable // \Application\Entity\UserRepositoryWriteable instance
    ->insertOrUpdate()
    ->set([
        'userId' => 5,
        'active' => true,
        'visitsNumber' => 1,
    ])
    ->index('userId')
    ->setOnUpdate([
        ':visitsNumber: = :visitsNumber: + 1',
    ])
    ->write();

$deletedNumber = $userRepositoryWriteable // \Application\Entity\UserRepositoryWriteable instance
    ->delete()
    ->where([
        'active' => true,
    ])
    ->writeAndReturnAffectedNumber();

$userRepositoryWriteable // \Application\Entity\UserRepositoryWriteable instance
    ->delete()
    ->confirmNoWhereRestrictions()
    ->write();

namespace Application\Entity;

use Squirrel\Entities\Attribute\Entity;
use Squirrel\Entities\Attribute\Field;

#[Entity("users_visits")]
class Visit
{
    #[Field("visit_id", autoincrement: true)]
    private int $visitId = 0;

    #[Field("user_id")]
    private int $userId = 0;

    #[Field("created_timestamp")]
    private int $timestamp = 0;
}

$multiBuilder = new \Squirrel\Entities\MultiRepositoryBuilderReadOnly();

$entries = $multiBuilder
    ->select()
    ->fields([
        'user.userId',
        'user.active',
        'visit.timestamp',
    ])
    ->inRepositories([
        'user' => $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
        'visit' => $visitRepositoryReadOnly // \Application\Entity\VisitRepositoryReadOnly instance
    ])
    ->where([
        ':user.userId: = :visit.userId:',
        'user.userId' => 5,
    ])
    ->orderBy([
        'visit.timestamp' => 'DESC',
    ])
    ->limitTo(10)
    ->getAllEntries();

foreach ($entries as $entry) {
    // Each $entry has the following data in it:
    // - $entry['user.userId'] as an integer
    // - $entry['user.active'] as a boolean
    // - $entry['visit.timestamp'] as an integer
}

$entries = $multiBuilder
    ->select()
    ->fields([
        'userId' => 'user.userId',
        'isActive' => 'user.active',
        'visitTimestamp' => 'visit.timestamp',
    ])
    ->inRepositories([
        'user' => $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
        'visit' => $visitRepositoryReadOnly // \Application\Entity\VisitRepositoryReadOnly instance
    ])
    ->where([
        ':user.userId: = :visit.userId:',
        'user.userId' => 5,
    ])
    ->getAllEntries();

foreach ($entries as $entry) {
    // Each $entry has the following data in it:
    // - $entry['userId'] as an integer
    // - $entry['isActive'] as a boolean
    // - $entry['visitTimestamp'] as an integer
}

$multiBuilder = new \Squirrel\Entities\MultiRepositoryBuilderReadOnly();

$entries = $multiBuilder
    ->select()
    ->fields([
        'visit.userId',
        'visit.timestamp',
        'userIdWhenActive' => 'user.userId',
    ])
    ->inRepositories([
        'user' => $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
        'visit' => $visitRepositoryReadOnly // \Application\Entity\VisitRepositoryReadOnly instance
    ])
    ->joinTables([
        ':visit: LEFT JOIN :user: ON (:user.userId: = :visit.userId: AND :user.active: = ?)' => true,
    ])
    ->where([
        ':visit.timestamp: > ?' => time() - 86400, // Visit timestamp within the last 24 hours
    ])
    ->groupBy([
        'visit.userId',
    ])
    ->orderBy([
        'visit.timestamp' => 'DESC',
    ])
    ->limitTo(5)
    ->startAt(10)
    ->blocking()
    ->getAllEntries();

foreach ($entries as $entry) {
    // Each $entry has the following data in it:
    // - $entry['visit.userId'] as an integer
    // - $entry['visit.timestamp'] as an integer
    // - $entry['userIdWhenActive'] as an integer if the LEFT JOIN was successful, otherwise NULL
}

$selectBuilder = $multiBuilder
    ->select()
    ->fields([
        'userId' => 'user.userId',
        'isActive' => 'user.active',
        'visitTimestamp' => 'visit.timestamp',
    ])
    ->inRepositories([
        'user' => $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
        'visit' => $visitRepositoryReadOnly // \Application\Entity\VisitRepositoryReadOnly instance
    ])
    ->where([
        ':user.userId: = :visit.userId:',
        'user.userId' => 5,
    ]);

foreach ($selectBuilder as $entry) {
    // Each $entry has the following data in it:
    // - $entry['userId'] as an integer
    // - $entry['isActive'] as a boolean
    // - $entry['visitTimestamp'] as an integer
}

$entriesNumber = $multiBuilder
    ->count()
    ->inRepositories([
        'user' => $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
        'visit' => $visitRepositoryReadOnly // \Application\Entity\VisitRepositoryReadOnly instance
    ])
    ->where([
        ':user.userId: = :visit.userId:',
        'user.userId' => 5,
    ])
    ->getNumber();

// $entriesNumber now contains the number of visits of userId = 5

$entries = $multiBuilder
    ->selectFreeform()
    ->fields([
        'userId' => 'user.userId',
        'isActive' => 'user.active',
    ])
    ->inRepositories([
        'user' => $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
        'visit' => $visitRepositoryReadOnly // \Application\Entity\VisitRepositoryReadOnly instance
    ])
    ->queryAfterFROM(':user: WHERE :user.userId: = ? AND NOT EXISTS ( SELECT * FROM :visit: WHERE :user.userId: = :visit.userId: )')
    ->withParameters([5])
    ->confirmFreeformQueriesAreNotRecommended('OK')
    ->getAllEntries();

foreach ($entries as $entry) {
    // Each $entry has the following data in it:
    // - $entry['userId'] as an integer
    // - $entry['isActive'] as a boolean
}

$multiBuilder
    ->updateFreeform()
    ->inRepositories([
        'user' => $userRepositoryReadOnly // \Application\Entity\UserRepositoryReadOnly instance
        'visit' => $visitRepositoryReadOnly // \Application\Entity\VisitRepositoryReadOnly instance
    ])
    ->query('UPDATE :user:, :visit: SET :visit.timestamp: = ? WHERE :user.userId: = :visit.userId: AND :user.userId: = ?')
    ->withParameters([time(), 5])
    ->confirmFreeformQueriesAreNotRecommended('OK')
    ->write();

use Squirrel\Entities\Transaction;

// Transaction class checks that all involved repositories use
// the same database connection so a transaction is actually possible
$transactionHandler = Transaction::withRepositories([
    $userRepositoryWriteable, // \Application\Entity\UserRepositoryWriteable instance
    $visitRepositoryReadOnly, // \Application\Entity\VisitRepositoryReadOnly instance
]);

// Passing additional arguments via `use` is recommended - you could also pass them as function arguments
$transactionHandler->run(function () use ($userId, $userRepositoryWriteable, $visitRepositoryReadOnly) {
    $visitsNumber = $visitRepositoryReadOnly
        ->count()
        ->where([
            'userId' => $userId,
        ])
        ->blocking()
        ->getNumber();

    $userRepositoryWriteable
        ->update()
        ->set([
            'visitsNumber' => $visitsNumber,
        ])
        ->where([
            'userId' => $userId,
        ])
        ->write();
});

namespace Application\Entity;

use Squirrel\Entities\Attribute\Entity;
use Squirrel\Entities\Attribute\Field;

#[Entity("users")]
class User
{
    #[Field("user_id", autoincrement: true)]
    private int $userId = 0;

    #[Field("active")]
    private bool $active = false;

    /** @var string JSON type in the database */
    #[Field("note_data")]
    private string $notes = '';

    /** @var string datetime type in the database */
    #[Field("created")]
    private string $createDate = '';

    public function getUserId(): int
    {
        return $this->userId;
    }

    public function isActive(): bool
    {
        return $this->active;
    }

    public function getNotes(): array
    {
        return \json_decode($this->notes, true);
    }

    public function getCreateDate(): \DateTimeImmutable
    {
        return new \DateTimeImmutable($this->createDate, new \DateTimeZone('Europe/London'));
    }
}

namespace Application\Value;

class GeoPoint
{
    public function __construct(
        private float $lat = 0,
        private float $lng = 0,
    ) {
    }

    public function getLatitude(): float
    {
        return $this->lat;
    }

    public function getLongitude(): float
    {
        return $this->lng;
    }
}

namespace Application\Entity;

use Application\Value\GeoPoint;
use Squirrel\Entities\Attribute\Entity;
use Squirrel\Entities\Attribute\Field;

#[Entity("users_locations")]
class UserLocation
{
    #[Field("user_id")]
    private int $userId = 0;

    /** @var string "point" type in Postgres */
    #[Field("location")]
    private string $locationPoint = '';

    public function getUserId(): int
    {
        return $this->userId;
    }

    /**
     * Convert the point syntax from the database into a value object
     */
    public function getLocation(): GeoPoint
    {
        $point = \explode(',', \trim($this->locationPoint, '()'));

        return new GeoPoint($point[0], $point[1]);
    }
}