Database and Cycle ORM

Flute CMS uses Cycle ORM with the ActiveRecord pattern for working with the database. Each object represents a record in the DB and contains both data and methods to work with it.

ActiveRecord is the recommended approach in Flute CMS. The old way via rep() is supported for backward compatibility, but use ActiveRecord methods for new modules.

Key Features

Automatic schema compilation from annotated entities
Schema caching in storage/app/orm_schema.php
Helpers: orm(), rep(), transaction()
ActiveRecord as the main API

Creating Entities

File Structure

Module entities are located in the database/Entities folder:

- - - Article.php
    - Category.php
    - Tag.php
    - Comment.php

For entities to load, the provider must call loadEntities() (automatically executed in bootstrapModule()).

Basic Entity

Let’s create a blog article entity with Cycle ORM annotations:


<?php
namespace Flute\Modules\Blog\Database\Entities;
 
use Cycle\ActiveRecord\ActiveRecord;
use Cycle\Annotated\Annotation\Column;
use Cycle\Annotated\Annotation\Entity;
use Cycle\Annotated\Annotation\Table\Index;
use Cycle\ORM\Entity\Behavior;
 
// Specify table and unique role for ORM
#[Entity(table: 'blog_articles', role: 'blog_article')]
 
// Create indexes for frequently used fields
#[Index(columns: ['slug'], unique: true)]
#[Index(columns: ['status', 'created_at'])]
 
// Automatically fill creation and update dates
#[Behavior\CreatedAt(field: 'createdAt', column: 'created_at')]
#[Behavior\UpdatedAt(field: 'updatedAt', column: 'updated_at')]
class Article extends ActiveRecord
{
    // Primary key — autoincrement
    #[Column(type: 'primary')]
    public int $id;
 
    #[Column(type: 'string')]
    public string $title;
 
    // Unique slug for URL
    #[Column(type: 'string')]
    public string $slug;
 
    // Nullable fields are specified explicitly
    #[Column(type: 'text', nullable: true)]
    public ?string $excerpt = null;
 
    #[Column(type: 'text')]
    public string $content;
 
    // Enum with default value
    #[Column(type: 'enum(draft,published,scheduled)', default: 'draft')]
    public string $status = 'draft';
 
    #[Column(type: 'datetime', nullable: true)]
    public ?\DateTimeImmutable $publishedAt = null;
 
    #[Column(type: 'integer', default: 0)]
    public int $views = 0;
 
    // Automatically filled fields
    #[Column(type: 'datetime')]
    public \DateTimeImmutable $createdAt;
 
    #[Column(type: 'datetime', nullable: true)]
    public ?\DateTimeImmutable $updatedAt = null;
}

Annotation Explanation:

Annotation	Purpose
`#[Entity]`	Declares the class as an ORM entity, specifies table and role
`#[Column]`	Describes table field: type, nullable, default
`#[Index]`	Creates an index to speed up search
`#[Behavior\CreatedAt]`	Automatically fills creation date
`#[Behavior\UpdatedAt]`	Automatically updates modification date

Column Types

Cycle ORM supports various data types:

Type	Description	Example
`primary`	Autoincrement ID	`#[Column(type: 'primary')]`
`string`	String (VARCHAR)	`#[Column(type: 'string')]`
`text`	Long text	`#[Column(type: 'text')]`
`integer`	Integer	`#[Column(type: 'integer')]`
`boolean`	Boolean value	`#[Column(type: 'boolean')]`
`datetime`	Date and time	`#[Column(type: 'datetime')]`
`float`	Floating point number	`#[Column(type: 'float')]`
`enum`	Enumeration	`#[Column(type: 'enum(a,b,c)')]`
`json`	JSON data	`#[Column(type: 'json')]`

Single Table Inheritance

When multiple content types are stored in one table, use Single Table Inheritance:


<?php
namespace Flute\Modules\Blog\Database\Entities;
 
use Cycle\Annotated\Annotation\Column;
use Cycle\Annotated\Annotation\Entity;
use Cycle\Annotated\Annotation\Inheritance\DiscriminatorColumn;
use Cycle\Annotated\Annotation\Inheritance\SingleTable;
 
// Base abstract class
#[Entity]
#[SingleTable]  // All heirs are stored in one table
#[DiscriminatorColumn(name: 'type')]  // Field for determining type
abstract class Content
{
    #[Column(type: 'primary')]
    public int $id;
 
    #[Column(type: 'string')]
    public string $title;
 
    #[Column(type: 'text')]
    public string $content;
 
    #[Column(type: 'string')]
    public string $status = 'draft';
 
    #[Column(type: 'datetime')]
    public \DateTime $created_at;
 
    #[Column(type: 'datetime', nullable: true)]
    public ?\DateTime $updated_at;
 
    // Abstract method — each heir defines its type
    abstract public function getType(): string;
}
 
// Article — one content type
#[Entity]
class Article extends Content
{
    #[Column(type: 'string', nullable: true)]
    public ?string $excerpt;
 
    public function getType(): string
    {
        return 'article';
    }
}
 
// Page — another content type
#[Entity]
class Page extends Content
{
    #[Column(type: 'string', nullable: true)]
    public ?string $slug;
 
    public function getType(): string
    {
        return 'page';
    }
}

When to use Single Table Inheritance:

Types share many common fields
Polymorphic queries are needed (get all content types)
The number of specific fields is small

Usage:


<?php
// Get all content types
$allContent = Content::findAll();
 
// Get only articles
$articles = Article::findAll();
 
// Get only pages  
$pages = Page::findAll();

Entity Relations

BelongsTo — Belongs To One

Article belongs to a category and an author:


<?php
use Cycle\Annotated\Annotation\Relation\BelongsTo;
use Flute\Core\Database\Entities\User;
 
class Article extends ActiveRecord
{
    // ... other fields
 
    // Article belongs to one author (can be null)
    #[BelongsTo(target: User::class, nullable: true)]
    public ?User $author = null;
 
    // Article belongs to one category
    #[BelongsTo(target: Category::class, nullable: true)]
    public ?Category $category = null;
}

HasMany — Has Many

Category has many articles:


<?php
use Cycle\Annotated\Annotation\Relation\HasMany;
 
#[Entity(table: 'blog_categories')]
class Category extends ActiveRecord
{
    #[Column(type: 'primary')]
    public int $id;
 
    #[Column(type: 'string')]
    public string $name;
 
    #[Column(type: 'string')]
    public string $slug;
 
    // One category contains many articles
    #[HasMany(target: Article::class)]
    public array $articles = [];
}

HasOne — Has One

User has one profile:


<?php
use Cycle\Annotated\Annotation\Relation\HasOne;
 
#[Entity(table: 'users')]
class User extends ActiveRecord
{
    #[Column(type: 'primary')]
    public int $id;
 
    // One user has one profile
    #[HasOne(target: UserProfile::class)]
    public ?UserProfile $profile = null;
}

ManyToMany — Many to Many

Article can have many tags, tag can belong to many articles:


<?php
use Cycle\Annotated\Annotation\Relation\ManyToMany;
 
// Intermediate table for relation
#[Entity(table: 'blog_article_tags')]
class ArticleTag extends ActiveRecord
{
    #[Column(type: 'primary')]
    public int $id;
 
    #[BelongsTo(target: Article::class)]
    public Article $article;
 
    #[BelongsTo(target: Tag::class)]
    public Tag $tag;
}
 
// In Article entity
#[ManyToMany(target: Tag::class, through: ArticleTag::class)]
public array $tags = [];
 
// In Tag entity
#[ManyToMany(target: Article::class, through: ArticleTag::class)]
public array $articles = [];

Working with Data

Finding Records

ActiveRecord provides static methods for searching:


<?php
use Flute\Modules\Blog\Database\Entities\Article;
 
// Find by primary key
$article = Article::findByPK(1);
 
// Find one record by condition
$article = Article::findOne(['slug' => 'my-article']);
 
// Get all records
$articles = Article::findAll();
 
// Get with conditions
$published = Article::findAll(['status' => 'published']);

Query Builder

For complex queries, use query():


<?php
// Basic query with conditions
$articles = Article::query()
    ->where('status', 'published')
    ->where('publishedAt', '>=', new \DateTimeImmutable('-1 month'))
    ->orderBy('views', 'DESC')
    ->limit(10)
    ->fetchAll();

Available Query Builder methods:


<?php
$query = Article::query()
    // WHERE conditions
    ->where('field', 'value')           // Equality
    ->where('field', '>', 100)          // With operator
    ->where('field', 'LIKE', '%text%')  // Pattern search
    
    // Additional conditions
    ->whereIn('status', ['draft', 'published'])
    ->whereNull('deletedAt')
    ->whereNotNull('publishedAt')
    ->orWhere('featured', true)
    
    // Sorting and limits
    ->orderBy('createdAt', 'DESC')
    ->limit(20)
    ->offset(40);
 
// Executing query
$results = $query->fetchAll();   // All results
$result = $query->fetchOne();    // One record
$count = $query->count();        // Count
$exists = $query->exists();      // Do records exist

Grouping Conditions

For complex conditions, use closures:


<?php
$articles = Article::query()
    ->where('status', 'published')
    ->where(function($q) {
        // Conditions inside brackets are combined with OR
        $q->where('featured', true)
          ->orWhere('views', '>', 1000);
    })
    ->fetchAll();
 
// SQL: WHERE status = 'published' AND (featured = true OR views > 1000)

Loading Relations

Use load() to eager load relations (avoids N+1 problem):


<?php
// Load article with all relations
$article = Article::query()
    ->where('id', $id)
    ->load('author')      // Load author
    ->load('category')    // Load category
    ->load('tags')        // Load tags
    ->load('comments')    // Load comments
    ->fetchOne();
 
// Now can access without extra queries
echo $article->author->name;
echo $article->category->name;

Service for Data Loading

Create a separate service for working with relation loading:


<?php
namespace Flute\Modules\Blog\Services;
 
use Flute\Modules\Blog\Database\Entities\Article;
 
class ArticleLoaderService
{
    /**
     * Get article with all relations for detail page
     */
    public function getArticleWithRelations(int $id): ?Article
    {
        return Article::query()
            ->where('id', $id)
            ->load('author')
            ->load('category')
            ->load('tags')
            ->load('comments')
            ->fetchOne();
    }
 
    /**
     * Get articles for list with needed relations
     */
    public function getArticlesForList(int $page = 1, int $perPage = 10)
    {
        return Article::query()
            ->where('status', 'published')
            ->load('author')
            ->load('category')
            ->load('tags')
            ->orderBy('publishedAt', 'DESC')
            ->paginate($perPage, $page);
    }
 
    /**
     * Get article by slug with publication check
     */
    public function getArticleBySlugWithRelations(string $slug): ?Article
    {
        return Article::query()
            ->where('slug', $slug)
            ->where('status', 'published')
            ->load('author')
            ->load('category')
            ->load('tags')
            ->load('comments')
            ->fetchOne();
    }
 
    /**
     * Get articles of specific author
     */
    public function getArticlesByAuthor(int $authorId, int $limit = 10): array
    {
        return Article::query()
            ->where('author.id', $authorId)
            ->where('status', 'published')
            ->load('category')
            ->load('tags')
            ->orderBy('publishedAt', 'DESC')
            ->limit($limit)
            ->fetchAll();
    }
}

Extended Query Builder

Query Builder supports many methods for building complex queries:


<?php
namespace Flute\Modules\Blog\Services;
 
use Flute\Modules\Blog\Database\Entities\Article;
 
class AdvancedQueryService
{
    /**
     * Demonstration of all Query Builder capabilities
     */
    public function queryBuilderExamples()
    {
        // 1. Various WHERE conditions
        $query = Article::query()
            ->where('status', 'published')              // Simple equality
            ->where('views', '>', 100)                  // With comparison operator
            ->where('title', 'LIKE', '%important%')     // LIKE search
            ->whereIn('category.id', [1, 2, 3])         // IN condition
            ->whereNotIn('status', ['deleted'])         // NOT IN
            ->whereNull('deletedAt')                    // IS NULL
            ->whereNotNull('publishedAt')               // IS NOT NULL
            ->whereBetween('createdAt', [$start, $end]) // BETWEEN
            ->whereNotBetween('views', [0, 10]);        // NOT BETWEEN
 
        // 2. Logical groupings (brackets in SQL)
        $query = Article::query()
            ->where('status', 'published')
            ->where(function($q) {
                // These conditions are combined inside brackets
                $q->where('featured', true)
                  ->orWhere('views', '>', 1000);
            })
            ->orWhere('category.slug', 'urgent');
        // SQL: WHERE status = 'published' AND (featured = true OR views > 1000) OR category.slug = 'urgent'
 
        // 3. Sorting
        $query = Article::query()
            ->orderBy('publishedAt', 'DESC')    // By one field
            ->orderBy('views', 'DESC')          // Additional sorting
            ->orderByRaw('RAND()');             // Random order
 
        // 4. Grouping and aggregation
        $stats = Article::query()
            ->select([
                'category.name',
                'COUNT(*) as count',
                'AVG(views) as avg_views',
                'SUM(views) as total_views',
                'MAX(views) as max_views'
            ])
            ->groupBy('category.id')
            ->having('COUNT(*)', '>', 5)
            ->fetchAll();
 
        // 5. Table joins
        $articles = Article::query()
            ->join('categories', 'categories.id = articles.category_id')
            ->where('categories.active', true)
            ->fetchAll();
 
        // 6. Subqueries
        $popularCategories = Article::query()
            ->select('category_id')
            ->where('views', '>', 1000)
            ->groupBy('category_id');
 
        $articles = Article::query()
            ->whereIn('category_id', $popularCategories)
            ->fetchAll();
 
        // 7. Raw expressions for complex cases
        $monthlyStats = Article::query()
            ->selectRaw('DATE_FORMAT(publishedAt, "%Y-%m") as month, COUNT(*) as count')
            ->where('status', 'published')
            ->groupByRaw('DATE_FORMAT(publishedAt, "%Y-%m")')
            ->orderByRaw('month DESC')
            ->limit(12)
            ->fetchAll();
    }
 
    /**
     * Dynamic search with multiple filters
     */
    public function advancedSearch(array $filters)
    {
        $query = Article::query()->where('status', 'published');
 
        // Text search across multiple fields
        if (!empty($filters['search'])) {
            $search = $filters['search'];
            $query->where(function($q) use ($search) {
                $q->where('title', 'LIKE', "%{$search}%")
                  ->orWhere('content', 'LIKE', "%{$search}%")
                  ->orWhere('excerpt', 'LIKE', "%{$search}%");
            });
        }
 
        // Filter by multiple categories
        if (!empty($filters['category_ids'])) {
            $query->whereIn('category.id', $filters['category_ids']);
        }
 
        // Filter by multiple authors
        if (!empty($filters['author_ids'])) {
            $query->whereIn('author.id', $filters['author_ids']);
        }
 
        // Date range
        if (!empty($filters['date_from'])) {
            $query->where('publishedAt', '>=', new \DateTimeImmutable($filters['date_from']));
        }
        if (!empty($filters['date_to'])) {
            $query->where('publishedAt', '<=', new \DateTimeImmutable($filters['date_to']));
        }
 
        // Minimum views
        if (!empty($filters['min_views'])) {
            $query->where('views', '>=', (int) $filters['min_views']);
        }
 
        // Filter by tags
        if (!empty($filters['tags'])) {
            $query->whereIn('tags.slug', $filters['tags']);
        }
 
        // Safe sorting (only allowed fields)
        $allowedSorts = ['publishedAt', 'views', 'title', 'createdAt'];
        $sortBy = $filters['sort_by'] ?? 'publishedAt';
        $sortOrder = $filters['sort_order'] ?? 'DESC';
        
        if (in_array($sortBy, $allowedSorts)) {
            $query->orderBy($sortBy, $sortOrder);
        }
 
        return $query;
    }
 
    /**
     * Get article statistics
     */
    public function getArticleStatistics(): array
    {
        // Stats by categories
        $categoryStats = Article::query()
            ->select([
                'category.name',
                'category.slug',
                'COUNT(*) as total_articles',
                'SUM(views) as total_views',
                'AVG(views) as average_views',
                'MAX(views) as max_views'
            ])
            ->where('status', 'published')
            ->groupBy('category.id')
            ->orderBy('total_articles', 'DESC')
            ->fetchAll();
 
        // Monthly stats for the year
        $monthlyStats = Article::query()
            ->selectRaw('DATE_FORMAT(publishedAt, "%Y-%m") as month, COUNT(*) as count')
            ->where('status', 'published')
            ->groupByRaw('DATE_FORMAT(publishedAt, "%Y-%m")')
            ->orderByRaw('month DESC')
            ->limit(12)
            ->fetchAll();
 
        // Top authors
        $topAuthors = Article::query()
            ->select([
                'author.id',
                'author.name',
                'COUNT(*) as articles_count',
                'SUM(views) as total_views'
            ])
            ->where('status', 'published')
            ->groupBy('author.id')
            ->orderBy('articles_count', 'DESC')
            ->limit(10)
            ->fetchAll();
 
        return [
            'categories' => $categoryStats,
            'monthly' => $monthlyStats,
            'top_authors' => $topAuthors
        ];
    }
}

Query Execution Methods:


<?php
$query = Article::query()->where('status', 'published');
 
// Getting results
$articles = $query->fetchAll();        // All results as array
$article = $query->fetchOne();         // First result or null
$count = $query->count();              // Record count (without LIMIT)
$exists = $query->exists();            // Check existence
 
// Pagination
$paginated = $query->paginate($perPage, $page);
 
// Iteration for large datasets (saves memory)
foreach ($query->getIterator() as $article) {
    // Process each record individually
}

Filter Query Service

Create a separate service for complex queries with filters:


<?php
namespace Flute\Modules\Blog\Services;
 
use Flute\Modules\Blog\Database\Entities\Article;
 
class ArticleQueryService
{
    /**
     * Get articles with multiple filters
     */
    public function getArticlesWithFilters(array $filters = [])
    {
        $query = Article::query();
 
        // Filter by status (default — published)
        if (isset($filters['status'])) {
            $query->where('status', $filters['status']);
        } else {
            $query->where('status', 'published');
        }
 
        // Filter by category
        if (isset($filters['category_id'])) {
            $query->where('category.id', $filters['category_id']);
        }
 
        // Filter by author
        if (isset($filters['author_id'])) {
            $query->where('author.id', $filters['author_id']);
        }
 
        // Full-text search across multiple fields
        if (isset($filters['search'])) {
            $search = $filters['search'];
            $query->where(function($q) use ($search) {
                $q->where('title', 'LIKE', "%{$search}%")
                  ->orWhere('content', 'LIKE', "%{$search}%")
                  ->orWhere('excerpt', 'LIKE', "%{$search}%");
            });
        }
 
        // Filter by publication date range
        if (isset($filters['date_from'])) {
            $query->where('publishedAt', '>=', $filters['date_from']);
        }
        if (isset($filters['date_to'])) {
            $query->where('publishedAt', '<=', $filters['date_to']);
        }
 
        // Filter by tags (articles with any of the specified tags)
        if (isset($filters['tags']) && !empty($filters['tags'])) {
            $query->whereIn('tags.slug', $filters['tags']);
        }
 
        // Sorting
        $sortBy = $filters['sort_by'] ?? 'publishedAt';
        $sortOrder = $filters['sort_order'] ?? 'DESC';
        $query->orderBy($sortBy, $sortOrder);
 
        return $query;
    }
 
    /**
     * Get articles by category
     */
    public function getArticlesByCategory(string $categorySlug, ?int $limit = null): array
    {
        $query = Article::query()
            ->where('status', 'published')
            ->where('category.slug', $categorySlug)
            ->orderBy('publishedAt', 'DESC');
 
        if ($limit) {
            $query->limit($limit);
        }
 
        return $query->fetchAll();
    }
 
    /**
     * Get scheduled articles for auto-publishing
     */
    public function getScheduledArticles(): array
    {
        $now = new \DateTimeImmutable();
        
        return Article::query()
            ->where('status', 'scheduled')
            ->where('publishedAt', '<=', $now)
            ->fetchAll();
    }
 
    /**
     * Get articles by tag
     */
    public function getArticlesByTag(string $tagSlug, int $limit = 10): array
    {
        return Article::query()
            ->where('status', 'published')
            ->where('tags.slug', $tagSlug)
            ->orderBy('publishedAt', 'DESC')
            ->limit($limit)
            ->fetchAll();
    }
 
    /**
     * Get popular articles for a period
     */
    public function getPopularArticles(?int $categoryId = null, int $days = 30): array
    {
        $query = Article::query()
            ->where('status', 'published')
            ->where('publishedAt', '>=', new \DateTimeImmutable("-{$days} days"))
            ->orderBy('views', 'DESC');
 
        if ($categoryId) {
            $query->where('category.id', $categoryId);
        }
 
        return $query->limit(10)->fetchAll();
    }
 
    /**
     * Get articles with multiple tags (must have ALL specified tags)
     */
    public function getArticlesWithAllTags(array $tagSlugs): array
    {
        return Article::query()
            ->where('status', 'published')
            ->whereIn('tags.slug', $tagSlugs)
            ->groupBy('id')
            ->having('COUNT(DISTINCT tags.id)', '>=', count($tagSlugs))
            ->fetchAll();
    }
 
    /**
     * Get similar articles
     */
    public function getSimilarArticles(Article $article, int $limit = 5): array
    {
        return Article::query()
            ->where('status', 'published')
            ->where('id', '!=', $article->id)
            ->where(function($q) use ($article) {
                // Similar by category
                if ($article->category) {
                    $q->where('category.id', $article->category->id);
                }
                // Or by tags
                if (!empty($article->tags)) {
                    $tagIds = array_map(fn($tag) => $tag->id, $article->tags);
                    $q->orWhereIn('tags.id', $tagIds);
                }
            })
            ->orderBy('publishedAt', 'DESC')
            ->limit($limit)
            ->fetchAll();
    }
}

Creating and Updating

Creating a Record


<?php
// Create new object
$article = new Article();
 
// Fill fields
$article->title = 'New Article';
$article->slug = 'new-article';
$article->content = 'Article text...';
$article->status = 'draft';
 
// Set relations
if (user()->isLoggedIn()) {
    $article->author = user()->getCurrentUser();
}
 
$category = Category::findByPK($categoryId);
if ($category) {
    $article->category = $category;
}
 
// Save to database
$article->save();
 
// ID is available after saving
echo $article->id; // Example: 42

Updating a Record


<?php
// Find record
$article = Article::findByPK($id);
 
if (!$article) {
    throw new \Exception('Article not found');
}
 
// Change fields
$article->title = 'Updated Title';
$article->status = 'published';
$article->publishedAt = new \DateTimeImmutable();
 
// Save changes
$article->save();

Business Logic in Entity

Add business logic methods directly to the entity:


<?php
class Article extends ActiveRecord
{
    // ... fields
 
    /**
     * Check if article is published
     */
    public function isPublished(): bool
    {
        return $this->status === 'published';
    }
 
    /**
     * Publish article
     */
    public function publish(): void
    {
        $this->status = 'published';
        $this->publishedAt = new \DateTimeImmutable();
        $this->save();
    }
 
    /**
     * Unpublish article
     */
    public function unpublish(): void
    {
        $this->status = 'draft';
        $this->publishedAt = null;
        $this->save();
    }
 
    /**
     * Increment view counter
     */
    public function incrementViews(): void
    {
        $this->views++;
        $this->save();
    }
 
    /**
     * Get article URL
     */
    public function getUrl(): string
    {
        return route('blog.articles.show', ['slug' => $this->slug]);
    }
}

Deleting Data

Simple Deletion


<?php
$article = Article::findByPK($id);
 
if ($article) {
    // Delete via transaction helper
    transaction($article, 'delete')->run();
}

Soft Delete

Instead of physical deletion, mark record as deleted:


<?php
class Article extends ActiveRecord
{
    #[Column(type: 'datetime', nullable: true)]
    public ?\DateTimeImmutable $deletedAt = null;
 
    /**
     * Soft delete
     */
    public function softDelete(): void
    {
        $this->deletedAt = new \DateTimeImmutable();
        $this->save();
    }
 
    /**
     * Restore
     */
    public function restore(): void
    {
        $this->deletedAt = null;
        $this->save();
    }
 
    /**
     * Check deletion
     */
    public function isDeleted(): bool
    {
        return $this->deletedAt !== null;
    }
}

Exclude deleted records when querying:


<?php
$articles = Article::query()
    ->whereNull('deletedAt')
    ->fetchAll();

Transactions

Use transactions for atomic operations — if one operation fails, all changes are rolled back.

Basic Transaction


<?php
$transaction = transaction();
 
try {
    // Create article
    $article = new Article();
    $article->title = $data['title'];
    $article->content = $data['content'];
    $transaction->persist($article);
 
    // Create tags
    foreach ($data['tags'] as $tagName) {
        $tag = Tag::findOne(['name' => $tagName]);
        
        if (!$tag) {
            $tag = new Tag();
            $tag->name = $tagName;
            $tag->slug = slugify($tagName);
            $transaction->persist($tag);
        }
 
        // Create relation
        $articleTag = new ArticleTag();
        $articleTag->article = $article;
        $articleTag->tag = $tag;
        $transaction->persist($articleTag);
    }
 
    // Apply all changes atomically
    $transaction->run();
 
    return $article;
 
} catch (\Exception $e) {
    // Rollback ALL changes on any error
    $transaction->rollback();
    throw $e;
}

Complex Transactions with Notifications

Example of publishing an article with sending notifications to subscribers:


<?php
namespace Flute\Modules\Blog\Services;
 
use Flute\Modules\Blog\Database\Entities\Article;
use Flute\Modules\Blog\Database\Entities\Notification;
 
class BlogTransactionService
{
    /**
     * Publish article with subscriber notification
     */
    public function publishArticle(int $articleId): Article
    {
        $transaction = transaction();
 
        try {
            $article = Article::findByPK($articleId);
 
            if (!$article) {
                throw new \Exception('Article not found');
            }
 
            // 1. Publish article
            $article->status = 'published';
            $article->publishedAt = new \DateTimeImmutable();
            $transaction->persist($article);
 
            // 2. Get category subscribers
            $subscribers = $this->getCategorySubscribers($article->category->id);
 
            // 3. Create notifications for each subscriber
            foreach ($subscribers as $subscriber) {
                $notification = new Notification();
                $notification->user = $subscriber;
                $notification->type = 'article_published';
                $notification->data = json_encode([
                    'article_id' => $article->id,
                    'article_title' => $article->title,
                    'category_name' => $article->category->name,
                    'author_name' => $article->author->name
                ]);
                $notification->createdAt = new \DateTimeImmutable();
 
                $transaction->persist($notification);
            }
 
            // 4. Update article counter in category
            if ($article->category) {
                $article->category->articleCount = ($article->category->articleCount ?? 0) + 1;
                $transaction->persist($article->category);
            }
 
            // 5. Update author article counter
            $article->author->articlesCount = ($article->author->articlesCount ?? 0) + 1;
            $transaction->persist($article->author);
 
            // Apply all changes
            $transaction->run();
 
            return $article;
 
        } catch (\Exception $e) {
            $transaction->rollback();
            logs('blog')->error('Article publishing error: ' . $e->getMessage());
            throw $e;
        }
    }
 
    /**
     * Create comment with author notification
     */
    public function createComment(int $articleId, array $data): Comment
    {
        $transaction = transaction();
 
        try {
            $article = Article::findByPK($articleId);
 
            if (!$article) {
                throw new \Exception('Article not found');
            }
 
            // 1. Create comment
            $comment = new Comment();
            $comment->article = $article;
            $comment->author = user()->getCurrentUser();
            $comment->content = $data['content'];
            $comment->approved = config('blog.comments.auto_approve', false);
            $comment->createdAt = new \DateTimeImmutable();
 
            $transaction->persist($comment);
 
            // 2. Increment comment counter
            $article->commentsCount = ($article->commentsCount ?? 0) + 1;
            $transaction->persist($article);
 
            // 3. Notify article author (if not commenting themselves)
            if ($article->author->id !== user()->id) {
                $notification = new Notification();
                $notification->user = $article->author;
                $notification->type = 'new_comment';
                $notification->data = json_encode([
                    'article_id' => $article->id,
                    'article_title' => $article->title,
                    'comment_id' => $comment->id,
                    'commenter_name' => user()->getCurrentUser()->name
                ]);
                $notification->createdAt = new \DateTimeImmutable();
 
                $transaction->persist($notification);
            }
 
            $transaction->run();
 
            return $comment;
 
        } catch (\Exception $e) {
            $transaction->rollback();
            throw $e;
        }
    }
}

Pagination

Basic Pagination


<?php
$page = (int) request()->get('page', 1);
$perPage = 10;
 
$articles = Article::query()
    ->where('status', 'published')
    ->orderBy('createdAt', 'DESC')
    ->paginate($perPage, $page);
 
// Using results
$items = $articles->items();           // Current page records
$total = $articles->total();           // Total records
$currentPage = $articles->currentPage(); // Current page
$lastPage = $articles->lastPage();     // Last page
$hasMorePages = $articles->hasMorePages(); // Has more pages
$onFirstPage = $articles->onFirstPage();   // On first page?

In Controller


<?php
class ArticleController extends BaseController
{
    public function index()
    {
        $page = (int) request()->get('page', 1);
        $perPage = min((int) request()->get('per_page', 10), 50); // Limit max 50
 
        $articles = Article::query()
            ->where('status', 'published')
            ->load('author')
            ->load('category')
            ->orderBy('publishedAt', 'DESC')
            ->paginate($perPage, $page);
 
        return view('blog::articles.index', [
            'articles' => $articles
        ]);
    }
}

Pagination Service with Filters


<?php
namespace Flute\Modules\Blog\Services;
 
use Flute\Modules\Blog\Database\Entities\Article;
 
class ArticlePaginationService
{
    /**
     * Get articles with pagination and filters
     */
    public function getPaginatedArticles(array $filters = [], int $page = 1, int $perPage = 10): array
    {
        $query = Article::query()->where('status', 'published');
 
        // Apply filters
        if (!empty($filters['category_id'])) {
            $query->where('category.id', $filters['category_id']);
        }
 
        if (!empty($filters['author_id'])) {
            $query->where('author.id', $filters['author_id']);
        }
 
        if (!empty($filters['search'])) {
            $search = $filters['search'];
            $query->where(function($q) use ($search) {
                $q->where('title', 'LIKE', "%{$search}%")
                  ->orWhere('content', 'LIKE', "%{$search}%");
            });
        }
 
        // Load relations with field limiting (optimization)
        $query->load('author')
              ->load('category')
              ->load('tags');
 
        // Sorting
        $sortBy = $filters['sort_by'] ?? 'publishedAt';
        $sortOrder = $filters['sort_order'] ?? 'DESC';
        $query->orderBy($sortBy, $sortOrder);
 
        // Pagination
        $paginator = $query->paginate($perPage, $page);
 
        return [
            'items' => $paginator->items(),
            'pagination' => [
                'current_page' => $paginator->currentPage(),
                'last_page' => $paginator->lastPage(),
                'per_page' => $paginator->perPage(),
                'total' => $paginator->total(),
                'from' => $paginator->firstItem(),
                'to' => $paginator->lastItem(),
                'has_more_pages' => $paginator->hasMorePages(),
            ],
            'filters' => $filters
        ];
    }
 
    /**
     * Generate pagination links
     */
    public function getPaginationLinks($paginator, string $baseUrl = null): array
    {
        $links = [];
        $currentPage = $paginator->currentPage();
        $lastPage = $paginator->lastPage();
 
        // Previous page
        $links['previous'] = $paginator->onFirstPage() 
            ? null 
            : ($baseUrl ? $baseUrl . '?page=' . ($currentPage - 1) : null);
 
        // Next page
        $links['next'] = $paginator->hasMorePages() 
            ? ($baseUrl ? $baseUrl . '?page=' . ($currentPage + 1) : null) 
            : null;
 
        // Page numbers (show 5 pages around current)
        $pageNumbers = [];
        $start = max(1, $currentPage - 2);
        $end = min($lastPage, $currentPage + 2);
 
        for ($page = $start; $page <= $end; $page++) {
            $pageNumbers[] = [
                'page' => $page,
                'url' => $baseUrl ? $baseUrl . '?page=' . $page : null,
                'active' => $page === $currentPage
            ];
        }
 
        $links['pages'] = $pageNumbers;
        $links['first'] = $baseUrl ? $baseUrl . '?page=1' : null;
        $links['last'] = $baseUrl ? $baseUrl . '?page=' . $lastPage : null;
 
        return $links;
    }
}

Service Layer

Move complex logic into services:


<?php
namespace Flute\Modules\Blog\Services;
 
use Flute\Modules\Blog\Database\Entities\Article;
use Flute\Modules\Blog\Database\Entities\Category;
 
class ArticleService
{
    /**
     * Get articles with filters
     */
    public function getArticles(array $filters = [], int $page = 1, int $perPage = 10)
    {
        $query = Article::query()->where('status', 'published');
 
        // Filter by category
        if (!empty($filters['category_id'])) {
            $query->where('category.id', $filters['category_id']);
        }
 
        // Text search
        if (!empty($filters['search'])) {
            $search = $filters['search'];
            $query->where(function($q) use ($search) {
                $q->where('title', 'LIKE', "%{$search}%")
                  ->orWhere('content', 'LIKE', "%{$search}%");
            });
        }
 
        // Filter by date
        if (!empty($filters['date_from'])) {
            $query->where('publishedAt', '>=', new \DateTimeImmutable($filters['date_from']));
        }
 
        // Sorting
        $sortBy = $filters['sort'] ?? 'publishedAt';
        $sortOrder = $filters['order'] ?? 'DESC';
        $query->orderBy($sortBy, $sortOrder);
 
        // Load relations
        $query->load('author')->load('category');
 
        return $query->paginate($perPage, $page);
    }
 
    /**
     * Create article
     */
    public function create(array $data): Article
    {
        $article = new Article();
        $article->title = $data['title'];
        $article->slug = $this->generateSlug($data['title']);
        $article->content = $data['content'];
        $article->excerpt = $data['excerpt'] ?? null;
        $article->status = $data['status'] ?? 'draft';
 
        if (user()->isLoggedIn()) {
            $article->author = user()->getCurrentUser();
        }
 
        if (!empty($data['category_id'])) {
            $article->category = Category::findByPK($data['category_id']);
        }
 
        if ($article->status === 'published') {
            $article->publishedAt = new \DateTimeImmutable();
        }
 
        $article->save();
 
        return $article;
    }
 
    /**
     * Generate unique slug
     */
    protected function generateSlug(string $title): string
    {
        $slug = mb_strtolower(trim($title));
        $slug = preg_replace('/[^a-zа-яё0-9-]/u', '-', $slug);
        $slug = preg_replace('/-+/', '-', $slug);
        $slug = trim($slug, '-');
 
        // Check uniqueness
        $original = $slug;
        $counter = 1;
 
        while (Article::findOne(['slug' => $slug])) {
            $slug = $original . '-' . $counter++;
        }
 
        return $slug;
    }
}

Schema Migrations

The ORM schema is stored in storage/app/orm_schema.php. If you have issues with unrecognized entities, delete this file or call forceRefreshSchema().

Forced schema refresh:


<?php
// Refresh ORM schema
app(\Flute\Core\Database\DatabaseConnection::class)->forceRefreshSchema();

Best Practices

Use Indexes

Create indexes for fields that are frequently searched or sorted.

Load Relations Explicitly

Always use load() for eager loading relations to avoid N+1 problem.

Move Logic to Services

Place complex queries and business logic in service classes.

Use Transactions

Wrap related operations in transactions to ensure data integrity.

Validate Data

Always validate input data before saving to the database.

Performance

In production, keep config('database.debug') disabled — otherwise DB logger slows down queries.

Recommendations:

Use select() to select only needed fields
Apply pagination for large datasets
Cache results of frequently executed queries
Avoid queries inside loops