如何轻松构建动态SQL查询？EveronCriteriaBuilder帮你告别拼接噩梦

<code class="bash">composer require everon/criteria-builder</code>

<code class="php">use Everon\Component\CriteriaBuilder\CriteriaBuilderFactoryWorker;
use Everon\Component\Factory\Dependency\Container;
use Everon\Component\Factory\Factory;

// 初始化 CriteriaBuilder，这部分通常在框架的DI容器中完成
$Container = new Container();
$Factory = new Factory($Container);
$Factory->registerWorkerCallback('CriteriaBuilderFactoryWorker', function() use ($Factory) {
    return $Factory->buildWorker(CriteriaBuilderFactoryWorker::class);
});
$CriteriaBuilderFactoryWorker = $Factory->getWorkerByName('CriteriaBuilderFactoryWorker');
$CriteriaBuilder = $CriteriaBuilderFactoryWorker->buildCriteriaBuilder();

// 想象一个用户列表筛选：查找状态为 'active' 且创建日期在 '2023-01-01' 之后的记录
$CriteriaBuilder
    ->where('status', '=', 'active')
    ->andWhere('created_at', '>', '2023-01-01');

// 输出的 SQL 片段（简化）：
// WHERE (status = :status_xxx AND created_at > :created_at_yyy)</code>

<code class="php">$CriteriaBuilder = $CriteriaBuilderFactoryWorker->buildCriteriaBuilder(); // 重新初始化以展示新例子

$CriteriaBuilder
    ->where('id', 'IN', [1, 2, 3]) // 第一个条件组开始
        ->orWhere('id', 'NOT IN', [4, 5, 6])
        ->andWhere('name', '=', 'foo')
    ->glueByOr() // 使用 OR 连接上一个条件组和下一个条件组
    ->where('modified', 'IS', null) // 第二个条件组开始
        ->andWhere('name', '!=', null)
        ->orWhere('age', '=', 55);

// 自动生成带括号的复杂逻辑，例如：
// WHERE (id IN (:id_xxx,:id_yyy,:id_zzz) OR id NOT IN (:id_aaa,:id_bbb,:id_ccc) AND name = :name_foo)
// OR (modified IS NULL AND name IS NOT NULL OR age = :age_55)</code>

<code class="php">// 获取SQL和参数，直接用于PDO
$sqlPart = $CriteriaBuilder->toSqlPart();
$fullSql = 'SELECT * FROM users ' . (string) $sqlPart; // 将构建的WHERE子句添加到你的主SQL
$parameters = $sqlPart->getParameters();

// 假设 $dbh 是你的 PDO 实例
$sth = $dbh->prepare($fullSql);
$sth->execute($parameters);
$data = $sth->fetchAll(PDO::FETCH_ASSOC);

// $fullSql 可能是：SELECT * FROM users WHERE (status = :status_xxx AND created_at > :created_at_yyy)
// $parameters 可能是：['status_xxx' => 'active', 'created_at_yyy' => '2023-01-01']</code>

<code class="php">$CriteriaBuilder = $CriteriaBuilderFactoryWorker->buildCriteriaBuilder(); // 重新初始化

$CriteriaBuilder
    ->where('category_id', '=', 10)
    ->setLimit(10) // 每页10条
    ->setOffset(20) // 从第21条开始（第3页）
    ->setOrderBy([
        'name' => 'DESC', // 按名称降序
        'id' => 'ASC'     // 再按ID升序
    ]);

// 输出的 SQL 片段（简化）：
// WHERE (category_id = :category_id_xxx) ORDER BY name DESC,id ASC LIMIT 10 OFFSET 20</code>

<code class="php">$CriteriaBuilder = $CriteriaBuilderFactoryWorker->buildCriteriaBuilder(); // 重新初始化

$CriteriaBuilder
    ->whereRaw('LENGTH(description) > 500') // 插入原生SQL函数
    ->andWhere('is_published', '=', true);

// 输出的 SQL 片段（简化）：
// WHERE (LENGTH(description) > 500 AND is_published = :is_published_xxx)</code>