inanzzz | Using a custom query_builder_method provider to populate elasticsearch index with manually selected data from database

If you use default options of persistence key in FOSElasticaBundle then you know that when you run $ app/console fos:elastica:populate command, it will select all records from database. If you want to override this behaviour to select what you want then you can use example below.

In example below, we want to ignore indexing unpublished posts.

Database status

mysql> SELECT count(*) AS TotalNotPublished
    -> FROM post
    -> WHERE is_published = false;
+-------------------+
| TotalNotPublished |
+-------------------+
|               486 |
+-------------------+
1 row in set (0.00 sec)

Query to use

curl -XPOST "http://127.0.0.1:9200/_search?post_dev" -d'
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "is_published": false
          }
        }
      ]
    }
  }
}'

Default provider config

This will select all records in database to populate elasticsearch index so we don't want to use this anymore.

persistence:
    driver: orm
    model: Application\SearchBundle\Entity\Post
    finder: ~
    provider: ~
    listener: ~

Query result

Unfortunately, it indexed all unpublished posts. Total 486 records found.

{
  "took": 4,
  "timed_out": false,
  "_shards": {
    "total": 15,
    "successful": 15,
    "failed": 0
  },
  "hits": {
    "total": 486,
    "max_score": 1.8724881,
    "hits": [
        ...
        ...
        ...
    ]
  }
}

Custom provider config

This will trigger createPublishedPostQueryBuilder method in "PostRepository" to select what we want to select in database to populate elasticsearch index.

persistence:
    driver: orm
    model: Application\SearchBundle\Entity\Post
    finder: ~
    provider:
        query_builder_method: 'createPublishedPostQueryBuilder'
    listener: ~

Repository

In the case below, the $alias variable represents post entity as p.

class PostRepository extends EntityRepository
{
    public function createPublishedPostQueryBuilder($alias)
    {
        $qb = $this->createQueryBuilder($alias);

        return $qb
            ->select($alias, 'c')
            ->join($alias.'.comment', 'c')
            ->where($qb->expr()->eq($alias.'.isPublished', $qb->expr()->literal(true)));
    }
}

Query result

There we go, total 0 records found which is what we wanted to see.

{
  "took": 2,
  "timed_out": false,
  "_shards": {
    "total": 15,
    "successful": 15,
    "failed": 0
  },
  "hits": {
    "total": 0,
    "max_score": null,
    "hits": [
      
    ]
  }
}