Build a SQL Query BuilderStep 3 of 5

Order By and Limit

12345

Step 3 - Order By and Limit

Two more optional clauses complete a basic query: ORDER BY sorts the rows and LIMIT caps how many come back. Each is a single value, so each setter simply overwrites its slot.

Implement two methods:

  • order_by(col) stores the sort column and returns self
  • limit(n) stores the row cap and returns self

The build method already appends the clauses in a fixed order after WHERE: first a space, ORDER BY, and the column when a sort column is set; then a space, LIMIT, and the number when a limit is set. The number is converted to text with str.

Hints
Starter code
class QueryBuilder:
    def __init__(self):
        self.columns = []
        self.table = None
        self.wheres = []
        self.order = None
        self._limit = None

    def select(self, *cols):
        self.columns = list(cols)
        return self

    def from_(self, table):
        self.table = table
        return self

    def where(self, condition):
        self.wheres.append(condition)
        return self

    def order_by(self, col):
        """Set the ORDER BY column and return self."""
        # TODO: remember the sort column in self.order, overwriting whatever was
        #   there before, and hand back the builder itself for chaining.
        pass

    def limit(self, n):
        """Set the LIMIT value and return self."""
        # TODO: remember the row cap in self._limit and hand back the builder
        #   itself. Store the value exactly as given: a cap of zero is a real
        #   cap, and build checks the slot against None rather than falseness.
        pass

    def build(self):
        cols = ", ".join(self.columns) if self.columns else "*"
        sql = "SELECT " + cols + " FROM " + self.table
        if self.wheres:
            sql += " WHERE " + " AND ".join(self.wheres)
        if self.order is not None:
            sql += " ORDER BY " + self.order
        if self._limit is not None:
            sql += " LIMIT " + str(self._limit)
        return sql
Write it on a laptop

The editor and the test runner need a wide screen. Open this page on a desktop browser and your progress will be waiting.

PreviousNext step