Add Query.one_or_none()pr/201

4 files changed, 96 insertions, 1 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index 09800bea8..5c5ffed1d 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -90,6 +90,10 @@
         to again work as it is based on an 8.0.x version of Postgresql.
         Fix courtesy Pete Hollobon.
 
+    .. change::
+        :tags: orm
+
+        Add Query.one_or_none() method.
 
 .. changelog::
     :version: 1.0.8
diff --git a/doc/build/orm/tutorial.rst b/doc/build/orm/tutorial.rst
index 6958e15d8..ed8d05534 100644
--- a/doc/build/orm/tutorial.rst
+++ b/doc/build/orm/tutorial.rst
@@ -849,7 +849,7 @@ database results.  Here's a brief tour:
       ('%ed', 1, 0)
       {stop}<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>
 
-* :meth:`~.Query.one()`, fully fetches all rows, and if not
+* :meth:`~.Query.one()` fully fetches all rows, and if not
   exactly one object identity or composite row is present in the result, raises
   an error.  With multiple rows found:
 
@@ -892,6 +892,11 @@ database results.  Here's a brief tour:
   web service, which may want to raise a "404 not found" when no results are found,
   but raise an application error when multiple results are found.
 
+* :meth:`~.Query.one_or_none` is like :meth:`~.Query.one`, except that if no
+  results are found, it doesn't raise an error; it just returns ``None``. Like
+  :meth:`~.Query.one`, however, it does raise an error if multiple results are
+  found.
+
 * :meth:`~.Query.scalar` invokes the :meth:`~.Query.one` method, and upon
   success returns the first column of the row:
 
diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 7e77c66a8..8c4925905 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -2473,6 +2473,38 @@ class Query(object):
         else:
             return None
 
+    def one_or_none(self):
+        """Return at most one result or raise an exception.
+
+        Returns ``None`` if the query selects
+        no rows.  Raises ``sqlalchemy.orm.exc.MultipleResultsFound``
+        if multiple object identities are returned, or if multiple
+        rows are returned for a query that does not return object
+        identities.
+
+        Note that an entity query, that is, one which selects one or
+        more mapped classes as opposed to individual column attributes,
+        may ultimately represent many rows but only one row of
+        unique entity or entities - this is a successful result for
+        `one_or_none()`.
+
+        Calling ``one_or_none()`` results in an execution of the underlying
+        query.
+
+        .. versionchanged:: 1.0.9
+            Added ``one_or_none()``
+        """
+        ret = list(self)
+
+        l = len(ret)
+        if l == 1:
+            return ret[0]
+        elif l == 0:
+            return None
+        else:
+            raise orm_exc.MultipleResultsFound(
+                "Multiple rows were found for one_or_none()")
+
     def one(self):
         """Return exactly one result or raise an exception.
 
diff --git a/test/orm/test_query.py b/test/orm/test_query.py
index b0501739f..4ae0b010a 100644
--- a/test/orm/test_query.py
+++ b/test/orm/test_query.py
@@ -3675,6 +3675,60 @@ class ImmediateTest(_fixtures.FixtureTest):
             sess.query(User).join(User.addresses).filter(User.id.in_([8, 9])).
             order_by(User.id).one)
 
+    def test_one_or_none(self):
+        User, Address = self.classes.User, self.classes.Address
+
+        sess = create_session()
+
+        eq_(sess.query(User).filter(User.id == 99).one_or_none(), None)
+
+        eq_(sess.query(User).filter(User.id == 7).one_or_none().id, 7)
+
+        assert_raises_message(
+            sa.orm.exc.MultipleResultsFound,
+            "Multiple rows were found for one_or_none\(\)",
+            sess.query(User).one_or_none)
+
+        eq_(sess.query(User.id, User.name).filter(User.id == 99).one_or_none(), None)
+
+        eq_(sess.query(User.id, User.name).filter(User.id == 7).one_or_none(),
+            (7, 'jack'))
+
+        assert_raises(
+            sa.orm.exc.MultipleResultsFound,
+            sess.query(User.id, User.name).one_or_none)
+
+        eq_(
+            (sess.query(User, Address).join(User.addresses).
+           filter(Address.id == 99)).one_or_none(), None)
+
+        eq_((sess.query(User, Address).
+            join(User.addresses).
+            filter(Address.id == 4)).one_or_none(),
+           (User(id=8), Address(id=4)))
+
+        assert_raises(
+            sa.orm.exc.MultipleResultsFound,
+            sess.query(User, Address).join(User.addresses).one_or_none)
+
+        # this result returns multiple rows, the first
+        # two rows being the same.  but uniquing is
+        # not applied for a column based result.
+        assert_raises(
+            sa.orm.exc.MultipleResultsFound,
+            sess.query(User.id).join(User.addresses).
+            filter(User.id.in_([8, 9])).order_by(User.id).one_or_none)
+
+        # test that a join which ultimately returns
+        # multiple identities across many rows still
+        # raises, even though the first two rows are of
+        # the same identity and unique filtering
+        # is applied ([ticket:1688])
+        assert_raises(
+            sa.orm.exc.MultipleResultsFound,
+            sess.query(User).join(User.addresses).filter(User.id.in_([8, 9])).
+            order_by(User.id).one_or_none)
+
     @testing.future
     def test_getslice(self):
         assert False