inanzzz | Handling database joins with elasticsearch in symfony applications

If your database has joined entities and you want to query them in elasticsearch then you can use example like below. This example depends on FOSElasticaBundle package.

To increase performance in joins, you must read Handling Relationships pages carefully. Pay attention to "Application-side Joins", "Denormalizing Your Data" and "Nested Objects" sections.

Setup

You need to install friendsofsymfony/elastica-bundle. At the time of writing this post, friendsofsymfony/elastica-bundle package was supporting only elasticsearch 1.7.4 and below.

Config.yml

Config for One to Many (Post - Comment) join.

fos_elastica:
    clients:
        default: { host: 127.0.0.1, port: 9203 }
    indexes:
        post_index:
            client: default
            index_name: post_%kernel.environment%
            types:
                post:
                    mappings:
                        id:
                            type: integer
                            index: not_analyzed
                        title:
                            type: string
                            analyzer: english
                        is_published:
                            type: boolean
                            index: not_analyzed
                        comment:
                            type: object
                            properties:
                                id:
                                    type: integer
                                    index: not_analyzed
                                message:
                                    type: string
                    persistence:
                        driver: orm
                        model: Application\SearchBundle\Entity\Post
                        finder: ~
                        provider: ~
                        listener: ~

If you want to do same thing in MongoDB, all you should do is small changes such as, change "id" fields to "string" type and "driver" to "mongodb".

Post entity

namespace Application\SearchBundle\Entity;

use DateTime;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="post")
 */
class Post
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(name="title", type="string", length=100)
     */
    private $title;

    /**
     * @ORM\Column(name="is_published", type="boolean")
     */
    private $isPublished;

    /**
     * @ORM\OneToMany(targetEntity="Comment", mappedBy="post", cascade={"persist", "remove"})
     */
    private $comment;
}

Comment entity

namespace Application\SearchBundle\Entity;

use DateTime;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity
 * @ORM\Table(name="comment")
 */
class Comment
{
    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(name="message", type="text")
     */
    private $message;

    /**
     * @ORM\ManyToOne(targetEntity="Post", inversedBy="comment")
     * @ORM\JoinColumn(name="post_id", referencedColumnName="id", onDelete="CASCADE", nullable=false)
     */
    private $post;
}

Query 1

Assume that we're looking for "Eltit" in "post.title" and "inanzzz" in "comment.message" fields.

Doctrine

return $this
    ->createQueryBuilder('p')
    ->select('p, c')
    ->innerJoin('p.comment', 'c')
    ->where('p.title LIKE :title')
    ->andWhere('c.message LIKE :message')
    ->setParameter('title', '%Eltit%')
    ->setParameter('message', '%inanzzz%')
    ->getQuery()
    ->getResult();

MySQL

SELECT *
FROM post
INNER JOIN comment ON post.id = comment.post_id
WHERE
MATCH(`post`.`title`) AGAINST ('Eltit' IN BOOLEAN MODE) AND
MATCH(`comment`.`message`) AGAINST ('inanzzz' IN BOOLEAN MODE)
#GROUP BY post.id #This would return same amount of records as doctrine and ES

Elasticsearch

curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "title": "Eltit"
          }
        },
        {
          "match": {
            "comment.message": "inanzzz"
          }
        }
      ]
    }
  },
  "sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'

Query 2

Assume that we're looking for "true" in "post.is_published" and "inanzzz" in "comment.message" fields.

Doctrine

You can do this.

MySQL

You can do this.

Elasticsearch

curl -XGET "http://127.0.0.1:9203/_search?post_dev" -d'
{
  "query": {
    "filtered": {
      "query": {
        "match": {
          "comment.message": "inanzzz"
        }
      },
      "filter": {
        "bool": {
          "must_not": {
            "term": {
              "is_published": "false"
            }
          }
        }
      }
    }
  },
  "sort": [
    {
      "_score": {
        "order": "desc"
      }
    }
  ],
  "from": "0",
  "size": "10"
}'