✨ Introduce More Specific Query Helper Methods in `DatabaseMethods`

[David-2357]

The current database abstraction layer would benefit from more specific and intention-revealing helper methods for common query patterns. This would improve readability, reduce boilerplate, and make data access code easier to maintain.

A proposed example:

DatabaseMethods::selectWhere(
    'users',
    ['id', 'name'],
    ['id' => $userId]
);
// Expected SQL: SELECT id, name FROM users WHERE id = :id

Instead of relying on generic methods for every query, we should provide a richer set of focused helpers that cover the most frequent use cases.

---

Proposed Methods

1. selectWhere()

Fetch specific columns with simple equality filters.

DatabaseMethods::selectWhere(
    string $table,
    array $columns,
    array $conditions
): array

Example

DatabaseMethods::selectWhere(
    'users',
    ['id', 'name'],
    ['id' => $userId]
);

Generated SQL

SELECT id, name
FROM users
WHERE id = :id

---

2. selectOneWhere()

Same as selectWhere() but returns a single row.

DatabaseMethods::selectOneWhere(
    'users',
    ['id', 'email'],
    ['id' => $userId]
);

Generated SQL

SELECT id, email
FROM users
WHERE id = :id
LIMIT 1

---

3. existsWhere()

Check if a record exists without fetching full data.

DatabaseMethods::existsWhere(
    'users',
    ['email' => $email]
);

Generated SQL

SELECT 1
FROM users
WHERE email = :email
LIMIT 1

Why useful

• Better performance than full SELECT *
• Common validation use case
• Clear intent

---

4. countWhere()

Count records matching conditions.

DatabaseMethods::countWhere(
    'orders',
    ['status' => 'pending']
);

Generated SQL

SELECT COUNT(*) as total
FROM orders
WHERE status = :status

---

5. updateWhere()

Update rows using explicit conditions.

DatabaseMethods::updateWhere(
    'users',
    ['last_login' => $timestamp],
    ['id' => $userId]
);

Generated SQL

UPDATE users
SET last_login = :last_login
WHERE id = :id

---

6. deleteWhere()

Delete rows with safe condition handling.

DatabaseMethods::deleteWhere(
    'sessions',
    ['user_id' => $userId]
);

Generated SQL

DELETE FROM sessions
WHERE user_id = :user_id

---

7. selectWhereIn()

Useful for batch retrieval.

DatabaseMethods::selectWhereIn(
    'users',
    ['id', 'name'],
    'id',
    $userIds
);

Generated SQL

SELECT id, name
FROM users
WHERE id IN (:id_1, :id_2, :id_3)

---

8. selectOrderedWhere()

Frequently needed for sorted results.

DatabaseMethods::selectOrderedWhere(
    'posts',
    ['id', 'title'],
    ['published' => true],
    ['created_at' => 'DESC']
);

---

9. paginateWhere()

Native pagination support.

DatabaseMethods::paginateWhere(
    'products',
    ['id', 'name'],
    ['active' => 1],
    20,
    0
);

Generated SQL

SELECT id, name
FROM products
WHERE active = :active
LIMIT 20 OFFSET 0

Benefits

• Better developer experience
• More expressive API
• Reduced duplicated SQL logic
• Safer parameter binding
• Easier maintenance
• More discoverable method usage

---

Considerations

• Ensure all methods use prepared statements
• Validate table/column names to prevent SQL injection in identifiers
• Avoid allowing deleteWhere() or updateWhere() without conditions
• Standardize return types (array, bool, int, null)