Search Doctrine Dbal Laravel Package

Getting Started

rollerworks/search-doctrine-dbal bridges the Rollerworks Search component to Doctrine DBAL. It turns a validated, user-facing SearchCriteria object into SQL conditions you can bolt onto a DBAL QueryBuilder.

1. Install (pin the alpha explicitly):

composer require rollerworks/search-doctrine-dbal:2.0.0-alpha3

Also pull the core search component (rollerworks/search) and doctrine/dbal if they aren't already present.

2. Wire a DBAL connection in Laravel. The package speaks DBAL, not Eloquent, so the first decision is how you get a DBAL connection:

use Doctrine\DBAL\DriverManager;

$connection = DriverManager::getConnection([
    'driver'   => 'pdo_mysql',
    'host'     => config('database.connections.mysql.host'),
    'dbname'   => config('database.connections.mysql.database'),
    'user'     => config('database.connections.mysql.username'),
    'password' => config('database.connections.mysql.password'),
]);

For a single first use case, don't build a full search UI. Start with one field set, one endpoint, one condition:

$factory = new SearchFactory();
$factory->registerExtension(new DoctrineDbalExtension());

$fieldSet = $factory->createFieldSetBuilder()
    ->add('name', TextType::class, ['comparison_type' => ValueComparison::CONTAINS])
    ->add('createdAt', DateTimeType::class)
    ->add('id', IntegerType::class)
    ->getFieldSet();

$criteria = $factory->createSearchCriteria($fieldSet);
$criteria->add(Condition::fromValues('name', 'john', $fieldSet->get('name')));

Then compile it and apply it to a DBAL query. Look first at the package README's SearchConditionBuilder example — that class is the entry point, and createConditionFromCriteria() is the method you'll call on every request.

Where to look first: the README, then the DoctrineDbalExtension class, then the ValueMatch implementations under Extension/Doctrine/Dbal/ValueMatch/. That last directory is where the v2.0.0-alpha3 BC break landed (see below).

Implementation Patterns

Search criteria from the request. The intended workflow is: validate query params into SearchCriteria, build a condition, apply it to a query. Keep the criteria construction in a form request or a small DTO so controllers stay thin.

$condition = (new SearchConditionBuilder($fieldFactory, $fieldSet))
    ->createConditionFromCriteria($criteria);

$qb = $connection->createQueryBuilder();
$qb->select('u.*')->from('users', 'u');
$condition->applyTo($qb, 'u'); // alias must match the FROM alias

Reuse the condition across count + page queries. Build the condition once, then apply it to both the SELECT COUNT(*) query and the paginated query so the two can't drift apart:

$countQb = $connection->createQueryBuilder()->select('COUNT(*)')->from('users', 'u');
$condition->applyTo($countQb, 'u');
$total = (int) $countQb->executeQuery()->fetchOne();

$pageQb = $connection->createQueryBuilder()->select('u.*')->from('users', 'u');
$condition->applyTo($pageQb, 'u');
$pageQb->setMaxResults(25)->setFirstResult(($page - 1) * 25);

Wrap it in a repository/service. The package is deliberately low-level; put it behind something like UserSearchRepository::search(SearchCriteria $criteria, int $limit, int $offset): Paginator. That isolates your app from alpha API churn — which matters a lot right now (see Gotchas).

Map the field set to something declarative. Rather than constructing a FieldSetBuilder per controller, define field sets in a config array or a dedicated registry class so the API contract (which fields a client may filter on) lives in one place.

Test at the SQL level. Because the output is a query, assert on $qb->getSQL() and $qb->getParameters() rather than round-tripping through the DB. This is fast and catches alias and parameter-binding mistakes.

Mix with Eloquent carefully. If the rest of the app is Eloquent, the cleanest integration is: build the condition, apply it to a DBAL query builder, run it, and hydrate Eloquent models from the resulting rows. Don't try to splice DBAL conditions into an Eloquent builder.

Gotchas and Tips

The BC break in v2.0.0-alpha3 — Regex ValueMatch is gone. RegexValueMatch (matching a DB column against a regular expression) has been removed from the package (#50). Consequences:

• Any field set using regex value matching will now fail at condition-compilation time. Audit your comparison_type / value-match configuration before bumping.
• Regex matching was already non-portable (MySQL REGEXP vs Postgres ~ vs SQLite's lack of a built-in operator), so removal is defensible — but it means there is no drop-in replacement. Your options:
  1. Convert to ValueComparison::CONTAINS / LIKE where the pattern is really a substring search (LIKE '%foo%').
  2. Pre-filter in PHP and pass the matching IDs in as an IN (…) condition — fine for small result sets, a trap for large ones.
  3. Implement your own ValueMatch and register it via the extension point (this is the intended escape hatch). Keep it driver-specific and document the assumption, because applyTo() will emit raw SQL.
  4. Stay on the latest 1.x if regex search is load-bearing and you can't migrate yet.

This is an alpha. 2.0.0-alpha3 means the API can break again without a major version bump. Pin the exact version in composer.json (not ^2.0@alpha), and keep the adapter/service wrapper described above so an upgrade is a one-file change. Don't ship it on a project with no test coverage around search.

Aliases are silent killers. applyTo($qb, 'u') expects u to be the alias declared in from('users', 'u'). Get it wrong and you'll get an "unknown column" error that looks like a schema problem.

LIKE wildcards are yours to escape. If a user types 100% into a contains filter, the % is interpreted as a wildcard unless you escape it. Escape % and _ in the criteria values before compiling, or you'll get surprising supersets of results.

Case sensitivity varies by driver. Contains/equals comparisons inherit the database's collation. MySQL is usually case-insensitive; Postgres is case-sensitive and needs ILIKE or LOWER() — which the package won't do for you. Test against your real production driver, not SQLite.

Parameters are bound, but only for values you pass through the component. If you hand-roll a ValueMatch or append raw SQL fragments, you're back on injection duty. Never interpolate user input into applyTo().

Empty criteria produce empty conditions, not "no rows." Confirm what your app should do when a user submits a blank search — usually you want to skip the condition entirely rather than apply a condition that matches everything.

Debugging: dump the criteria with var_dump($criteria) and the compiled SQL with $qb->getSQL() plus $qb->getParameters(). The DoctrineDbalExtension must be registered on the SearchFactory before you create the field set, or value matches won't resolve and you'll get confusing "unsupported comparison" errors.

Extension points worth knowing: register additional ValueMatch implementations through the extension to add driver-specific behaviour (that's the replacement path for regex), and keep field sets in a registry class so clients can't filter on columns you haven't explicitly exposed.