5 * Copyright 2004 (c) Dominik Haas, GForge Team
12 require_once('common/search/SearchQuery.class');
14 class TrackersSearchQuery extends SearchQuery {
24 * flag if non public items are returned
26 * @var boolean $showNonPublic
33 * @param string $words words we are searching for
34 * @param int $offset offset
35 * @param boolean $isExact if we want to search for all the words or if only one matching the query is sufficient
36 * @param int $groupId group id
37 * @param array $sections sections to search in
38 * @param boolean $showNonPublic flag if private sections are searched too
40 function TrackersSearchQuery($words, $offset, $isExact, $groupId, $sections=SEARCH__ALL_SECTIONS, $showNonPublic=false) {
41 $this->groupId = $groupId;
42 $this->showNonPublic = $showNonPublic;
44 $this->SearchQuery($words, $offset, $isExact);
46 $this->setSections($sections);
50 * getQuery - get the sql query built to get the search results
52 * @return string sql query to execute
57 if(count($this->words)) {
58 $tsquery = ", to_tsquery('".$this->getFormattedWords()."') q, artifact_idx, artifact_message_idx ";
59 $tsmatch = "(artifact_idx.vectors @@ q OR artifact_message_idx.vectors @@ q)";
60 $rankCol = ", (rank(artifact_idx.vectors, q)+rank(artifact_message_idx.vectors, q)) AS rank ";
61 $tsjoin = 'AND artifact_idx.artifact_id = artifact.artifact_id '
62 . 'AND artifact_message_idx.id = artifact_message.id '
63 . 'AND artifact_message_idx.artifact_id = artifact_message_idx.artifact_id ';
64 $orderBy = "ORDER BY RANK DESC";
65 $phraseOp = $this->getOperator();
75 if(count($this->phrases)) {
76 $phraseCond .= $phraseOp.'('
77 . ' ('.$this->getMatchCond('artifact.details', $this->phrases).')'
78 . ' OR ('.$this->getMatchCond('artifact.summary', $this->phrases).')'
79 . ' OR ('.$this->getMatchCond('artifact_message.body', $this->phrases).'))';
81 $sql = 'SELECT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_group_list.name '
83 . 'FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list '
85 . ' WHERE users.user_id = artifact.submitted_by '
87 . 'AND artifact_group_list.group_artifact_id = artifact.group_artifact_id '
88 . 'AND artifact_group_list.group_id = '.$this->groupId.' ';
89 if ($this->sections != SEARCH__ALL_SECTIONS) {
90 $sql .= 'AND artifact_group_list.group_artifact_id in ('.$this->sections.') ';
92 if (!$this->showNonPublic) {
93 $sql .= 'AND artifact_group_list.is_public = 1 ';
95 $sql .= "AND ($tsmatch $phraseCond)";
96 $sql = "SELECT DISTINCT x.* FROM ($sql) x $orderBy";
98 $sql = 'SELECT DISTINCT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_group_list.name '
99 . 'FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list '
100 . 'WHERE users.user_id = artifact.submitted_by '
101 . 'AND artifact_group_list.group_artifact_id = artifact.group_artifact_id '
102 . 'AND artifact_group_list.group_id = '.$this->groupId.' ';
103 if ($this->sections != SEARCH__ALL_SECTIONS) {
104 $sql .= 'AND artifact_group_list.group_artifact_id in ('.$this->sections.') ';
106 if (!$this->showNonPublic) {
107 $sql .= 'AND artifact_group_list.is_public = 1 ';
109 $sql .= 'AND (('.$this->getIlikeCondition('artifact.details', $this->words).') '
110 . 'OR ('.$this->getIlikeCondition('artifact.summary', $this->words).') '
111 . 'OR ('.$this->getIlikeCondition('artifact_message.body', $this->words).')) '
112 . 'ORDER BY artifact_group_list.name, artifact.artifact_id';
118 * getSections - returns the list of available trackers
120 * @param $groupId int group id
121 * @param $showNonPublic boolean if we should consider non public sections
123 function getSections($groupId, $showNonPublic=false) {
124 $sql = 'SELECT group_artifact_id, name FROM artifact_group_list WHERE group_id = '.$groupId.'';
125 if (!$showNonPublic) {
126 $sql .= ' AND artifact_group_list.is_public = 1';
128 $sql .= ' ORDER BY name';
131 $res = db_query($sql);
132 while($data = db_fetch_array($res)) {
133 $sections[$data['group_artifact_id']] = $data['name'];
138 function getSearchByIdQuery() {
139 $sql = 'SELECT DISTINCT artifact.artifact_id, artifact.group_artifact_id, artifact.summary, artifact.open_date, users.realname, artifact_group_list.name '
140 . 'FROM artifact LEFT OUTER JOIN artifact_message USING (artifact_id), users, artifact_group_list '
141 . 'WHERE users.user_id = artifact.submitted_by '
142 . 'AND artifact_group_list.group_artifact_id = artifact.group_artifact_id '
143 . 'AND artifact_group_list.group_id = '.$this->groupId.' ';
144 if ($this->sections != SEARCH__ALL_SECTIONS) {
145 $sql .= 'AND artifact_group_list.group_artifact_id in ('.$this->sections.') ';
147 if (!$this->showNonPublic) {
148 $sql .= 'AND artifact_group_list.is_public = 1 ';
150 $sql .= 'AND artifact.artifact_id=\''.$this->searchId.'\''
151 . 'ORDER BY artifact_group_list.name, artifact.artifact_id';