public function executeSimple($request)
{
$page = $request->getParameter('page', 1);
$num = $request->getParameter('num', 20);
$terms = $request->getParameter('q', '');
//networks to search
$networkIds = (array) $request->getParameter('network_ids');
//only show network options if user has home network other than United States
if (count($networkIds)) {
$this->networks = LsDoctrineQuery::create()->from('LsList l')->whereIn('l.id', $networkIds)->fetchArray();
} else {
$networkIds = null;
}
if ($this->getUser()->hasCredential('admin') || !sfConfig::get('app_blacklist_search_enabled')) {
$this->results_pager = EntityTable::getSphinxPager($terms, $page, $num, $networkIds);
} else {
$blacklistIds = (array) sfConfig::get('app_blacklist_search_ids');
$entities = array();
$result = EntityTable::getSphinxHits($terms, $page, $num, $networkIds);
if ($result['total_found'] > 0 && isset($result['matches'])) {
$ids = array_diff(array_keys($result['matches']), $blacklistIds);
if (count($ids) > 0) {
$db = Doctrine_Manager::connection();
$sql = 'SELECT e.*, FIELD(e.id, ' . implode(',', $ids) . ') AS field ' . 'FROM entity e WHERE e.id IN (' . implode(',', $ids) . ') AND e.is_deleted = 0 ' . 'ORDER BY field';
$stmt = $db->execute($sql);
$entities = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
}
$pager = new LsDoctrinePager($entities, $page, $num);
$pager->setNumResults(count($ids));
$pager->isSubsetWithCount(true);
$this->results_pager = $pager;
}
//search lists
if (strlen($terms) > 2) {
$this->lists = LsListTable::getListsFromSphinxSearch($terms, $this->getUser()->hasCredential('admin'));
// $db = Doctrine_Manager::connection();
// $sql = 'SELECT l.* FROM ls_list l WHERE l.name LIKE ? AND l.is_deleted = 0 AND l.is_network = 0 ' .
// ($this->getUser()->hasCredential('admin') ? '' : 'AND l.is_admin = 0 ') .
// 'ORDER BY l.name ASC';
// $stmt = $db->execute($sql, array('%' . $terms . '%'));
// $this->lists = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
//search groups
if (strlen($terms) > 2) {
$db = Doctrine_Manager::connection();
if (sfConfig::get('app_rails_enabled')) {
$sql = 'SELECT g.* FROM groups g WHERE (g.name LIKE ? OR g.tagline LIKE ? OR g.description LIKE ? OR g.slug LIKE ?) AND g.is_private = 0 ORDER BY g.name ASC';
$stmt = $db->execute($sql, array('%' . $terms . '%', '%' . $terms . '%', '%' . $terms . '%', '%' . $terms . '%'));
} else {
$sql = 'SELECT g.* FROM sf_guard_group g WHERE (g.display_name LIKE ? OR g.name LIKE ? OR g.blurb LIKE ?) AND g.is_working = 1 AND g.is_private = 0 ORDER BY g.display_name ASC';
$stmt = $db->execute($sql, array('%' . $terms . '%', '%' . $terms . '%', '%' . $terms . '%'));
}
$this->groups = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
//search campaigns
if (strlen($terms) > 2 && sfConfig::get('app_rails_enabled')) {
$db = Doctrine_Manager::connection();
$sql = 'SELECT c.* FROM campaigns c WHERE (c.name LIKE ? OR c.tagline LIKE ? OR c.description LIKE ? OR c.slug LIKE ?) ORDER BY c.name ASC';
$stmt = $db->execute($sql, array('%' . $terms . '%', '%' . $terms . '%', '%' . $terms . '%', '%' . $terms . '%'));
$this->campaigns = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
}
static function getSphinxPager($query, $page, $num, array $listIds = null, $aliases = true, $primary_ext = null)
{
$entities = array();
$result = self::getSphinxHits($query, $page, $num, $listIds, $aliases, $primary_ext);
if ($result['total_found'] > 0 && isset($result['matches'])) {
$ids = array_keys($result['matches']);
$db = Doctrine_Manager::connection();
$sql = 'SELECT e.*, FIELD(e.id, ' . implode(',', $ids) . ') AS field ' . 'FROM entity e WHERE e.id IN (' . implode(',', $ids) . ') AND e.is_deleted = 0 ' . 'ORDER BY field';
$stmt = $db->execute($sql);
$entities = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
$pager = new LsDoctrinePager($entities, $page, $num);
$pager->setNumResults($result['total_found']);
$pager->isSubsetWithCount(true);
return $pager;
}
static function getPagerFromSphinxHits($result, $page, $num)
{
if ($result['total_found'] > 0 && isset($result['matches'])) {
$ids = array_keys($result['matches']);
$db = Doctrine_Manager::connection();
$sql = 'SELECT n.*, FIELD(n.id, ' . implode(',', $ids) . ') AS field ' . 'FROM note n WHERE n.id IN (' . implode(',', $ids) . ') ' . 'ORDER BY field';
$stmt = $db->execute($sql);
$notes = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
$pager = new LsDoctrinePager($notes, $page, $num);
$pager->setNumResults($result['total_found']);
$pager->isSubsetWithCount(true);
return $pager;
}
