/**
* Search for a line
*
* @param string $q The search query
* @param integer $page The page of results to retrieve
* @param integer $perPage How many results to retrieve
* @return SearchResult The search results
* @see ChaosTangent\FansubEbooks\Bundle\AppBundle\DataFixtures\ORM\CreateSearchIndex
*/
public function search($q, $page = 1, $perPage = 30, Series $series = null)
{
if (empty(trim($q))) {
return new SearchResult($q, [], 0, $page, $perPage);
}
// default query parameters
$defaultParams = [':query' => trim($q), ':config' => 'english'];
// default where clause
$whereClause = 'WHERE to_tsvector(:config, l.line) @@ to_tsquery(:query)';
if ($series !== null) {
$whereClause .= ' AND f.series_id = :series';
$defaultParams['series'] = $series->getId();
}
// count total results from search query
$countSql = 'SELECT COUNT(l.id)
FROM lines l
JOIN files f ON f.id = l.file_id ' . $whereClause;
$total = $this->_em->getConnection()->fetchColumn($countSql, $defaultParams, 0);
// get the selected page of results from search query
$rsm = new ResultSetMappingBuilder($this->_em);
$rsm->addRootEntityFromClassMetadata('ChaosTangent\\FansubEbooks\\Entity\\Line', 'l');
$rsm->addScalarResult('positive_votes', 'positive_votes', 'integer');
$rsm->addScalarResult('negative_votes', 'negative_votes', 'integer');
$rsm->addScalarResult('tweet_id', 'tweet_id');
$sql = 'SELECT ' . $rsm->generateSelectClause() . ',
SUM(CASE WHEN v.positive = true THEN 1 ELSE 0 END) AS positive_votes,
SUM(CASE WHEN v.positive = false THEN 1 ELSE 0 END) AS negative_votes,
t.tweet_id
FROM lines l
JOIN files f ON f.id = l.file_id
LEFT JOIN votes v ON v.line_id = l.id
LEFT JOIN tweets t ON t.line_id = l.id ' . $whereClause . '
GROUP BY l.id, t.tweet_id
ORDER BY ts_rank(to_tsvector(:config, l.line), to_tsquery(:query))
LIMIT :limit OFFSET :offset';
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameters(array_merge($defaultParams, ['limit' => $perPage, 'offset' => ($page - 1) * $perPage]));
$result = $query->getResult();
$ret = [];
foreach ($result as $row) {
$ret[] = $row[0]->setPositiveVoteCount($row['positive_votes'])->setNegativeVoteCount($row['negative_votes'])->setTweetId($row['tweet_id']);
}
// bundle it all into a searchresult object
return new SearchResult($q, $ret, $total, $page, $perPage);
}
