🔍 DB Search Bundle

📌 Overview

The DB Search Bundle helps you search text inside your database using Doctrine and Symfony.

✅ No need for Elasticsearch or other search engines!

✅ Works with your existing database!

⚠️ This is a simple and lightweight search solution for small to medium-sized projects. It does not support advanced features like fuzzy matching or search ranking.

📥 Installation

First, install the bundle using Composer:

composer require ayaou/db-search-bundle

Then, create the database table for indexing:

php bin/console make:migration
php bin/console doctrine:migrations:migrate

🗃️ Database Schema

The migration creates a table called db_search_index. Here is an example for MySQL/MariaDB:

CREATE TABLE db_search_index (
    id INT AUTO_INCREMENT NOT NULL,
    entity_class VARCHAR(255) NOT NULL,
    entity_id VARCHAR(255) NOT NULL,
    index_name VARCHAR(255) NOT NULL,
    indexed_text LONGTEXT NOT NULL,
    indexed_at DATETIME DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',
    INDEX IDX_934BE820F5A3A655 (index_name),
    INDEX IDX_934BE82041BF2C66 (entity_class),
    INDEX IDX_934BE82081257D5D (entity_id),
    FULLTEXT INDEX IDX_934BE82097A6C9C3 (indexed_text),
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

⚙️ Configuration

Define your indexes and entities in config/packages/db_search.yaml:

# config/packages/db_search.yaml
db_search:
  indexes:
    main:
      entities:
        App\Entity\MyEntity:
          fields:
            - title
            - description
        App\Entity\AnotherEntity:
          fields:
            - fullName
            - formattedAddress

⚠️ Important: Each entity must have a getId() method that returns a unique ID.

🏷️ Indexing Rules

✅ You can create multiple indexes for different entities or fields.

✅ Each entity can have multiple fields indexed.

✅ Fields can be public properties or public methods.

✅ Indexed fields must return text, numbers, or boolean values (string, int, float, bool).

🔎 How Indexing & Search Works

For each field you configure in an entity, the bundle will:

1. Take the field values.
2. Concatenate them into one long text string, separated by spaces.
3. Store the result in the indexed_text column.

When you search for a keyword, the bundle will:

1. Look for the keyword inside the indexed_text column.
2. Return matching results based on the index configuration.

🔎 Usage

Searching the Database

Use the Searcher service (@db_search.searcher) to search in the database:

<?php

namespace App\Controller;

use Ayaou\DbSearchBundle\Searcher\Searcher;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\Routing\Attribute\Route;

class MyController extends AbstractController
{
    #[Route('/search', name: 'search')]
    public function search(Request $request, Searcher $searcher)
    {
        $q = $request->query->get('q', '');
        
        // Search in all indexes
        $results = $searcher->search($q);
        
        // Search in a specific index
        // $results = $searcher->search($q, 'my_index');
        
        // Search in multiple indexes
        // $results = $searcher->search($q, ['my_index', 'another_index']);
        
        // Search for a specific entity
        // $results = $searcher->search($q, [], 'App\Entity\MyEntity');
        
        // Get raw search results
        // $results = $searcher->searchRaw($q);
        
        return $this->json($results);
    }
}

Regenerating the Search Index

Use the db-search:index:regenerate command to purge and rebuild the index for specific entities or indexes.

Reindex Everything

php bin/console db-search:index:regenerate

Reindex a Specific Index

php bin/console db-search:index:regenerate --index=main

Reindex a Specific Entity

php bin/console db-search:index:regenerate --entity=App\\Entity\\MyEntity

Reindex a Specific Entity in a Specific Index

php bin/console db-search:index:regenerate --index=main --entity=App\\Entity\\MyEntity

🏷️ This command purges existing index entries before reindexing.

🌟 Features

✅ Fast Search: Uses full-text search when available.

✅ Flexible Indexing: Supports multiple indexes and entities.

✅ Customizable: You can modify search behavior using Symfony services.

🔮 Future Improvements

🔹 Support for More Databases like PostgreSQL, SQLServer, Oracle.

🔹 Faster indexation with asynchronous indexing using Symfony Messenger.

🔹 Smarter Search with phonetic matching and synonyms.

🔹 Better Ranking to show more relevant results first.

💡 Contributions Welcome!

If you find a bug or have an idea for improvement, please create an issue or pull request. 🚀