Yields the SQL syntax of the query, which can be passed to Database.execute(). The SQL string will be cached for faster reuse.
(self)
| 1207 | return self.rows()[i] |
| 1208 | |
| 1209 | def SQL(self): |
| 1210 | """ Yields the SQL syntax of the query, which can be passed to Database.execute(). |
| 1211 | The SQL string will be cached for faster reuse. |
| 1212 | """ |
| 1213 | #if self._id in Query.cache: |
| 1214 | # return Query.cache[self._id] |
| 1215 | # Construct the SELECT clause from Query.fields. |
| 1216 | g = not isinstance(self.group, (list, tuple)) and [self.group] or self.group |
| 1217 | g = [abs(self._table.name, f) for f in g if f is not None] |
| 1218 | fields = not isinstance(self.fields, (list, tuple)) and [self.fields] or self.fields |
| 1219 | fields = [f in self.aliases and "%s as %s" % (f, self.aliases[f]) or f for f in fields] |
| 1220 | fields = abs(self._table.name, fields) |
| 1221 | # With a GROUPY BY clause, fields not used for grouping are wrapped in the given function. |
| 1222 | # The function can also be a list of functions for each field (FIRST by default). |
| 1223 | if g and isinstance(self.function, basestring): |
| 1224 | fields = [f in g and f or "%s(%s)" % (self.function, f) for f in fields] |
| 1225 | if g and isinstance(self.function, (list, tuple)): |
| 1226 | fields = [f in g and f or "%s(%s)" % (F,f) for F,f in zip(self.function+[FIRST]*len(fields), fields)] |
| 1227 | q = [] |
| 1228 | q.append("select %s" % ", ".join(fields)) |
| 1229 | # Construct the FROM clause from Query.relations. |
| 1230 | # Table relations defined on the database are taken into account, |
| 1231 | # but overridden by relations defined on the query. |
| 1232 | q.append("from `%s`" % self._table.name) |
| 1233 | relations = {} |
| 1234 | for key1, key2, table, join in (relation(*r) for r in self.relations): |
| 1235 | table = isinstance(table, Table) and table.name or table |
| 1236 | relations[table] = (key1, key2, join) |
| 1237 | for table1, key1, table2, key2, join in self._table.db.relations: |
| 1238 | if table1 == self._table.name: |
| 1239 | relations.setdefault(table2, (key1, key2, join)) |
| 1240 | if table2 == self._table.name: |
| 1241 | relations.setdefault(table1, (key1, key2, join==LEFT and RIGHT or (join==RIGHT and LEFT or join))) |
| 1242 | # Define relations only for tables whose fields are actually selected. |
| 1243 | for (table, (key1, key2, join)) in relations.items(): |
| 1244 | for f in fields: |
| 1245 | if table + "." in f: |
| 1246 | q.append("%sjoin `%s`" % (join and join+" " or "", table)) |
| 1247 | q.append("on %s=%s" % (abs(self._table.name, key1), abs(self._table.db[table].name, key2))) |
| 1248 | break |
| 1249 | # Construct the WHERE clause from Query.filters.SQL(). |
| 1250 | # Use the database's escape function and absolute field names. |
| 1251 | if len(self.filters) > 0: |
| 1252 | q.append("where %s" % self.filters.SQL(escape=self._table.db.escape, table=self._table.name)) |
| 1253 | # Construct the ORDER BY clause from Query.sort and Query.order. |
| 1254 | # Construct the GROUP BY clause from Query.group. |
| 1255 | for clause, value in (("order", self.sort), ("group", self.group)): |
| 1256 | if isinstance(value, basestring) and value != "": |
| 1257 | q.append("%s by %s" % (clause, abs(self._table.name, value))) |
| 1258 | elif isinstance(value, (list, tuple)) and len(value) > 0: |
| 1259 | q.append("%s by %s" % (clause, ", ".join(abs(self._table.name, value)))) |
| 1260 | elif isinstance(value, int): |
| 1261 | q.append("%s by %s" % (clause, abs(self._table.name, self._table.fields[value]))) |
| 1262 | if self.sort and clause == "order": |
| 1263 | if self.order in (ASCENDING, DESCENDING): |
| 1264 | q.append("%s" % self.order) |
| 1265 | elif isinstance(self.order, (list, tuple)): |
| 1266 | q[-1] = ",".join(" ".join(v) for v in zip(q[-1].split(","), self.order)) |