Запросы и операторы

Сложные WHERE условия

Комбинированные условия

<!-- Сложные AND условия -->
{% set premiumUsers = db('1752665380840')->getItems('users', {
    'balance': '>1000',
    'verified': 1,
    'ban': 0,
    'last_seen': '>' ~ (time() - 86400 * 7),  # Активен за неделю
    'created_at': '>' ~ (time() - 86400 * 30)  # Регистрация меньше месяца назад
}) %}

<!-- OR условия с implode -->
{% set moderators = db('1752665380840')->getItems('users', {
    'role': 'admin',
    'role ': 'moderator',
    'role  ': 'supervisor',
    'implode': 'OR'
}) %}

Диапазоны и интервалы

<!-- Числовые диапазоны -->
{% set middleClassUsers = db('1752665380840')->getItems('users', {
    'balance': '>=100',
    'balance ': '<=10000'
}) %}

<!-- Временные интервалы -->
{% set thisMonthUsers = db('1752665380840')->getItems('users', {
    'created_at': '>=' ~ strtotime('first day of this month'),
    'created_at ': '<' ~ strtotime('first day of next month')
}) %}

<!-- Возрастные группы -->
{% set adultsUsers = db('1752665380840')->getItems('users', {
    'age': '>=18',
    'age ': '<65'
}) %}

NULL и пустые значения

<!-- Поиск NULL значений -->
{% set usersWithoutAvatar = db('1752665380840')->getItems('users', {
    'avatar': 'IS NULL'
}) %}

<!-- Поиск НЕ NULL значений -->
{% set usersWithAvatar = db('1752665380840')->getItems('users', {
    'avatar': 'IS NOT NULL'
}) %}

<!-- Поиск пустых строк -->
{% set usersWithoutBio = db('1752665380840')->getItems('users', {
    'bio': ''
}) %}

Прямые SQL запросы

Простые SELECT запросы

<!-- Базовый SELECT -->
{% set users = db('1752665380840')->query('SELECT * FROM users WHERE active = 1') %}

<!-- С параметрами (безопасно) -->
{% set users = db('1752665380840')->query('
    SELECT id, login, balance 
    FROM users 
    WHERE balance > ? AND role = ?
', [1000, 'user']) %}

<!-- Подсчет с группировкой -->
{% set roleStats = db('1752665380840')->query('
    SELECT role, COUNT(*) as count, AVG(balance) as avg_balance
    FROM users 
    GROUP BY role 
    ORDER BY count DESC
') %}

Сложные аналитические запросы

<!-- Топ пользователей по балансу -->
{% set topUsers = db('1752665380840')->query('
    SELECT 
        id,
        login,
        balance,
        RANK() OVER (ORDER BY balance DESC) as rank
    FROM users 
    WHERE ban = 0 
    ORDER BY balance DESC 
    LIMIT 10
') %}

<!-- Статистика по месяцам -->
{% set monthlyStats = db('1752665380840')->query('
    SELECT 
        DATE_FORMAT(FROM_UNIXTIME(created_at), "%Y-%m") as month,
        COUNT(*) as registrations,
        AVG(balance) as avg_balance,
        MAX(balance) as max_balance
    FROM users 
    WHERE created_at >= ?
    GROUP BY month
    ORDER BY month DESC
', [strtotime('-12 months')]) %}

<!-- Активность пользователей -->
{% set activityStats = db('1752665380840')->query('
    SELECT 
        CASE 
            WHEN last_seen > ? THEN "Онлайн"
            WHEN last_seen > ? THEN "Сегодня" 
            WHEN last_seen > ? THEN "На этой неделе"
            ELSE "Давно"
        END as activity_group,
        COUNT(*) as count
    FROM users 
    WHERE ban = 0
    GROUP BY activity_group
    ORDER BY 
        CASE activity_group
            WHEN "Онлайн" THEN 1
            WHEN "Сегодня" THEN 2  
            WHEN "На этой неделе" THEN 3
            ELSE 4
        END
', [
    time() - 300,      # 5 минут назад
    time() - 86400,    # Сегодня
    time() - 604800    # Неделя назад
]) %}

JOIN запросы

<!-- Простой JOIN -->
{% set usersWithProfiles = db('1752665380840')->query('
    SELECT 
        u.id,
        u.login,
        u.balance,
        p.avatar,
        p.bio,
        p.website
    FROM users u
    LEFT JOIN user_profiles p ON u.id = p.user_id
    WHERE u.ban = 0
    ORDER BY u.created_at DESC
    LIMIT 50
') %}

<!-- Множественные JOIN -->
{% set ordersWithDetails = db('1752665380840')->query('
    SELECT 
        o.id,
        o.total,
        o.status,
        o.created_at,
        u.login as customer_login,
        u.email as customer_email,
        COUNT(oi.id) as items_count,
        SUM(oi.quantity) as total_quantity
    FROM orders o
    LEFT JOIN users u ON o.user_id = u.id
    LEFT JOIN order_items oi ON o.id = oi.order_id
    WHERE o.created_at >= ?
    GROUP BY o.id
    ORDER BY o.created_at DESC
', [strtotime('-30 days')]) %}

Подзапросы

Коррелированные подзапросы

<!-- Пользователи с количеством заказов -->
{% set usersWithOrderCount = db('1752665380840')->query('
    SELECT 
        u.id,
        u.login,
        u.balance,
        (
            SELECT COUNT(*) 
            FROM orders o 
            WHERE o.user_id = u.id AND o.status = "completed"
        ) as completed_orders,
        (
            SELECT COALESCE(SUM(total), 0) 
            FROM orders o 
            WHERE o.user_id = u.id AND o.status = "completed"
        ) as total_spent
    FROM users u
    WHERE u.ban = 0
    HAVING completed_orders > 0
    ORDER BY total_spent DESC
') %}

<!-- Товары с последним заказом -->
{% set productsWithLastOrder = db('1752665380840')->query('
    SELECT 
        p.id,
        p.name,
        p.price,
        (
            SELECT MAX(o.created_at) 
            FROM orders o
            JOIN order_items oi ON o.id = oi.order_id
            WHERE oi.product_id = p.id
        ) as last_ordered
    FROM products p
    WHERE p.active = 1
    ORDER BY last_ordered DESC
') %}

EXISTS и NOT EXISTS

<!-- Пользователи с заказами -->
{% set usersWithOrders = db('1752665380840')->query('
    SELECT u.id, u.login, u.email
    FROM users u
    WHERE EXISTS (
        SELECT 1 
        FROM orders o 
        WHERE o.user_id = u.id AND o.status = "completed"
    )
    ORDER BY u.login
') %}

<!-- Пользователи без заказов -->
{% set usersWithoutOrders = db('1752665380840')->query('
    SELECT u.id, u.login, u.created_at
    FROM users u
    WHERE NOT EXISTS (
        SELECT 1 
        FROM orders o 
        WHERE o.user_id = u.id
    )
    AND u.created_at < ?
    ORDER BY u.created_at
', [time() - 86400 * 30]) %}  # Регистрация больше месяца назад

Агрегационные функции

Группировка и статистика

<!-- Статистика по ролям -->
{% set roleStats = db('1752665380840')->query('
    SELECT 
        role,
        COUNT(*) as count,
        AVG(balance) as avg_balance,
        MIN(balance) as min_balance,
        MAX(balance) as max_balance,
        SUM(balance) as total_balance,
        STDDEV(balance) as balance_stddev
    FROM users 
    WHERE ban = 0
    GROUP BY role
    HAVING count >= 5
    ORDER BY avg_balance DESC
') %}

<!-- Активность по дням недели -->
{% set weeklyActivity = db('1752665380840')->query('
    SELECT 
        DAYOFWEEK(FROM_UNIXTIME(last_seen)) as day_of_week,
        CASE DAYOFWEEK(FROM_UNIXTIME(last_seen))
            WHEN 1 THEN "Воскресенье"
            WHEN 2 THEN "Понедельник" 
            WHEN 3 THEN "Вторник"
            WHEN 4 THEN "Среда"
            WHEN 5 THEN "Четверг"
            WHEN 6 THEN "Пятница"
            WHEN 7 THEN "Суббота"
        END as day_name,
        COUNT(*) as active_users
    FROM users 
    WHERE last_seen >= ? AND ban = 0
    GROUP BY day_of_week, day_name
    ORDER BY day_of_week
', [time() - 604800]) %}  # За последнюю неделю

Оконные функции (для MySQL 8.0+)

<!-- Ранжирование пользователей -->
{% set rankedUsers = db('1752665380840')->query('
    SELECT 
        id,
        login,
        balance,
        ROW_NUMBER() OVER (ORDER BY balance DESC) as row_num,
        RANK() OVER (ORDER BY balance DESC) as rank,
        DENSE_RANK() OVER (ORDER BY balance DESC) as dense_rank,
        PERCENT_RANK() OVER (ORDER BY balance DESC) as percentile
    FROM users 
    WHERE ban = 0 AND balance > 0
    ORDER BY balance DESC
    LIMIT 20
') %}

<!-- Прирост по месяцам -->
{% set monthlyGrowth = db('1752665380840')->query('
    SELECT 
        month,
        registrations,
        LAG(registrations) OVER (ORDER BY month) as prev_month,
        registrations - LAG(registrations) OVER (ORDER BY month) as growth,
        ROUND(
            (registrations - LAG(registrations) OVER (ORDER BY month)) * 100.0 
            / LAG(registrations) OVER (ORDER BY month), 2
        ) as growth_percent
    FROM (
        SELECT 
            DATE_FORMAT(FROM_UNIXTIME(created_at), "%Y-%m") as month,
            COUNT(*) as registrations
        FROM users 
        WHERE created_at >= ?
        GROUP BY month
    ) monthly_data
    ORDER BY month
', [strtotime('-12 months')]) %}

Полнотекстовый поиск

FULLTEXT поиск (MySQL)

<!-- Поиск по контенту -->
{% set searchResults = db('1752665380840')->query('
    SELECT 
        id,
        title,
        content,
        MATCH(title, content) AGAINST(? IN BOOLEAN MODE) as relevance
    FROM articles 
    WHERE MATCH(title, content) AGAINST(? IN BOOLEAN MODE)
    AND published = 1
    ORDER BY relevance DESC
    LIMIT 20
', [searchQuery, searchQuery]) %}

<!-- Поиск с модификаторами -->
{% set advancedSearch = db('1752665380840')->query('
    SELECT 
        id,
        title,
        content,
        MATCH(title, content) AGAINST(? IN BOOLEAN MODE) as score
    FROM articles 
    WHERE MATCH(title, content) AGAINST(? IN BOOLEAN MODE)
    ORDER BY score DESC
', [
    '+' ~ searchTerm ~ '* -excluded',  # Обязательно содержит, исключая слово
    '+' ~ searchTerm ~ '* -excluded'
]) %}

Поиск LIKE для простых случаев

<!-- Множественный поиск -->
{% set userSearch = db('1752665380840')->query('
    SELECT id, login, email, first_name, last_name
    FROM users 
    WHERE (
        login LIKE ? 
        OR email LIKE ? 
        OR CONCAT(first_name, " ", last_name) LIKE ?
        OR first_name LIKE ?
        OR last_name LIKE ?
    )
    AND ban = 0
    ORDER BY 
        CASE 
            WHEN login = ? THEN 1
            WHEN login LIKE ? THEN 2
            WHEN email LIKE ? THEN 3
            ELSE 4
        END,
        login
    LIMIT 50
', [
    '%' ~ searchTerm ~ '%',  # login LIKE
    '%' ~ searchTerm ~ '%',  # email LIKE  
    '%' ~ searchTerm ~ '%',  # full name LIKE
    '%' ~ searchTerm ~ '%',  # first_name LIKE
    '%' ~ searchTerm ~ '%',  # last_name LIKE
    searchTerm,              # exact login match
    searchTerm ~ '%',        # login starts with
    '%' ~ searchTerm ~ '%'   # email contains
]) %}

Операции изменения данных

INSERT запросы

<!-- Простая вставка -->
{% php %}
    $newUserId = db('1752665380840')->query('
        INSERT INTO users (login, email, password, created_at) 
        VALUES (?, ?, ?, ?)
    ', [
        $username, 
        $email, 
        password_hash($password, PASSWORD_DEFAULT), 
        time()
    ]);
{% endphp %}

<!-- Множественная вставка -->
{% php %}
    db('1752665380840')->query('
        INSERT INTO user_sessions (user_id, token, ip_address, created_at) 
        VALUES 
            (?, ?, ?, ?),
            (?, ?, ?, ?),
            (?, ?, ?, ?)
    ', [
        $userId1, $token1, $ip1, time(),
        $userId2, $token2, $ip2, time(), 
        $userId3, $token3, $ip3, time()
    ]);
{% endphp %}

<!-- INSERT ... SELECT -->
{% php %}
    db('1752665380840')->query('
        INSERT INTO user_backup (user_id, login, email, backup_date)
        SELECT id, login, email, ?
        FROM users 
        WHERE last_seen < ? AND ban = 0
    ', [time(), time() - 86400 * 365]);  # Год назад
{% endphp %}

UPDATE запросы

<!-- Простое обновление -->
{% php %}
    db('1752665380840')->query('
        UPDATE users 
        SET last_seen = ?, ip_address = ?
        WHERE id = ?
    ', [time(), $userIP, $userId]);
{% endphp %}

<!-- Условное обновление -->
{% php %}
    db('1752665380840')->query('
        UPDATE users 
        SET 
            balance = balance + ?,
            updated_at = ?
        WHERE id = ? AND ban = 0
    ', [$bonusAmount, time(), $userId]);
{% endphp %}

<!-- Обновление с подзапросом -->
{% php %}
    db('1752665380840')->query('
        UPDATE users 
        SET orders_count = (
            SELECT COUNT(*) 
            FROM orders 
            WHERE user_id = users.id AND status = "completed"
        )
        WHERE id IN (?)
    ', [implode(',', $userIds)]);
{% endphp %}

Практические примеры запросов

Отчет по продажам

<!-- Детальный отчет продаж -->
{% set salesReport = db('1752665380840')->query('
    SELECT 
        DATE(FROM_UNIXTIME(o.created_at)) as sale_date,
        COUNT(DISTINCT o.id) as orders_count,
        COUNT(oi.id) as items_sold,
        SUM(oi.quantity) as total_quantity,
        SUM(oi.price * oi.quantity) as gross_revenue,
        AVG(o.total) as avg_order_value,
        COUNT(DISTINCT o.user_id) as unique_customers
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    WHERE o.status = "completed" 
        AND o.created_at BETWEEN ? AND ?
    GROUP BY sale_date
    ORDER BY sale_date DESC
', [
    strtotime($startDate),
    strtotime($endDate . ' 23:59:59')
]) %}

<!-- Топ продуктов -->
{% set topProducts = db('1752665380840')->query('
    SELECT 
        p.id,
        p.name,
        p.price,
        SUM(oi.quantity) as total_sold,
        SUM(oi.price * oi.quantity) as revenue,
        COUNT(DISTINCT oi.order_id) as orders_count,
        AVG(oi.price) as avg_sell_price,
        ROUND(SUM(oi.quantity * oi.price) / SUM(oi.quantity), 2) as weighted_avg_price
    FROM products p
    JOIN order_items oi ON p.id = oi.product_id
    JOIN orders o ON oi.order_id = o.id
    WHERE o.status = "completed" 
        AND o.created_at >= ?
    GROUP BY p.id
    HAVING total_sold >= 10
    ORDER BY revenue DESC
    LIMIT 20
', [strtotime('-30 days')]) %}

Анализ пользователей

<!-- Сегментация пользователей -->
{% set userSegments = db('1752665380840')->query('
    SELECT 
        segment,
        COUNT(*) as users_count,
        AVG(total_spent) as avg_spent,
        AVG(orders_count) as avg_orders
    FROM (
        SELECT 
            u.id,
            u.login,
            COALESCE(user_stats.total_spent, 0) as total_spent,
            COALESCE(user_stats.orders_count, 0) as orders_count,
            CASE 
                WHEN COALESCE(user_stats.total_spent, 0) = 0 THEN "Не покупал"
                WHEN user_stats.total_spent < 1000 THEN "Новичок"
                WHEN user_stats.total_spent < 5000 THEN "Постоянный"
                WHEN user_stats.total_spent < 20000 THEN "VIP"
                ELSE "Премиум"
            END as segment
        FROM users u
        LEFT JOIN (
            SELECT 
                user_id,
                SUM(total) as total_spent,
                COUNT(*) as orders_count
            FROM orders 
            WHERE status = "completed"
            GROUP BY user_id
        ) user_stats ON u.id = user_stats.user_id
        WHERE u.ban = 0
    ) segmented_users
    GROUP BY segment
    ORDER BY 
        CASE segment
            WHEN "Премиум" THEN 1
            WHEN "VIP" THEN 2
            WHEN "Постоянный" THEN 3  
            WHEN "Новичок" THEN 4
            ELSE 5
        END
') %}