MDL-26371 sql - avoid distinct + text in db enrol
[moodle.git] / mod / glossary / sql.php
CommitLineData
4f3e59c3 1<?php
2
3/**
4 * SQL.PHP
5 * This file is include from view.php and print.php
ae8c3566 6 * @copyright 2003
4f3e59c3 7 **/
8
9/// Creating the SQL statements
10
1c144d0e 11/// Initialise some variables
12 $sqlorderby = '';
54eca8f2 13 $sqlsortkey = NULL;
afda6b56 14 $textlib = textlib_get_instance();
1c144d0e 15
572889f1 16/// Calculate the SQL sortkey to be used by the SQL statements later
ae8c3566 17 switch ( $sortkey ) {
18 case "CREATION":
572889f1 19 $sqlsortkey = "timecreated";
20 break;
ae8c3566 21 case "UPDATE":
572889f1 22 $sqlsortkey = "timemodified";
23 break;
ae8c3566 24 case "FIRSTNAME":
572889f1 25 $sqlsortkey = "firstname";
26 break;
ae8c3566 27 case "LASTNAME":
572889f1 28 $sqlsortkey = "lastname";
29 break;
30 }
31 $sqlsortorder = $sortorder;
32
4f3e59c3 33/// Pivot is the field that set the break by groups (category, initial, author name, etc)
34
35/// fullpivot indicate if the whole pivot should be compared agasint the db or just the first letter
36/// printpivot indicate if the pivot should be printed or not
c9128b63 37
4f3e59c3 38 $fullpivot = 1;
ae8c3566 39 $params = array('gid1'=>$glossary->id, 'gid2'=>$glossary->id, 'myid'=>$USER->id, 'hook'=>$hook);
4f3e59c3 40
41 $userid = '';
4f0c2d00 42 if ( isloggedin() ) {
ae8c3566 43 $userid = "OR ge.userid = :myid";
4f3e59c3 44 }
45 switch ($tab) {
46 case GLOSSARY_CATEGORY_VIEW:
ae8c3566 47 if ($hook == GLOSSARY_SHOW_ALL_CATEGORIES ) {
4f3e59c3 48
e24d96ba 49 $sqlselect = "SELECT gec.id AS cid, ge.*, gec.entryid, gc.name AS glossarypivot";
ae8c3566 50 $sqlfrom = "FROM {glossary_entries} ge,
51 {glossary_entries_categories} gec,
52 {glossary_categories} gc";
53 $sqlwhere = "WHERE (ge.glossaryid = :gid1 OR ge.sourceglossaryid = :gid2) AND
4f3e59c3 54 ge.id = gec.entryid AND gc.id = gec.categoryid AND
ae8c3566 55 (ge.approved <> 0 $userid)";
4f3e59c3 56
a359c29b 57 $sqlorderby = ' ORDER BY gc.name, ge.concept';
4f3e59c3 58
ae8c3566 59 } elseif ($hook == GLOSSARY_SHOW_NOT_CATEGORISED ) {
4f3e59c3 60
61 $printpivot = 0;
a91960b3 62 $sqlselect = "SELECT ge.*, concept AS glossarypivot";
ae8c3566 63 $sqlfrom = "FROM {glossary_entries} ge LEFT JOIN {glossary_entries_categories} gec
64 ON ge.id = gec.entryid";
65 $sqlwhere = "WHERE (glossaryid = :gid1 OR sourceglossaryid = :gid2) AND
66 (ge.approved <> 0 $userid) AND gec.entryid IS NULL";
4f3e59c3 67
68
69 $sqlorderby = ' ORDER BY concept';
70
71 } else {
72
73 $printpivot = 0;
a91960b3 74 $sqlselect = "SELECT ge.*, ce.entryid, c.name AS glossarypivot";
ae8c3566 75 $sqlfrom = "FROM {glossary_entries} ge, {glossary_entries_categories} ce, {glossary_categories} c";
76 $sqlwhere = "WHERE ge.id = ce.entryid AND ce.categoryid = :hook AND
4f3e59c3 77 ce.categoryid = c.id AND ge.approved != 0 AND
ae8c3566 78 (ge.glossaryid = :gid1 OR ge.sourceglossaryid = :gid2) AND
79 (ge.approved <> 0 $userid)";
4f3e59c3 80
81 $sqlorderby = ' ORDER BY c.name, ge.concept';
82
83 }
84 break;
85 case GLOSSARY_AUTHOR_VIEW:
86
87 $where = '';
ae8c3566 88 $params['hookup'] = $textlib->strtoupper($hook);
2cb3ab13 89
572889f1 90 if ( $sqlsortkey == 'firstname' ) {
ae8c3566 91 $usernamefield = $DB->sql_fullname('u.firstname' , 'u.lastname');
2cb3ab13 92 } else {
ae8c3566 93 $usernamefield = $DB->sql_fullname('u.lastname' , 'u.firstname');
4f3e59c3 94 }
655bbf51 95 $where = "AND " . $DB->sql_substr("upper($usernamefield)", 1, $textlib->strlen($hook)) . " = :hookup";
2cb3ab13 96
4f3e59c3 97 if ( $hook == 'ALL' ) {
98 $where = '';
99 }
100
8451166c 101 $sqlselect = "SELECT ge.*, $usernamefield AS glossarypivot, 1 AS userispivot ";
ae8c3566 102 $sqlfrom = "FROM {glossary_entries} ge, {user} u";
4f3e59c3 103 $sqlwhere = "WHERE ge.userid = u.id AND
ae8c3566 104 (ge.approved <> 0 $userid)
105 $where AND
106 (ge.glossaryid = :gid1 OR ge.sourceglossaryid = :gid2)";
db87439a 107 $sqlorderby = "ORDER BY $usernamefield $sqlsortorder, ge.concept";
4f3e59c3 108 break;
109 case GLOSSARY_APPROVAL_VIEW:
110 $fullpivot = 0;
111 $printpivot = 0;
112
113 $where = '';
ae8c3566 114 $params['hookup'] = $textlib->strtoupper($hook);
115
4f3e59c3 116 if ($hook != 'ALL' and $hook != 'SPECIAL') {
655bbf51 117 $where = "AND " . $DB->sql_substr("upper(concept)", 1, $textlib->strlen($hook)) . " = :hookup";
4f3e59c3 118 }
119
a91960b3 120 $sqlselect = "SELECT ge.*, ge.concept AS glossarypivot";
ae8c3566 121 $sqlfrom = "FROM {glossary_entries} ge";
122 $sqlwhere = "WHERE (ge.glossaryid = :gid1 OR ge.sourceglossaryid = :gid2) AND
4f3e59c3 123 ge.approved = 0 $where";
ae8c3566 124
db87439a 125 if ( $sqlsortkey ) {
126 $sqlorderby = "ORDER BY $sqlsortkey $sqlsortorder";
4f3e59c3 127 } else {
128 $sqlorderby = "ORDER BY ge.concept";
129 }
130 break;
131 case GLOSSARY_DATE_VIEW:
c356bb40 132 $printpivot = 0;
4f3e59c3 133 case GLOSSARY_STANDARD_VIEW:
134 default:
a91960b3 135 $sqlselect = "SELECT ge.*, ge.concept AS glossarypivot";
ae8c3566 136 $sqlfrom = "FROM {glossary_entries} ge";
4f3e59c3 137
138 $where = '';
139 $fullpivot = 0;
4f3e59c3 140
141 switch ( $mode ) {
ae8c3566 142 case 'search':
74a27855 143
ae8c3566 144 if ($DB->sql_regex_supported()) {
145 $REGEXP = $DB->sql_regex(true);
146 $NOTREGEXP = $DB->sql_regex(false);
74a27855 147 }
ae8c3566 148
149 $searchcond = array();
150 $alcond = array();
eaed2fd1 151 //$params = array();
ae8c3566 152 $i = 0;
74a27855 153
eaed2fd1 154 $concat = $DB->sql_concat('ge.concept', "' '", 'ge.definition',"' '", "COALESCE(al.alias, '')");
74a27855 155
156 $searchterms = explode(" ",$hook);
157
158 foreach ($searchterms as $searchterm) {
ae8c3566 159 $i++;
0fd4f407 160
e99cfeb8 161 $NOT = false; /// Initially we aren't going to perform NOT LIKE searches, only MSSQL and Oracle
ae8c3566 162 /// will use it to simulate the "-" operator with LIKE clause
6f4bdb9c 163
164 /// Under Oracle and MSSQL, trim the + and - operators and perform
ae8c3566 165 /// simpler LIKE (or NOT LIKE) queries
166 if (!$DB->sql_regex_supported()) {
167 if (substr($searchterm, 0, 1) == '-') {
e99cfeb8 168 $NOT = true;
ae8c3566 169 }
6f4bdb9c 170 $searchterm = trim($searchterm, '+-');
171 }
172
ae8c3566 173 if (substr($searchterm,0,1) == '+') {
174 $searchterm = trim($searchterm, '+-');
175 if ($textlib->strlen($searchterm) < 2) {
176 continue;
177 }
178 $searchterm = preg_quote($searchterm, '|');
179 $searchcond[] = "$concat $REGEXP :ss$i";
180 $params['ss'.$i] = "(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)";
181
74a27855 182 } else if (substr($searchterm,0,1) == "-") {
ae8c3566 183 $searchterm = trim($searchterm, '+-');
184 if ($textlib->strlen($searchterm) < 2) {
185 continue;
186 }
187 $searchterm = preg_quote($searchterm, '|');
188 $searchcond[] = "$concat $NOTREGEXP :ss$i";
189 $params['ss'.$i] = "(^|[^a-zA-Z0-9])$searchterm([^a-zA-Z0-9]|$)";
190
74a27855 191 } else {
ae8c3566 192 if ($textlib->strlen($searchterm) < 2) {
193 continue;
194 }
47586394 195 $searchcond[] = $DB->sql_like($concat, ":ss$i", false, true, $NOT);
ae8c3566 196 $params['ss'.$i] = "%$searchterm%";
69baafde 197 }
69baafde 198 }
74a27855 199
ae8c3566 200 if (empty($searchcond)) {
e99cfeb8 201 $where = "AND 1=2 "; // no search result
74a27855 202
4f3e59c3 203 } else {
ae8c3566 204 $searchcond = implode(" AND ", $searchcond);
205
206 $sqlselect = "SELECT DISTINCT ge.*, ge.concept AS glossarypivot";
207 $sqlfrom = "FROM {glossary_entries} ge
208 LEFT JOIN {glossary_alias} al ON al.entryid = ge.id";
209 $where = "AND ($searchcond)";
4f3e59c3 210 }
74a27855 211
4f3e59c3 212 break;
ae8c3566 213
214 case 'term':
215 $params['hook2'] = $hook;
4f3e59c3 216 $printpivot = 0;
ae8c3566 217 $sqlfrom .= " LEFT JOIN {glossary_alias} ga on ge.id = ga.entryid";
218 $where = "AND (ge.concept = :hook OR ga.alias = :hook2) ";
4f3e59c3 219 break;
220
ae8c3566 221 case 'entry':
4f3e59c3 222 $printpivot = 0;
ae8c3566 223 $where = "AND ge.id = :hook";
4f3e59c3 224 break;
225
ae8c3566 226 case 'letter':
4f3e59c3 227 if ($hook != 'ALL' and $hook != 'SPECIAL') {
ae8c3566 228 $params['hookup'] = $textlib->strtoupper($hook);
655bbf51 229 $where = "AND " . $DB->sql_substr("upper(concept)", 1, $textlib->strlen($hook)) . " = :hookup";
4f3e59c3 230 }
539f698d 231 if ($hook == 'SPECIAL') {
232 //Create appropiate IN contents
568d0b6d 233 $alphabet = explode(",", get_string('alphabet', 'langconfig'));
ae8c3566 234 list($nia, $aparams) = $DB->get_in_or_equal($alphabet, SQL_PARAMS_NAMED, $start='a0', false);
235 $params = array_merge($params, $aparams);
655bbf51 236 $where = "AND " . $DB->sql_substr("upper(concept)", 1, 1) . " $nia";
539f698d 237 }
4f3e59c3 238 break;
239 }
ae8c3566 240
241 $sqlwhere = "WHERE (ge.glossaryid = :gid1 or ge.sourceglossaryid = :gid2) AND
242 (ge.approved <> 0 $userid)
4f3e59c3 243 $where";
244 switch ( $tab ) {
ae8c3566 245 case GLOSSARY_DATE_VIEW:
db87439a 246 $sqlorderby = "ORDER BY $sqlsortkey $sqlsortorder";
4f3e59c3 247 break;
ae8c3566 248
249 case GLOSSARY_STANDARD_VIEW:
4f3e59c3 250 $sqlorderby = "ORDER BY ge.concept";
251 default:
252 break;
253 }
254 break;
ae8c3566 255 }
256 $count = $DB->count_records_sql("SELECT COUNT(DISTINCT(ge.id)) $sqlfrom $sqlwhere", $params);
465e3ae3 257
0217757b 258 $limitfrom = $offset;
259 $limitnum = 0;
ae8c3566 260
4f3e59c3 261 if ( $offset >= 0 ) {
0217757b 262 $limitnum = $entriesbypage;
4f3e59c3 263 }
8451166c 264
ae8c3566 265 $allentries = $DB->get_records_sql("$sqlselect $sqlfrom $sqlwhere $sqlorderby", $params, $limitfrom, $limitnum);
1adbd2c3 266