* @return array query+params array
*/
function getQuery() {
-
-
$qpa = db_construct_qpa () ;
+ $qpa = db_construct_qpa ($qpa,
+ 'SELECT x.* FROM (SELECT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_group_list.name, artifact.summary||$1||artifact.details||$1||coalesce(ff_string_agg(artifact_message.body), $1) as full_string_agg',
+ array (''));
if (forge_get_config('use_fti')) {
+ $words = $this->getFTIwords();
$qpa = db_construct_qpa ($qpa,
- 'SELECT DISTINCT x.* FROM (SELECT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_group_list.name, (ts_rank(artifact_idx.vectors, q)+ts_rank(artifact_message_idx.vectors, q)) AS rank FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list, to_tsquery($1) q, artifact_idx, artifact_message_idx WHERE users.user_id = artifact.submitted_by AND artifact_idx.artifact_id = artifact.artifact_id AND artifact_message_idx.id = artifact_message.id AND artifact_message_idx.artifact_id = artifact_message_idx.artifact_id AND artifact_group_list.group_artifact_id = artifact.group_artifact_id AND artifact_group_list.group_id = $2 ',
- array ($this->getFTIwords(),
- $this->groupId)) ;
- $tsmatch = "(artifact_idx.vectors @@ q OR artifact_message_idx.vectors @@ q)";
- $phraseOp = $this->getOperator();
-
- if (count($this->phrases)) {
- $qpa = db_construct_qpa ($qpa,
- 'AND ((') ;
- $qpa = $this->addMatchCondition($qpa, 'artifact.details');
- $qpa = db_construct_qpa ($qpa,
- ') OR (') ;
- $qpa = $this->addMatchCondition($qpa, 'artifact.summary');
- $qpa = db_construct_qpa ($qpa,
- ') OR (') ;
- $qpa = $this->addMatchCondition($qpa, 'artifact_message.body');
- $qpa = db_construct_qpa ($qpa,
- ')) ') ;
- }
- if ($this->sections != SEARCH__ALL_SECTIONS) {
- $qpa = db_construct_qpa ($qpa,
- 'AND artifact_group_list.group_artifact_id = ANY ($1) ',
- array( db_int_array_to_any_clause ($this->sections))) ;
- }
- if (!$this->showNonPublic) {
- $qpa = db_construct_qpa ($qpa,
- 'AND artifact_group_list.is_public = 1 ') ;
- }
+ ', (artifact_idx.vectors || coalesce(ff_tsvector_agg(artifact_message_idx.vectors), $1::tsvector)) AS full_vector_agg',
+ array (''));
+ }
+ $qpa = db_construct_qpa ($qpa,
+ ' FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list',
+ array ()) ;
+ if (forge_get_config('use_fti')) {
+ $qpa = db_construct_qpa ($qpa,
+ ', artifact_idx, artifact_message_idx',
+ array ()) ;
+ }
+ $qpa = db_construct_qpa ($qpa,
+ ' WHERE users.user_id = artifact.submitted_by AND artifact_group_list.group_artifact_id = artifact.group_artifact_id AND artifact_group_list.group_id = $1 AND artifact_message.artifact_id = artifact.artifact_id ',
+ array ($this->groupId)) ;
+ if ($this->sections != SEARCH__ALL_SECTIONS) {
$qpa = db_construct_qpa ($qpa,
- ') x') ;
+ 'AND artifact_group_list.group_artifact_id = ANY ($1) ',
+ array (db_int_array_to_any_clause ($this->sections))) ;
+ }
+ if (!$this->showNonPublic) {
$qpa = db_construct_qpa ($qpa,
- 'ORDER BY rank DESC') ;
- } else {
+ 'AND artifact_group_list.is_public = 1 ') ;
+ }
+ if (forge_get_config('use_fti')) {
+ $qpa = db_construct_qpa ($qpa,
+ 'AND artifact.artifact_id = artifact_idx.artifact_id AND artifact_message.id = artifact_message_idx.id ',
+ array ()) ;
+ }
+ $qpa = db_construct_qpa ($qpa,
+ 'GROUP BY artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_group_list.name, artifact.details') ;
+
+ if (forge_get_config('use_fti')) {
+ $qpa = db_construct_qpa ($qpa,
+ ', artifact_idx.vectors',
+ array ()) ;
+ }
+ $qpa = db_construct_qpa ($qpa,
+ ') AS x WHERE ') ;
+
+ if (forge_get_config('use_fti')) {
$qpa = db_construct_qpa ($qpa,
- 'SELECT DISTINCT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_group_list.name FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list WHERE users.user_id = artifact.submitted_by AND artifact_group_list.group_artifact_id = artifact.group_artifact_id AND artifact_group_list.group_id = $1 ',
- array ($this->groupId)) ;
- if ($this->sections != SEARCH__ALL_SECTIONS) {
+ 'full_vector_agg @@ $1 ',
+ array($words));
+ if (count($this->phrases)) {
$qpa = db_construct_qpa ($qpa,
- 'AND artifact_group_list.group_artifact_id = ANY ($1) ',
- array(db_int_array_to_any_clause ($this->sections))) ;
- }
- if (!$this->showNonPublic) {
+ 'AND (') ;
+ $qpa = $this->addMatchCondition ($qpa, 'x.full_string_agg') ;
$qpa = db_construct_qpa ($qpa,
- 'AND artifact_group_list.is_public = 1 ') ;
+ ') ') ;
}
-
- $qpa = db_construct_qpa ($qpa,
- ' AND ((') ;
- $qpa = $this->addIlikeCondition ($qpa, 'artifact.details') ;
$qpa = db_construct_qpa ($qpa,
- ') OR (') ;
- $qpa = $this->addIlikeCondition ($qpa, 'artifact.summary') ;
- $qpa = db_construct_qpa ($qpa,
- ') OR (') ;
- $qpa = $this->addIlikeCondition ($qpa, 'artifact_message.body') ;
+ 'ORDER BY ts_rank(full_vector_agg, $1) DESC',
+ array($words)) ;
+
+ } else {
+ $qpa = $this->addIlikeCondition ($qpa, 'x.full_string_agg') ;
$qpa = db_construct_qpa ($qpa,
- ')) ORDER BY artifact_group_list.name, artifact.artifact_id') ;
+ ' ORDER BY x.name, x.artifact_id') ;
}
return $qpa ;
}