Repository: kayak/pypika
Pull request #742 (2023-08-16 03:51:36)

fix `Field.__init__`
According to the signature of `Field.__init__`, the argument `table` can be of type `str`. Therefore, we should also check the type and ensure that `self.table` is assigned a `Table` object.

WH-2099: @AzisK Sorry for the delay. I've finished the test case.

Patch:
diff --git a/README.rst b/README.rst
index f36f34b..31bb94f 100644
--- a/README.rst
+++ b/README.rst
@@ -1368,6 +1368,58 @@ This produces:
 
     DROP INDEX IF EXISTS my_index
 
+
+Chaining Functions
+^^^^^^^^^^^^^^^^^^
+
+The ``QueryBuilder.pipe`` method gives a more readable alternative while chaining functions.
+
+.. code-block:: python 
+
+    # This 
+    (
+        query
+        .pipe(func1, *args)
+        .pipe(func2, **kwargs)
+        .pipe(func3)
+    )
+
+    # Is equivalent to this
+    func3(func2(func1(query, *args), **kwargs))
+
+Or for a more concrete example:
+
+.. code-block:: python 
+
+    from pypika import Field, Query, functions as fn
+    from pypika.queries import QueryBuilder
+
+    def filter_days(query: QueryBuilder, col, num_days: int) -> QueryBuilder: 
+        if isinstance(col, str): 
+            col = Field(col)
+
+        return query.where(col > fn.Now() - num_days)
+
+    def count_groups(query: QueryBuilder, *groups) -> QueryBuilder: 
+        return query.groupby(*groups).select(*groups, fn.Count("*").as_("n_rows"))
+
+    base_query = Query.from_("table")
+
+    query = (
+        base_query
+        .pipe(filter_days, "date", num_days=7)
+        .pipe(count_groups, "col1", "col2")
+    )
+
+This produces: 
+
+.. code-block:: sql
+
+    SELECT "col1","col2",COUNT(*) n_rows 
+    FROM "table" 
+    WHERE "date">NOW()-7 
+    GROUP BY "col1","col2"
+
 .. _tutorial_end:
 
 .. _contributing_start: 
diff --git a/pypika/dialects.py b/pypika/dialects.py
index 8894e56..7653577 100644
--- a/pypika/dialects.py
+++ b/pypika/dialects.py
@@ -1,4 +1,5 @@
 import itertools
+import warnings
 from copy import copy
 from typing import Any, Optional, Union, Tuple as TypedTuple
 
@@ -347,6 +348,19 @@ class VerticaCopyQueryBuilder:
         return self.get_sql()
 
 
+class FetchNextAndOffsetRowsQueryBuilder(QueryBuilder):
+    def _limit_sql(self) -> str:
+        return " FETCH NEXT {limit} ROWS ONLY".format(limit=self._limit)
+
+    def _offset_sql(self) -> str:
+        return " OFFSET {offset} ROWS".format(offset=self._offset or 0)
+
+    @builder
+    def fetch_next(self, limit: int):
+        warnings.warn("`fetch_next` is deprecated - please use the `limit` method", DeprecationWarning)
+        self._limit = limit
+
+
 class OracleQuery(Query):
     """
     Defines a query class for use with Oracle.
@@ -357,7 +371,7 @@ class OracleQuery(Query):
         return OracleQueryBuilder(**kwargs)
 
 
-class OracleQueryBuilder(QueryBuilder):
+class OracleQueryBuilder(FetchNextAndOffsetRowsQueryBuilder):
     QUOTE_CHAR = None
     QUERY_CLS = OracleQuery
 
@@ -370,6 +384,16 @@ class OracleQueryBuilder(QueryBuilder):
         kwargs['groupby_alias'] = False
         return super().get_sql(*args, **kwargs)
 
+    def _apply_pagination(self, querystring: str) -> str:
+        # Note: Overridden as Oracle specifies offset before the fetch next limit
+        if self._offset:
+            querystring += self._offset_sql()
+
+        if self._limit is not None:
+            querystring += self._limit_sql()
+
+        return querystring
+
 
 class PostgreSQLQuery(Query):
     """
@@ -670,7 +694,7 @@ class MSSQLQuery(Query):
         return MSSQLQueryBuilder(**kwargs)
 
 
-class MSSQLQueryBuilder(QueryBuilder):
+class MSSQLQueryBuilder(FetchNextAndOffsetRowsQueryBuilder):
     QUERY_CLS = MSSQLQuery
 
     def __init__(self, **kwargs: Any) -> None:
@@ -695,17 +719,6 @@ class MSSQLQueryBuilder(QueryBuilder):
         self._top_percent: bool = percent
         self._top_with_ties: bool = with_ties
 
-    @builder
-    def fetch_next(self, limit: int) -> "MSSQLQueryBuilder":
-        # Overridden to provide a more domain-specific API for T-SQL users
-        self._limit = limit
-
-    def _offset_sql(self) -> str:
-        return " OFFSET {offset} ROWS".format(offset=self._offset or 0)
-
-    def _limit_sql(self) -> str:
-        return " FETCH NEXT {limit} ROWS ONLY".format(limit=self._limit)
-
     def _apply_pagination(self, querystring: str) -> str:
         # Note: Overridden as MSSQL specifies offset before the fetch next limit
         if self._limit is not None or self._offset:
diff --git a/pypika/queries.py b/pypika/queries.py
index 223c3c9..c51c6b2 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -1560,6 +1560,54 @@ class QueryBuilder(Selectable, Term):
             )
         )
 
+    def pipe(self, func, *args, **kwargs):
+        """Call a function on the current object and return the result.
+
+        Example usage:
+
+        .. code-block:: python
+
+            from pypika import Query, functions as fn
+            from pypika.queries import QueryBuilder
+
+            def rows_by_group(query: QueryBuilder, *groups) -> QueryBuilder:
+                return (
+                    query
+                    .select(*groups, fn.Count("*").as_("n_rows"))
+                    .groupby(*groups)
+                )
+
+            base_query = Query.from_("table")
+
+            col1_agg = base_query.pipe(rows_by_group, "col1")
+            col2_agg = base_query.pipe(rows_by_group, "col2")
+            col1_col2_agg = base_query.pipe(rows_by_group, "col1", "col2")
+
+        Makes chaining functions together easier, especially when the functions are
+        defined elsewhere. For example, you could define a function that filters
+        rows by a date range and then group by a set of columns:
+
+
+        .. code-block:: python
+
+            from datetime import datetime, timedelta
+
+            from pypika import Field
+
+            def days_since(query: QueryBuilder, n_days: int) -> QueryBuilder:
+                return (
+                    query
+                    .where("date" > fn.Date(datetime.now().date() - timedelta(days=n_days)))
+                )
+
+            (
+                base_query
+                .pipe(days_since, n_days=7)
+                .pipe(rows_by_group, "col1", "col2")
+            )
+        """
+        return func(self, *args, **kwargs)
+
 
 class Joiner:
     def __init__(
diff --git a/pypika/terms.py b/pypika/terms.py
index b25af5c..ce7aed6 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -548,6 +548,10 @@ class Field(Criterion, JSON):
     ) -> None:
         super().__init__(alias=alias)
         self.name = name
+        if isinstance(table, str):
+            # avoid circular import at load time
+            from pypika.queries import Table
+            table = Table(table)
         self.table = table
 
     def nodes_(self) -> Iterator[NodeT]:


Test patch:
diff --git a/pypika/tests/dialects/test_mssql.py b/pypika/tests/dialects/test_mssql.py
index f8940b0..7cf53b2 100644
--- a/pypika/tests/dialects/test_mssql.py
+++ b/pypika/tests/dialects/test_mssql.py
@@ -53,18 +53,13 @@ class SelectTests(unittest.TestCase):
 
         self.assertEqual('SELECT "def" FROM "abc" ORDER BY "def" OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', str(q))
 
-    def test_fetch_next(self):
-        q = MSSQLQuery.from_("abc").select("def").orderby("def").fetch_next(10)
-
-        self.assertEqual('SELECT "def" FROM "abc" ORDER BY "def" OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', str(q))
-
     def test_offset(self):
         q = MSSQLQuery.from_("abc").select("def").orderby("def").offset(10)
 
         self.assertEqual('SELECT "def" FROM "abc" ORDER BY "def" OFFSET 10 ROWS', str(q))
 
-    def test_fetch_next_with_offset(self):
-        q = MSSQLQuery.from_("abc").select("def").orderby("def").fetch_next(10).offset(10)
+    def test_limit_with_offset(self):
+        q = MSSQLQuery.from_("abc").select("def").orderby("def").limit(10).offset(10)
 
         self.assertEqual('SELECT "def" FROM "abc" ORDER BY "def" OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY', str(q))
 
diff --git a/pypika/tests/dialects/test_oracle.py b/pypika/tests/dialects/test_oracle.py
index c3a757a..21b54f1 100644
--- a/pypika/tests/dialects/test_oracle.py
+++ b/pypika/tests/dialects/test_oracle.py
@@ -19,3 +19,33 @@ class SelectTests(unittest.TestCase):
         q = OracleQuery.from_(subquery).select(col, Count('*')).groupby(col)
 
         self.assertEqual('SELECT sq0.abc a,COUNT(\'*\') FROM (SELECT abc FROM table1) sq0 GROUP BY sq0.abc', str(q))
+
+    def test_limit_query(self):
+        t = Table('table1')
+        limit = 5
+        q = OracleQuery.from_(t).select(t.test).limit(limit)
+
+        self.assertEqual(f'SELECT test FROM table1 FETCH NEXT {limit} ROWS ONLY', str(q))
+
+    def test_offset_query(self):
+        t = Table('table1')
+        offset = 5
+        q = OracleQuery.from_(t).select(t.test).offset(offset)
+
+        self.assertEqual(f'SELECT test FROM table1 OFFSET {offset} ROWS', str(q))
+
+    def test_limit_offset_query(self):
+        t = Table('table1')
+        limit = 5
+        offset = 5
+        q = OracleQuery.from_(t).select(t.test).limit(limit).offset(offset)
+
+        self.assertEqual(f'SELECT test FROM table1 OFFSET {offset} ROWS FETCH NEXT {limit} ROWS ONLY', str(q))
+
+    def test_fetch_next_method_deprecated(self):
+        with self.assertWarns(DeprecationWarning):
+            t = Table('table1')
+            limit = 5
+            q = OracleQuery.from_(t).select(t.test).fetch_next(limit)
+
+            self.assertEqual(f'SELECT test FROM table1 FETCH NEXT {limit} ROWS ONLY', str(q))
diff --git a/pypika/tests/test_query.py b/pypika/tests/test_query.py
index 460b7d7..ae8dc01 100644
--- a/pypika/tests/test_query.py
+++ b/pypika/tests/test_query.py
@@ -1,6 +1,6 @@
 import unittest
 
-from pypika import Case, Query, Tables, Tuple, functions
+from pypika import Case, Query, Tables, Tuple, functions, Field
 from pypika.dialects import (
     ClickHouseQuery,
     ClickHouseQueryBuilder,
@@ -204,3 +204,39 @@ class QueryBuilderTests(unittest.TestCase):
 
         with self.subTest('OracleQueryBuilder'):
             self.assertEqual(OracleQuery, OracleQueryBuilder.QUERY_CLS)
+
+    def test_pipe(self) -> None:
+        base_query = Query.from_("test")
+
+        def select(query: QueryBuilder) -> QueryBuilder:
+            return query.select("test1", "test2")
+
+        def count_group(query: QueryBuilder, *groups) -> QueryBuilder:
+            return query.groupby(*groups).select(*groups, functions.Count("*"))
+
+        for func, args, kwargs, expected_str in [
+            (select, [], {}, 'SELECT "test1","test2" FROM "test"'),
+            (
+                count_group,
+                ["test1", "test2"],
+                {},
+                'SELECT "test1","test2",COUNT(*) FROM "test" GROUP BY "test1","test2"',
+            ),
+            (count_group, ["test1"], {}, 'SELECT "test1",COUNT(*) FROM "test" GROUP BY "test1"'),
+        ]:
+            result_str = str(base_query.pipe(func, *args, **kwargs))
+            self.assertEqual(result_str, str(func(base_query, *args, **kwargs)))
+            self.assertEqual(result_str, expected_str)
+
+        def where_clause(query: QueryBuilder, num_days: int) -> QueryBuilder:
+            return query.where(Field("date") > functions.Now() - num_days)
+
+        result_str = str(base_query.pipe(select).pipe(where_clause, num_days=1))
+        self.assertEqual(
+            result_str,
+            str(select(where_clause(base_query, num_days=1))),
+        )
+        self.assertEqual(
+            result_str,
+            'SELECT "test1","test2" FROM "test" WHERE "date">NOW()-1',
+        )
diff --git a/pypika/tests/test_terms.py b/pypika/tests/test_terms.py
index 562e0d2..607c4c0 100644
--- a/pypika/tests/test_terms.py
+++ b/pypika/tests/test_terms.py
@@ -15,6 +15,13 @@ class FieldAliasTests(TestCase):
         self.assertEqual('bar', str(c1.alias))
 
 
+class FieldInitTests(TestCase):
+    def test_init_with_str_table(self):
+        test_table_name = "test_table"
+        field = Field(name="name", table=test_table_name)
+        self.assertEqual(field.table, Table(name=test_table_name))
+    
+
 class FieldHashingTests(TestCase):
     def test_tabled_eq_fields_equally_hashed(self):
         client_name1 = Field(name="name", table=Table("clients"))