跳转到内容
XOOPS 2.7 Docs

“标准API参考”

完成 XOOPS 标准查询构建系统的 API 文档。

标准系统架构

Section titled “标准系统架构”
classDiagram
    class CriteriaElement {
        <<abstract>>
        #string $order
        #string $sort
        #int $limit
        #int $start
        +setSort(sort)
        +getSort()
        +setOrder(order)
        +getOrder()
        +setLimit(limit)
        +getLimit()
        +setStart(start)
        +getStart()
        +setGroupby(groupby)
        +getGroupby()
        +render()
        +renderWhere()
        +renderLdap()
    }


    class Criteria {
        -string $prefix
        -string $function
        -string $column
        -string $operator
        -mixed $value
        +__construct(column, value, operator, prefix, function)
        +render()
        +renderWhere()
    }


    class CriteriaCompo {
        -array $criterias
        -array $conditions
        +__construct(criteria)
        +add(criteria, condition)
        +render()
        +renderWhere()
    }


    CriteriaElement <|-- Criteria
    CriteriaElement <|-- CriteriaCompo
    CriteriaCompo o-- CriteriaElement : contains

标准类

Section titled “标准类”

构造函数

Section titled “构造函数”
public function __construct(
    string $column,           // Column name
    mixed $value = '',        // Value to compare
    string $operator = '=',   // Comparison operator
    string $prefix = '',      // Table prefix
    string $function = ''     // SQL function wrapper
)

运算符

Section titled “运算符”
操作员示例SQL输出
=new Criteria('status', 1)status = 1
!=new Criteria('status', 0, '!=')status != 0
<>new Criteria('status', 0, '<>')status <> 0
<new Criteria('age', 18, '<')age < 18
<=new Criteria('age', 18, '<=')age <= 18
>new Criteria('age', 18, '>')age > 18
>=new Criteria('age', 18, '>=')age >= 18
LIKEnew Criteria('title', '%php%', 'LIKE')title LIKE '%php%'
NOT LIKEnew Criteria('title', '%spam%', 'NOT LIKE')title NOT LIKE '%spam%'
INnew Criteria('id', '(1,2,3)', 'IN')id IN (1,2,3)
NOT INnew Criteria('id', '(1,2,3)', 'NOT IN')id NOT IN (1,2,3)
IS NULLnew Criteria('deleted', null, 'IS NULL')deleted IS NULL
IS NOT NULLnew Criteria('email', null, 'IS NOT NULL')email IS NOT NULL
BETWEENnew Criteria('created', '1000 AND 2000', 'BETWEEN')created BETWEEN 1000 AND 2000

用法示例

Section titled “用法示例”
// Simple equality
$criteria = new Criteria('status', 'published');


// Numeric comparison
$criteria = new Criteria('views', 100, '>=');


// Pattern matching
$criteria = new Criteria('title', '%XOOPS%', 'LIKE');


// With table prefix
$criteria = new Criteria('uid', 1, '=', 'u');
// Renders: u.uid = 1


// With SQL function
$criteria = new Criteria('title', '', '!=', '', 'LOWER');
// Renders: LOWER(title) != ''

CriteriaCompo 类

Section titled “CriteriaCompo 类”

构造函数和方法

Section titled “构造函数和方法”
// Create empty compo
$criteria = new CriteriaCompo();


// Or with initial criteria
$criteria = new CriteriaCompo(new Criteria('status', 'active'));


// Add criteria (AND by default)
$criteria->add(new Criteria('views', 10, '>='));


// Add with OR
$criteria->add(new Criteria('featured', 1), 'OR');


// Nesting
$subCriteria = new CriteriaCompo();
$subCriteria->add(new Criteria('author', 1));
$subCriteria->add(new Criteria('author', 2), 'OR');
$criteria->add($subCriteria); // (author = 1 OR author = 2)

排序和分页

Section titled “排序和分页”
$criteria = new CriteriaCompo();
$criteria->add(new Criteria('status', 'published'));


// Single sort
$criteria->setSort('created');
$criteria->setOrder('DESC');


// Multiple sort columns
$criteria->setSort('category_id, created');
$criteria->setOrder('ASC, DESC');


// Pagination
$criteria->setLimit(10);    // Items per page
$criteria->setStart(0);     // Offset (page * limit)


// Group by
$criteria->setGroupby('category_id');

查询构建流程

Section titled “查询构建流程”
flowchart TD
    A[Create CriteriaCompo] --> B[Add Criteria]
    B --> C{More Conditions?}
    C -->|Yes| B
    C -->|No| D[Set Sort/Order]
    D --> E[Set Limit/Start]
    E --> F[Pass to Handler]
    F --> G[Handler calls render]
    G --> H[Generate SQL WHERE]
    H --> I[Execute Query]
    I --> J[Return Results]

复杂查询示例

Section titled “复杂查询示例”

多个条件搜索

Section titled “多个条件搜索”
$criteria = new CriteriaCompo();


// Status must be published
$criteria->add(new Criteria('status', 'published'));


// Category is 1, 2, or 3
$criteria->add(new Criteria('category_id', '(1, 2, 3)', 'IN'));


// Created in last 30 days
$thirtyDaysAgo = time() - (30 * 24 * 60 * 60);
$criteria->add(new Criteria('created', $thirtyDaysAgo, '>='));


// Search term in title OR content
$searchCriteria = new CriteriaCompo();
$searchCriteria->add(new Criteria('title', '%' . $searchTerm . '%', 'LIKE'));
$searchCriteria->add(new Criteria('content', '%' . $searchTerm . '%', 'LIKE'), 'OR');
$criteria->add($searchCriteria);


// Sort by views descending
$criteria->setSort('views');
$criteria->setOrder('DESC');


// Paginate
$criteria->setLimit($perPage);
$criteria->setStart($page * $perPage);


// Execute
$items = $itemHandler->getObjects($criteria);
$total = $itemHandler->getCount($criteria);

日期范围查询

Section titled “日期范围查询”
$criteria = new CriteriaCompo();


// Between two dates
$startDate = strtotime('2024-01-01');
$endDate = strtotime('2024-12-31');


$criteria->add(new Criteria('created', $startDate, '>='));
$criteria->add(new Criteria('created', $endDate, '<='));


// Or using BETWEEN
$criteria->add(new Criteria('created', "$startDate AND $endDate", 'BETWEEN'));

用户权限过滤器

Section titled “用户权限过滤器”
$criteria = new CriteriaCompo();
$criteria->add(new Criteria('status', 'published'));


// If not admin, only show own items or public
if (!$xoopsUser || !$xoopsUser->isAdmin()) {
    $permCriteria = new CriteriaCompo();
    $permCriteria->add(new Criteria('visibility', 'public'));


    if (is_object($xoopsUser)) {
        $permCriteria->add(new Criteria('author_id', $xoopsUser->getVar('uid')), 'OR');
    }


    $criteria->add($permCriteria);
}

###加入-like查询

// Get items where category is active
// (Using subquery approach)
$categoryHandler = xoops_getHandler('category');
$activeCatCriteria = new Criteria('status', 'active');
$activeCategories = $categoryHandler->getIds($activeCatCriteria);


if (!empty($activeCategories)) {
    $criteria->add(new Criteria('category_id', '(' . implode(',', $activeCategories) . ')', 'IN'));
}

SQL 可视化的标准

Section titled “SQL 可视化的标准”
graph LR
    subgraph "PHP Code"
        A["new Criteria('status', 'published')"]
        B["new Criteria('views', 100, '>=')"]
        C["CriteriaCompo with A + B"]
    end


    subgraph "Generated SQL"
        D["status = 'published'"]
        E["views >= 100"]
        F["WHERE status = 'published' AND views >= 100"]
    end


    A --> D
    B --> E
    C --> F

处理程序集成

Section titled “处理程序集成”
// Standard handler methods that accept Criteria


// Get multiple objects
$objects = $handler->getObjects($criteria);
$objects = $handler->getObjects($criteria, true);  // As array
$objects = $handler->getObjects($criteria, true, true); // As array, id as key


// Get count
$count = $handler->getCount($criteria);


// Get list (id => identifier)
$list = $handler->getList($criteria);


// Delete matching
$deleted = $handler->deleteAll($criteria);


// Update matching
$handler->updateAll('status', 'archived', $criteria);

性能考虑因素

Section titled “性能考虑因素”
graph TB
    subgraph "Optimization Tips"
        A[Use Indexes] --> E[Faster Queries]
        B[Limit Results] --> E
        C[Avoid LIKE '%...'] --> E
        D[Use Specific Columns] --> E
    end


    subgraph "Anti-Patterns"
        F[No Indexes] --> G[Slow Queries]
        H[SELECT *] --> G
        I[No LIMIT] --> G
        J[Leading Wildcards] --> G
    end

最佳实践

Section titled “最佳实践”
  1. 对于大表，请始终设置 LIMIT
  2. 对条件中使用的列使用索引
  3. 避免在 LIKE 中使用前导通配符（'%term' 速度很慢）
  4. 当可能存在复杂逻辑时，在 PHP 中的-filter
  5. 谨慎使用 COUNT - 尽可能缓存结果

相关文档

Section titled “相关文档”
  • 数据库层
  • XOOPSObjectHandler API
  • SQL注射预防

#XOOPS #api #criteria #database #query #reference