Repository: kayak/pypika
Pull request #21 (2016-10-24 14:20:39)

Added syntax for limits and offset
Added syntax for setting limits and offsets.

```
    def test_select_with_limit(self):
        q1 = Query.from_('abc').select('foo')[:10]

        self.assertEqual('SELECT "foo" FROM "abc" LIMIT 10', str(q1))

    def test_select_with_limit__func(self):
        q1 = Query.from_('abc').select('foo').limit(10)

        self.assertEqual('SELECT "foo" FROM "abc" LIMIT 10', str(q1))

    def test_select_with_offset(self):
        q1 = Query.from_('abc').select('foo')[10:]

        self.assertEqual('SELECT "foo" FROM "abc" OFFSET 10', str(q1))

    def test_select_with_offset__func(self):
        q1 = Query.from_('abc').select('foo').offset(10)

        self.assertEqual('SELECT "foo" FROM "abc" OFFSET 10', str(q1))

    def test_select_with_limit_and_offset(self):
        q1 = Query.from_('abc').select('foo')[10:10]

        self.assertEqual('SELECT "foo" FROM "abc" OFFSET 10 LIMIT 10', str(q1))
```


Patch:
diff --git a/pypika/queries.py b/pypika/queries.py
index dbf8a3c..6afa498 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -168,6 +168,9 @@ class QueryBuilder(Selectable, Term):
         self._joins = []
         self._unions = []
 
+        self._limit = None
+        self._offset = None
+
         self._select_star = False
         self._select_star_tables = set()
         self._mysql_rollup = False
@@ -319,6 +322,14 @@ class QueryBuilder(Selectable, Term):
 
         raise ValueError("Cannot join on type '%s'" % type(item))
 
+    @builder
+    def limit(self, limit):
+        self._limit = limit
+
+    @builder
+    def offset(self, offset):
+        self._offset = offset
+
     @builder
     def union(self, other):
         self._unions.append((UnionType.distinct, other))
@@ -333,6 +344,13 @@ class QueryBuilder(Selectable, Term):
     def __mul__(self, other):
         return self.union_all(other)
 
+    @builder
+    def __getitem__(self, item):
+        if not isinstance(item, slice):
+            raise TypeError("Query' object is not subscriptable")
+        self._offset = item.start
+        self._limit = item.stop
+
     @staticmethod
     def _list_aliases(field_set):
         return [field.alias or field.get_sql(with_quotes=False)
@@ -498,6 +516,12 @@ class QueryBuilder(Selectable, Term):
         if self._orderbys:
             querystring += self._orderby_sql(**kwargs)
 
+        if self._offset:
+            querystring += self._offset_sql()
+
+        if self._limit:
+            querystring += self._limit_sql()
+
         if subquery:
             querystring = '({query})'.format(
                 query=querystring,
@@ -606,6 +630,12 @@ class QueryBuilder(Selectable, Term):
                 )
         return unionstring
 
+    def _offset_sql(self):
+        return " OFFSET {offset}".format(offset=self._offset)
+
+    def _limit_sql(self):
+        return " LIMIT {limit}".format(limit=self._limit)
+
 
 class Joiner(object):
     def __init__(self, query, item, how, type_label):


Test patch:
diff --git a/pypika/tests/test_selects.py b/pypika/tests/test_selects.py
index 5012a6f..457e365 100644
--- a/pypika/tests/test_selects.py
+++ b/pypika/tests/test_selects.py
@@ -88,6 +88,31 @@ class SelectTests(unittest.TestCase):
         with self.assertRaises(AttributeError):
             Query.select(1).from_('abc').from_('efg')
 
+    def test_select_with_limit(self):
+        q1 = Query.from_('abc').select('foo')[:10]
+
+        self.assertEqual('SELECT "foo" FROM "abc" LIMIT 10', str(q1))
+
+    def test_select_with_limit__func(self):
+        q1 = Query.from_('abc').select('foo').limit(10)
+
+        self.assertEqual('SELECT "foo" FROM "abc" LIMIT 10', str(q1))
+
+    def test_select_with_offset(self):
+        q1 = Query.from_('abc').select('foo')[10:]
+
+        self.assertEqual('SELECT "foo" FROM "abc" OFFSET 10', str(q1))
+
+    def test_select_with_offset__func(self):
+        q1 = Query.from_('abc').select('foo').offset(10)
+
+        self.assertEqual('SELECT "foo" FROM "abc" OFFSET 10', str(q1))
+
+    def test_select_with_limit_and_offset(self):
+        q1 = Query.from_('abc').select('foo')[10:10]
+
+        self.assertEqual('SELECT "foo" FROM "abc" OFFSET 10 LIMIT 10', str(q1))
+
 
 class WhereTests(unittest.TestCase):
     t = Table('abc')