public function getQuery()
{
$words = implode('&', $this->searchWords);
$sub = clone $this->qb;
$this->qb->addSelect("ts_rank(bsearch.document, to_tsquery('" . $words . "')) as score");
$sub->select('*');
$select = [];
$fieldsToSearch = $this->config->getConfig($this->contentType);
$joins = $this->config->getJoins($this->contentType);
$fieldsToSearch = array_diff_key($fieldsToSearch, array_flip($joins));
$from = $this->qb->getQueryPart('from');
if (isset($from[0]['alias'])) {
$alias = $from[0]['alias'];
} else {
$alias = $from[0]['table'];
}
foreach ($fieldsToSearch as $fieldName => $config) {
$weight = $this->getWeight($config['weight']);
$select[] = "setweight(to_tsvector({$alias}.{$fieldName}), '{$weight}')";
}
$sub->select('*, ' . implode(' || ', $select) . ' AS document');
$sub->groupBy("{$alias}.id");
$this->qb->from('(' . $sub->getSQL() . ')', 'bsearch');
$this->qb->where("bsearch.document @@ to_tsquery('" . $words . "')");
$this->qb->orderBy('score', 'DESC');
return $this->qb;
}
public function testPostgresQueryBuild() { $app = $this->getApp(); $this->addSomeContent(); $repo = $app['storage']->getRepository('bolt_pages'); $query = $repo->createQueryBuilder('pages'); $handler = new PostgresSearch($query, $app['query.search_config'], explode(' ', 'lorem ipsum')); $handler->setContentType('pages'); $query = $handler->getQuery(); $this->assertEquals(['pages.*', "ts_rank(bsearch.document, to_tsquery('lorem&ipsum')) as score"], $query->getQueryPart('select')); $this->assertEquals([['table' => 'bolt_pages', 'alias' => 'pages'], ['table' => "(SELECT *, setweight(to_tsvector(pages.title), 'A') || setweight(to_tsvector(pages.teaser), 'B') || setweight(to_tsvector(pages.body), 'B') AS document FROM bolt_pages pages GROUP BY pages.id)", 'alias' => 'bsearch']], $query->getQueryPart('from')); $this->assertInstanceOf('Doctrine\\DBAL\\Query\\Expression\\CompositeExpression', $query->getQueryPart('where')); $this->assertEquals(['score DESC'], $query->getQueryPart('orderBy')); }