6 * Portions Copyright 1999-2001 (c) VA Linux Systems
7 * The rest Copyright 2004 (c) Guillaume Smet / Open Wide
14 require_once('common/search/SearchQuery.class');
16 class ArtifactSearchQuery extends SearchQuery {
28 * @var int $artifactId
35 * @param string $words words we are searching for
36 * @param int $offset offset
37 * @param boolean $isExact if we want to search for all the words or if only one matching the query is sufficient
38 * @param int $groupId group id
39 * @param int $artifactId artifact id
41 function ArtifactSearchQuery($words, $offset, $isExact, $groupId, $artifactId) {
42 //TODO: Why is groupId an arg and var since it isn't used anywhere?
43 $this->groupId = $groupId;
44 $this->artifactId = $artifactId;
46 $this->SearchQuery($words, $offset, $isExact);
50 * getQuery - get the sql query built to get the search results
52 * @return string sql query to execute
55 global $sys_database_type;
59 $words=$this->getFormattedWords();
60 $artifactId = $this->artifactId;
62 $tsquery0 = "headline(summary, '".$this->getFormattedWords()."') as summary";
63 $tsquery = ", artifact_idx ai, artifact_message_idx ami, to_tsquery('".$words."') q";
64 $tsmatch = "(ai.vectors @@ q OR ami.vectors @@ q)";
65 $rankCol = "sum((rank(ai.vectors, q)+rank(ami.vectors, q))) as rank";
66 $tsjoin = 'AND ai.artifact_id = a.artifact_id '
67 . 'AND ami.id = am.id ';
68 $phraseOp = $this->getOperator();
70 $tsquery0 = "summary";
74 $rankCol = "0 as rank";
78 if (count($this->phrases)) {
79 $detailsCond = $this->getMatchCond('a.details', $this->phrases);
80 $summaryCond = $this->getMatchCond('a.summary', $this->phrases);
81 $msgCond = $this->getMatchCond('am.body', $this->phrases);
82 $phraseCond = "$phraseOp (($detailsCond) OR ($summaryCond))";
85 select a.group_artifact_id,a.artifact_id, $tsquery0,
86 a.open_date,users.realname, rank
87 FROM (SELECT a.artifact_id,
89 FROM artifact a LEFT OUTER JOIN artifact_message am USING (artifact_id)
92 a.group_artifact_id='$artifactId'
94 AND ($tsmatch $phraseCond)
95 GROUP BY a.artifact_id) x,
98 a.artifact_id = x.artifact_id
99 AND users.user_id=a.submitted_by
100 ORDER BY group_artifact_id ASC, rank DESC, a.artifact_id ASC";
103 if ($sys_database_type == "mysql") {
104 $sql = 'SELECT DISTINCT a.group_artifact_id,a.artifact_id,a.summary,a.open_date,users.realname ';
106 $sql = 'SELECT DISTINCT ON (a.group_artifact_id,a.artifact_id) a.group_artifact_id,a.artifact_id,a.summary,a.open_date,users.realname ';
108 $sql.='FROM artifact a LEFT OUTER JOIN artifact_message am USING (artifact_id), users '
109 . 'WHERE a.group_artifact_id=\''.$this->artifactId.'\' '
110 . 'AND users.user_id=a.submitted_by '
111 . 'AND (('.$this->getIlikeCondition('a.details', $this->words).') '
112 . 'OR ('.$this->getIlikeCondition('a.summary', $this->words).') '
113 . 'OR ('.$this->getIlikeCondition('am.body', $this->words).')) '
114 . 'ORDER BY group_artifact_id ASC, a.artifact_id ASC';
120 * getSearchByIdQuery - get the sql query built to get the search results when we are looking for an int
122 * @return string sql query to execute
124 function getSearchByIdQuery() {
125 global $sys_database_type;
127 if ($sys_database_type == "mysql") {
128 $sql = 'SELECT DISTINCT a.group_artifact_id, a.artifact_id ';
130 $sql = 'SELECT DISTINCT ON (a.group_artifact_id,a.artifact_id) a.group_artifact_id, a.artifact_id ';
132 $sql.='FROM artifact a '
133 . 'WHERE a.group_artifact_id=\''.$this->artifactId.'\' '
134 . 'AND a.artifact_id=\''.$this->searchId.'\'';