Plugin Database Query Builder

The Plugin Database Query Builder allows you to safely query your plugin databases directly from Twig templates. This powerful feature enables you to create dynamic content by fetching data from your game servers' databases.

Getting Your Plugin ID

1. Go to our dashboard (https://gamecms.app)
2. Navigate to Integrations -> (Your Game)
3. Find the plugin you want and copy the id.

Basic Usage

Simple Query

{# Get the top 10 players by score #}
{% set topPlayers = pluginDatabaseQuery(833)
    .from('players')
    .select(['name', 'score', 'rank'])
    .where('active', '=', 1)
    .orderBy('score', 'DESC')
    .limit(10)
    .get() %}

{# Display the results #}
<div class="top-players">
    {% for player in topPlayers %}
        <div class="player">
            <h3>{{ player.name }}</h3>
            <p>Score: {{ player.score }} | Rank: {{ player.rank }}</p>
        </div>
    {% endfor %}
</div>

Available Methods

Method	Description	Example
.from(table)	Specify the table to query	.from('players')
.select([columns])	Choose specific columns	.select(['name', 'score'])
.where(column, operator, value)	Add conditions	.where('active', '=', 1)
.whereIn(column, values)	Match multiple values	.whereIn('id', [1, 2, 3])
.orderBy(column, direction)	Sort results	.orderBy('score', 'DESC')
.limit(number)	Limit number of results	.limit(50)
.offset(number)	Skip results for pagination	.offset(10)
.get()	Execute and get all results	.get()
.first()	Get only the first result	.first()
.count()	Count matching records	.count()

Working with Relationships (with)

The with() method is the most powerful feature - it allows you to load related data from other tables without writing complex joins.

Basic Relationship Loading

{# Load chat logs with user information #}
{% set chatLogs = pluginDatabaseQuery(833)
    .from('chat_logs')
    .with('users', 'steam_id', '=', 'chat_logs.steam_id', null, 'single')
    .orderBy('timestamp', 'DESC')
    .limit(20)
    .get() %}

{# Access the related data #}
{% for message in chatLogs %}
    <div class="chat-message">
        <strong>{{ message.users ? message.users.name : 'Unknown' }}:</strong>
        {{ message.message }}
        <small>{{ message.timestamp|date('Y-m-d H:i') }}</small>
    </div>
{% endfor %}

with() Method Signature

.with(table, column, operator, value, limit, type)

Parameters:

• table (string): The related table name
• column (string): Column in the related table
• operator (string): Comparison operator (usually '=')
• value (string): Column from main table (e.g., 'chat_logs.steam_id')
• limit (int|null): Maximum records to load (optional)
• type (string): 'single' or 'multiple'

Same Database Requirement

Important: The with() method can only load relationships from tables within the same database as your main query. You cannot cross-reference tables from different plugin databases or servers in a single query operation.

For example, if your main query uses plugin ID 833 (which connects to a specific database), all tables referenced in with() methods must exist in that same database.

Relationship Types

Single Relationship (One-to-One)

Use 'single' when each main record relates to one record in the related table.

{# Each player has one rank #}
{% set players = pluginDatabaseQuery(833)
    .from('players')
    .with('player_ranks', 'player_id', '=', 'players.id', null, 'single')
    .limit(10)
    .get() %}

{% for player in players %}
    <div class="player-card">
        <h3>{{ player.name }}</h3>
        <p>Rank: {{ player.player_ranks ? player.player_ranks.rank_name : 'Unranked' }}</p>
    </div>
{% endfor %}

Multiple Relationship (One-to-Many)

Use 'multiple' when each main record can relate to many records in the related table.

{# Each player has multiple weapons #}
{% set players = pluginDatabaseQuery(833)
    .from('players')
    .with('player_weapons', 'player_id', '=', 'players.id', null, 'multiple')
    .limit(5)
    .get() %}

{% for player in players %}
    <div class="player-weapons">
        <h3>{{ player.name }}</h3>
        <h4>Weapons:</h4>
        <ul>
            {% for weapon in player.player_weapons %}
                <li>{{ weapon.weapon_name }}: {{ weapon.kills }} kills</li>
            {% endfor %}
        </ul>
    </div>
{% endfor %}

Using Limits with Relationships

Why Use Limits?

Limits prevent loading too much data and improve page performance:

{# Without limit - could load thousands of records! #}
.with('player_weapons', 'player_id', '=', 'players.id', null, 'multiple')

{# With limit - only top 5 weapons per player #}
.with('player_weapons', 'player_id', '=', 'players.id', 5, 'multiple')

Practical Examples

Top Weapons Per Player

{% set topPlayers = pluginDatabaseQuery(833)
    .from('players')
    .with('player_weapons', 'player_id', '=', 'players.id', 3, 'multiple')
    .orderBy('score', 'DESC')
    .limit(10)
    .get() %}

<div class="leaderboard">
    {% for player in topPlayers %}
        <div class="player-stats">
            <h3>{{ player.name }} ({{ player.score }} points)</h3>
            <p><strong>Top 3 Weapons:</strong></p>
            {% for weapon in player.player_weapons %}
                <span class="weapon-badge">{{ weapon.weapon_name }} ({{ weapon.kills }})</span>
            {% endfor %}
        </div>
    {% endfor %}
</div>

Recent Activity with User Info

{% set recentActivity = pluginDatabaseQuery(833)
    .from('activity_log')
    .with('users', 'steam_id', '=', 'activity_log.steam_id', null, 'single')
    .with('recent_actions', 'player_id', '=', 'activity_log.player_id', 5, 'multiple')
    .where('server_id', '=', 1)
    .orderBy('timestamp', 'DESC')
    .limit(20)
    .get() %}

<div class="activity-feed">
    {% for activity in recentActivity %}
        <div class="activity-item">
            <div class="user-info">
                {% if activity.users %}
                    <img src="{{ activity.users.avatar }}" alt="Avatar">
                    <strong>{{ activity.users.name }}</strong>
                {% else %}
                    <strong>Unknown Player</strong>
                {% endif %}
            </div>
            <div class="activity-details">
                <p>{{ activity.action }} - {{ activity.timestamp|date('H:i') }}</p>
                {% if activity.recent_actions %}
                    <details>
                        <summary>Recent Actions ({{ activity.recent_actions|length }})</summary>
                        {% for action in activity.recent_actions %}
                            <small>{{ action.action_type }} - {{ action.created_at|date('m/d H:i') }}</small><br>
                        {% endfor %}
                    </details>
                {% endif %}
            </div>
        </div>
    {% endfor %}
</div>

Multiple Relationships

You can load multiple relationships in a single query:

{% set matchData = pluginDatabaseQuery(833)
    .from('matches')
    .with('match_players', 'match_id', '=', 'matches.id', 10, 'multiple')
    .with('match_rounds', 'match_id', '=', 'matches.id', 5, 'multiple')
    .with('map_info', 'map_id', '=', 'matches.map_id', null, 'single')
    .where('status', '=', 'completed')
    .orderBy('end_time', 'DESC')
    .limit(3)
    .get() %}

<div class="match-history">
    {% for match in matchData %}
        <div class="match-card">
            <h3>{{ match.map_info ? match.map_info.name : 'Unknown Map' }}</h3>
            
            <div class="players">
                <h4>Players (Top 10):</h4>
                <div class="player-grid">
                    {% for player in match.match_players %}
                        <span class="player-tag">{{ player.name }} ({{ player.score }})</span>
                    {% endfor %}
                </div>
            </div>
            
            <div class="rounds">
                <h4>Recent Rounds:</h4>
                {% for round in match.match_rounds %}
                    <p>Round {{ round.round_number }}: {{ round.winner }} won</p>
                {% endfor %}
            </div>
        </div>
    {% endfor %}
</div>

Common Patterns

Leaderboard with Stats

{% set leaderboard = pluginDatabaseQuery(833)
    .from('players')
    .with('player_stats', 'player_id', '=', 'players.id', null, 'single')
    .with('recent_matches', 'player_id', '=', 'players.id', 3, 'multiple')
    .where('active', '=', 1)
    .orderBy('score', 'DESC')
    .limit(20)
    .get() %}

<div class="leaderboard">
    {% for player in leaderboard %}
        <div class="player-row">
            <div class="player-info">
                <h4>{{ player.name }}</h4>
                <p>Score: {{ player.score }}</p>
            </div>
            <div class="player-stats">
                {% if player.player_stats %}
                    <span>K/D: {{ player.player_stats.kills }}/{{ player.player_stats.deaths }}</span>
                    <span>Accuracy: {{ player.player_stats.accuracy }}%</span>
                {% endif %}
            </div>
            <div class="recent-activity">
                <small>Recent matches:</small>
                {% for match in player.recent_matches %}
                    <small>{{ match.map_name }} ({{ match.result }})</small>
                {% endfor %}
            </div>
        </div>
    {% endfor %}
</div>

Server Activity Feed

{% set activity = pluginDatabaseQuery(833)
    .from('server_events')
    .with('users', 'steam_id', '=', 'server_events.steam_id', null, 'single')
    .where('event_type', 'IN', ['kill', 'death', 'disconnect'])
    .orderBy('timestamp', 'DESC')
    .limit(30)
    .get() %}

<div class="activity-feed">
    {% for event in activity %}
        <div class="event-item event-{{ event.event_type }}">
            <span class="timestamp">{{ event.timestamp|date('H:i:s') }}</span>
            <span class="player">{{ event.users ? event.users.name : 'Unknown' }}</span>
            <span class="action">{{ event.event_type }}</span>
            {% if event.details %}
                <span class="details">{{ event.details }}</span>
            {% endif %}
        </div>
    {% endfor %}
</div>

Troubleshooting

Plugin ID Not Found: Check your plugin ID in the dashboard

Empty Results: Verify table and column names are correct

Relationship Data Missing: Make sure the relationship columns exist in both tables