query-builder.txt

.. _laravel-query-builder:

=============
Query Builder
=============

.. facet::
   :name: genre
   :values: tutorial

.. meta::
   :keywords: code example, aggregation

.. contents:: On this page
   :local:
   :backlinks: none
   :depth: 2
   :class: singlecol

Overview
--------

In this guide, you can learn how to use the {+odm-short+} extension of
the Laravel query builder to work with a MongoDB database. The query builder
lets you use a single syntax and fluent interface to write queries for any
supported database.

.. note::

   The {+odm-short+} extends Laravel's query builder and Eloquent ORM, which can
   run similar database operations. To learn more about retrieving documents
   by using Eloquent models, see :ref:`laravel-fundamentals-retrieve`.

Laravel provides a **facade** to access the query builder class ``DB``, which
lets you perform database operations. Facades, which are static interfaces to
classes, make the syntax more concise, avoid runtime errors, and improve
testability.

The {+odm-short+} provides the ``DB`` method ``table()`` to access a collection.
Chain methods to specify commands and any constraints. Then, chain
the ``get()`` method at the end to run the methods and retrieve the
results. To retrieve only the first matching result, chain the
``first()`` method instead of the ``get()`` method. Starting in
{+odm-long+} v5.0, the query builder returns results as ``stdClass`` objects.

The following example shows the syntax of a query builder call:

.. code-block:: php

   DB::table('<collection name>')
       // chain methods by using the "->" object operator
       ->get();

.. tip:: Set Database Connection

   Before using the ``DB::table()`` method, ensure that you specify
   MongoDB as your application's default database connection. For
   instructions on setting the database connection, see the
   :ref:`laravel-quick-start-connect-to-mongodb` step in the Quick
   Start.

   If MongoDB is not your application's default database, you can use
   the ``DB::connection()`` method to specify a MongoDB connection. Pass
   the name of the connection to the ``connection()`` method, as shown
   in the following code:

   .. code-block:: php

      $connection = DB::connection('mongodb');

This guide provides examples of the following types of query builder operations:

- :ref:`laravel-retrieve-query-builder`
- :ref:`laravel-options-query-builder`
- :ref:`laravel-modify-results-query-builder`
- :ref:`laravel-mongodb-read-query-builder`
- :ref:`laravel-mongodb-write-query-builder`

Before You Get Started
----------------------

To run the code examples in this guide, complete the
:ref:`Quick Start <laravel-quick-start>` tutorial to configure a web
application, load sample datasets into your MongoDB deployment, and
run the example code from a controller method. To see the expected code
output as JSON documents, use the ``toJson()`` method shown in the optional
:ref:`View your results as JSON documents <laravel-quick-start-json>` step
of the Quick Start.

To perform read and write operations by using the query builder, import the
``Illuminate\Support\Facades\DB`` facade and compose your query.

.. _laravel-retrieve-query-builder:

Retrieve Matching Documents
---------------------------

This section includes query builder examples for read operations in the
following operator categories:

- :ref:`Where method <laravel-query-builder-where-example>`
- :ref:`Logical conditionals <laravel-query-builder-logical-operations>`
- :ref:`Ranges and type checks <laravel-query-builder-range-type>`
- :ref:`Pattern searches <laravel-query-builder-pattern>`
- :ref:`Retrieve distinct values <laravel-query-builder-distinct>`
- :ref:`Aggregations <laravel-query-builder-aggregations>`

.. _laravel-query-builder-where-example:

Where Method Example
~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``where()`` query
builder method to retrieve documents from the ``movies`` collection
that contain an ``imdb.rating`` field value of exactly ``9.3``. Click the
:guilabel:`{+code-output-label+}` button to see the results returned
by the query:

.. io-code-block::
   :copyable: true

   .. input:: /includes/query-builder/QueryBuilderTest.php
      :language: php
      :dedent:
      :start-after: begin query where
      :end-before: end query where

   .. output::
      :language: json
      :visible: false

      [
         { "title": "Cosmos",
           "year": 1980,
           "runtime": 60,
           "imdb": {
             "rating": 9.3,
             "votes": 17174,
             "id": 81846
           },
           "plot": "Astronomer Carl Sagan leads us on an engaging guided tour of the various elements and cosmological theories of the universe.",
           ...
         },
         { "title": "The Shawshank Redemption",
           "year": 1994,
           "runtime": 142,
           "imdb": {
             "rating": 9.3,
             "votes": 1521105,
             "id": 111161
           },
           "plot": "Two imprisoned men bond over a number of years, finding solace and eventual redemption through acts of common decency.",
           ...
         },
         { "title": "The Real Miyagi",
           "year": 2015,
           "runtime": 90,
           "imdb": {
             "rating": 9.3,
             "votes": 41,
             "id": 2313306
           },
           "plot": "The life of the greatest karate master of a generation.",
            ...
         }
      ]

.. _laravel-query-builder-logical-operations:

Logical Conditional Operations
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The examples in this section show the query builder syntax you
can use to perform the following logical conditional operations:

- :ref:`Logical OR to match one or more conditions <laravel-query-builder-logical-or>`
- :ref:`Logical AND to match all conditions <laravel-query-builder-logical-and>`
- :ref:`Logical NOT to match the negation of the condition <laravel-query-builder-logical-not>`
- :ref:`Nested logical operator groups <laravel-query-builder-logical-nested>`

.. _laravel-query-builder-logical-or:

Logical OR Example
^^^^^^^^^^^^^^^^^^

The following example shows how to chain the ``orWhere()``
query builder method to retrieve documents from the
``movies`` collection in which the value of the ``_id``
field is ``ObjectId('573a1398f29313caabce9682')`` or
the value of the ``title`` field is ``"Back to the Future"``:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query orWhere
   :end-before: end query orWhere

.. note::

   You can use the ``id`` alias in your queries to represent the
   ``_id`` field in MongoDB documents, as shown in the preceding
   code. When you use the query builder to run a find operation, the {+odm-short+}
   automatically converts between ``_id`` and ``id`` field names. In query results,
   the ``_id`` field is presented as ``id``. This provides better
   consistency with Laravel, as the framework assumes that each record has a
   primary key named ``id`` by default.
   
   Because of this behavior, you cannot have two separate ``id`` and ``_id``
   fields in your documents.

.. _laravel-query-builder-logical-and:

Logical AND Example
^^^^^^^^^^^^^^^^^^^

The following example shows how to chain the ``where()``
query builder method to retrieve documents from the
``movies`` collection that match both an ``imdb.rating``
value greater than ``8.5`` and a ``year`` value of less than
``1940``:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query andWhere
   :end-before: end query andWhere

.. tip::

   For compatibility with Laravel, Laravel MongoDB v5.1 supports both arrow
   (``->``) and dot (``.``) notation to access nested fields in a query
   filter. The preceding example uses dot notation to query the ``imdb.rating``
   nested field, which is the recommended syntax.

.. _laravel-query-builder-logical-not:

Logical NOT Example
^^^^^^^^^^^^^^^^^^^

The following example shows how to call the ``whereNot()``
query builder method to retrieve documents from the
``movies`` collection that match documents that do not have an ``imdb.rating``
value greater than ``2``. This is equivalent to matching all documents
that have an ``imdb.rating`` of less than or equal to ``2``:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query whereNot
   :end-before: end query whereNot

.. _laravel-query-builder-logical-nested:

Nested Logical Operator Group Example
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The following example shows how to chain the ``where()``
query builder method to retrieve documents from the
``movies`` collection that match both of the following
conditions. This example passes a closure as the first
parameter of the ``where()`` query builder method to group
the logical OR group:

- ``imdb.rating`` value is greater than ``8.5``
- ``year`` value is either ``1986`` or ``1996``

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query nestedLogical
   :end-before: end query nestedLogical

.. _laravel-query-builder-range-type:

Ranges and Type Checks
~~~~~~~~~~~~~~~~~~~~~~

The examples in this section show the query builder syntax you can use to
match values by using the following range queries and type check operations:

- :ref:`Values within a numerical range <laravel-query-builder-wherebetween>`
- :ref:`Null or missing values <laravel-query-builder-null>`
- :ref:`One or more values of a set <laravel-query-builder-wherein>`
- :ref:`Match dates <laravel-query-builder-wheredate>`
- :ref:`Match a text pattern <laravel-query-builder-pattern>`

.. _laravel-query-builder-wherebetween:

Numerical Range Example
^^^^^^^^^^^^^^^^^^^^^^^

The following example shows how to use the ``whereBetween()``
query builder method to retrieve documents from the
``movies`` collection that contain an ``imdb.rating`` value
between ``9`` and ``9.5``:

.. io-code-block::
   :copyable: true

   .. input:: /includes/query-builder/QueryBuilderTest.php
      :language: php
      :dedent:
      :start-after: begin query whereBetween
      :end-before: end query whereBetween

   .. output::
      :language: none
      :visible: false

      [
         { "title" "The Godfather", "imdb": { "rating": 9.2, "votes": 1038358, "id": 68646 }, ... },
         { "title": "Hollywood", "imdb": { "rating": 9.1, "votes": 511,"id": 80230 }, ... },
         { "title": "Cosmos", "imdb": { "rating": 9.3, "votes": 17174, "id": 81846 }, ... },
         ...
      ]

.. _laravel-query-builder-null:

Null or Missing Values Example
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The following example shows how to use the ``whereNull()``
query builder method to retrieve documents from the
``movies`` collection that omit a ``runtime`` value
or field:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query whereNull
   :end-before: end query whereNull

.. _laravel-query-builder-wherein:

One or More Values of a Set Example
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The following example shows how to use the ``whereIn()``
query builder method to retrieve documents from the
``movies`` collection that match at least one of the
``title`` values in the specified set:

.. io-code-block::
   :copyable: true

   .. input:: /includes/query-builder/QueryBuilderTest.php
      :language: php
      :dedent:
      :start-after: begin query whereIn
      :end-before: end query whereIn

   .. output::
      :language: json
      :visible: false

      [
         { "title": "Toy Story", "year": 1995, "runtime": 81, ... },
         { "title": "Johnny English", "year": 2003, "runtime": 87, ... },
         { "title": "Shrek 2", "year" 2004, "runtime": 93, ... },
         ...
      ]

.. _laravel-query-builder-wheredate:

Match Dates Example
^^^^^^^^^^^^^^^^^^^

The following example shows how to use the ``where()``
query builder method to retrieve documents from the
``movies`` collection in which the ``released`` value
is January 15, 2010, specified in a ``Carbon`` object:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query where date
   :end-before: end query where date

.. note:: Date Query Filter and Result Type

   Starting in {+odm-long+} v5.0, `Carbon <https://carbon.nesbot.com/docs/>`__
   objects passed as query filters, as shown in the preceding code, are
   converted to ``UTCDateTime`` BSON values.
   
   In query results, ``UTCDateTime`` BSON values in MongoDB are returned as ``Carbon``
   objects. The {+odm-short+} applies the default timezone when performing
   this conversion.

If you want to represent a date as a string in your query filter
rather than as a ``Carbon`` object, use the ``whereDate()`` query
builder method. The following example retrieves documents from
the ``movies`` collection in which the ``released`` value
is January 15, 2010 and specifies the date as a string:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query whereDate string
   :end-before: end query whereDate string

.. _laravel-query-builder-pattern:

Text Pattern Match Example
~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``like`` query operator
with the ``where()`` query builder method to retrieve documents from the
``movies`` collection by using a specified text pattern.

Text patterns can contain text mixed with the following
wildcard characters:

- ``%`` which matches zero or more characters
- ``_`` which matches a single character

.. io-code-block::
   :copyable: true

   .. input:: /includes/query-builder/QueryBuilderTest.php
      :language: php
      :dedent:
      :start-after: begin query like
      :end-before: end query like

   .. output::
      :language: json
      :visible: false

      [
         { "title": "Kiss of the Spider Woman", ... },
         { "title": "Spider-Man", ... },
         { "title": "Spider-Man 2", ...},
         ...
      ]

whereLike() and whereNotLike() Methods
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

The following methods provide the same functionality as using the
:ref:`like <laravel-query-builder-pattern>` query operator to match
patterns:

- ``whereLike()``: Matches a specified pattern. By default, this method
  performs a case-insensitive match. You can enable case-sensitivity by
  passing ``true`` as the last parameter to the method.
- ``whereNotLike()``: Matches documents in which the field
  value does not contain the specified string pattern.

The following example shows how to use the ``whereLike()`` method to
match documents in which the ``title`` field has a value that matches the
pattern ``'Start%'`` with case-sensitivity enabled:

.. io-code-block::
   :copyable: true

   .. input:: /includes/query-builder/QueryBuilderTest.php
      :language: php
      :dedent:
      :start-after: begin query whereLike
      :end-before: end query whereLike

   .. output::
      :language: json
      :visible: false

      [
         { "title": "Start-Up", ... },
         { "title": "Start the Revolution Without Me", ... },
         ...
      ]

.. _laravel-query-builder-distinct:

Retrieve Distinct Values
~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``distinct()``
query builder method to retrieve all the different values
of the ``year`` field for documents in the ``movies`` collections.

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query distinct
   :end-before: end query distinct

.. _laravel-query-builder-aggregations:

Aggregations
------------

The examples in this section show the query builder syntax you
can use to perform **aggregations**. Aggregations are operations
that compute values from a set of query result data. You can use
aggregations to compute and return the following information:

- :ref:`Results grouped by common field values <laravel-query-builder-aggregation-groupby>`
- :ref:`Count the number of results <laravel-query-builder-aggregation-count>`
- :ref:`Maximum value of a field <laravel-query-builder-aggregation-max>`
- :ref:`Minimum value of a field <laravel-query-builder-aggregation-min>`
- :ref:`Average value of a field <laravel-query-builder-aggregation-avg>`
- :ref:`Summed value of a field <laravel-query-builder-aggregation-sum>`
- :ref:`Aggregate matched results <laravel-query-builder-aggregate-matched>`

.. _laravel-query-builder-aggregation-groupby:

Results Grouped by Common Field Values Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``groupBy()`` query builder method
to retrieve document data grouped by shared values of the ``runtime`` field.
This example chains the following operations to match documents from the
``movies`` collection that contain a ``rated`` value of ``G`` and include the
``title`` field of one movie for each distinct ``runtime`` value:

- Match only documents that contain a ``rated`` field value of ``"G"`` by
  using the ``where()`` method
- Group data by the distinct values of the ``runtime`` field, which is
  assigned the ``_id`` field, by using the ``groupBy()`` method
- Sort the groups by the ``runtime`` field by using the ``orderBy()`` method
- Return ``title`` data from the last document in the grouped result by
  specifying it in the ``get()`` method

.. tip::

   The ``groupBy()`` method calls the MongoDB ``$group`` aggregation operator
   and ``$last`` accumulator operator. To learn more about these operators, see
   :manual:`$group (aggregation) </reference/operator/aggregation/group/>`
   in the {+server-docs-name+}.

.. io-code-block::
   :copyable: true

   .. input:: /includes/query-builder/QueryBuilderTest.php
      :language: php
      :dedent:
      :start-after: begin query groupBy
      :end-before: end query groupBy

   .. output::
      :language: json
      :visible: false

      [
        ...
        {
          "_id": { "runtime": 64 },
          "runtime": 64,
          "title": "Stitch! The Movie"
        },
        {
           "_id": { "runtime": 67 },
           "runtime": 67,
           "title": "Bartok the Magnificent"
         },
         {
           "_id": { "runtime":68 },
           "runtime": 68,
           "title": "Mickey's Twice Upon a Christmas"
         },
         ...
      ]

.. _laravel-query-builder-aggregation-count:

Number of Results Example
~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``count()``
query builder method to return the number of documents
contained in the ``movies`` collection:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin aggregation count
   :end-before: end aggregation count

.. _laravel-query-builder-aggregation-max:

Maximum Value of a Field Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``max()``
query builder method to return the highest numerical
value of the ``runtime`` field from the entire
``movies`` collection:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin aggregation max
   :end-before: end aggregation max

.. _laravel-query-builder-aggregation-min:

Minimum Value of a Field Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``min()``
query builder method to return the lowest numerical
value of the ``year`` field from the entire ``movies``
collection:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin aggregation min
   :end-before: end aggregation min

.. _laravel-query-builder-aggregation-avg:

Average Value of a Field Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``avg()``
query builder method to return the numerical average, or
arithmetic mean, of the ``imdb.rating`` values from
the entire ``movies`` collection.

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin aggregation avg
   :end-before: end aggregation avg

.. _laravel-query-builder-aggregation-sum:

Summed Value of a Field Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``sum()``
query builder method to return the numerical total of
the ``imdb.votes`` values from the entire ``movies``
collection:


.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin aggregation sum
   :end-before: end aggregation sum

.. _laravel-query-builder-aggregate-matched:

Aggregate Matched Results Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to aggregate data
from results that match a query. The query matches all
movies after the year ``2000`` and computes the average
value of ``imdb.rating`` of those matches by using the
``avg()`` method:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin aggregation with filter
   :end-before: end aggregation with filter

.. _laravel-options-query-builder:

Set Query-Level Options
-----------------------

You can modify the way that the {+odm-short+} performs operations by
setting options on the query builder. You can pass an array of options
to the ``options()`` query builder method to specify options for the
query.

The following code demonstrates how to attach a comment to
a query:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin options
   :end-before: end options

The query builder accepts the same options that you can set for
the :phpmethod:`find() <phpmethod.MongoDB\\Collection::find()>` method in the
{+php-library+}. Some of the options to modify query results, such as
``skip``, ``sort``, and ``limit``, are settable directly as query
builder methods and are described in the
:ref:`laravel-modify-results-query-builder` section of this guide. We
recommend that you use these methods instead of passing them as options.

.. _laravel-modify-results-query-builder:

Modify Query Results
--------------------

This section includes query builder examples for the
following functions that modify the order and format
of query results:

- :ref:`Order results by the value of a field <laravel-query-builder-orderby>`
- :ref:`Omit a specified number of results <laravel-query-builder-skip>`
- :ref:`Show a subset of fields and array values in the results <laravel-query-builder-project>`
- :ref:`Paginate the results <laravel-query-builder-paginate>`

.. _laravel-query-builder-orderby:

Order Results Example
~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``orderBy()``
query builder method to arrange the results that match
the filter specified in the ``title`` field by the
``imdb.rating`` value in descending order:

.. io-code-block::
   :copyable: true

   .. input:: /includes/query-builder/QueryBuilderTest.php
      :language: php
      :dedent:
      :start-after: begin query orderBy
      :end-before: end query orderBy

   .. output::
      :language: json
      :visible: false

      [
        { "title": "Back to the Future", "imdb": { "rating":8.5,"votes":636511,"id":88763 }, ... },
        { "title": "Back to the Future Part II", "imdb": { "rating":7.8,"votes":292539,"id":96874 }, ... },
        { "title": "Back to the Future Part III", "imdb": {"rating":7.4,"votes":242390,"id":99088 }, ... },
        ...
      ]

.. _laravel-query-builder-skip:

Omit a Specified Number of Results Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``skip()`` query builder method to
omit the first four results that match the filter specified in the ``title``
field, sorted by the ``year`` value in ascending order:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query skip
   :end-before: end query skip

.. _laravel-query-builder-project:

Show a Subset of Fields and Array Values in the Results Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``project()``
query builder method to match documents that contain an
``imdb.rating`` value higher than ``8.5`` and return
only the following field values:

- Title of the movie in the ``title``
- Second through fourth values of the ``cast`` array field, if they exist
- Document ``_id`` field, which is automatically included

.. io-code-block::
   :copyable: true

   .. input:: /includes/query-builder/QueryBuilderTest.php
      :language: php
      :dedent:
      :start-after: begin query projection
      :end-before: end query projection

   .. output::
      :language: json
      :visible: false

      [
        {
          "_id": { ... },
          "title": "City Lights"
          "cast": [
            "Florence Lee",
            "Harry Myers",
            "Al Ernest Garcia"
          ],
        },
        {
          "_id": { ... },
          "title": "Modern Times",
          "cast": [
            "Paulette Goddard",
            "Henry Bergman",
            "Tiny Sandford"
          ]
        },
        {
          "_id": { ... },
          "title": "Casablanca"
          "cast": [
            "Ingrid Bergman",
            "Paul Henreid",
            "Claude Rains"
          ],
        },
        ...
      ]

.. _laravel-query-builder-paginate:

Paginate the Results Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``paginate()`` query builder method
to divide the entire ``movie`` collection into discrete result sets of 15
documents. The example also includes a sort order to arrange the results by
the ``imdb.votes`` field in descending order and a projection that includes
only specific fields in the results.

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query projection with pagination
   :end-before: end query projection with pagination

To learn more about pagination, see
`Paginating Query Builder Results <https://laravel.com/docs/{+laravel-docs-version+}/pagination#paginating-query-builder-results>`__
in the Laravel documentation.

.. _laravel-mongodb-read-query-builder:

Retrieve Data by Using MongoDB Operations
-----------------------------------------

This section includes query builder examples that show how
to use the following MongoDB-specific query operations:

- :ref:`Match documents that contain a field <laravel-query-builder-exists>`
- :ref:`Match documents that contain all specified fields <laravel-query-builder-all>`
- :ref:`Match documents that contain a specific number of elements in an array <laravel-query-builder-size>`
- :ref:`Match documents that contain a particular data type in a field <laravel-query-builder-type>`
- :ref:`Match documents that contain a computed modulo value <laravel-query-builder-mod>`
- :ref:`Match documents that match a regular expression <laravel-query-builder-regex>`
- :ref:`Run MongoDB Query API operations <laravel-query-builder-whereRaw>`
- :ref:`Match documents that contain array elements <laravel-query-builder-elemMatch>`
- :ref:`Specify a cursor timeout <laravel-query-builder-cursor-timeout>`
- :ref:`Match locations by using geospatial searches <laravel-query-builder-geospatial>`

.. _laravel-query-builder-exists:

Contains a Field Example
~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``exists()``
query builder method to match documents that contain the
field ``random_review``:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query exists
   :end-before: end query exists

To learn more about this query operator, see
:manual:`$exists </reference/operator/query/exists/>`
in the {+server-docs-name+}.

.. _laravel-query-builder-all:

Contains All Fields Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``all`` query
operator with the ``where()`` query builder method to match
documents that contain all the specified fields:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query all
   :end-before: end query all

To learn more about this query operator, see
:manual:`$all </reference/operator/query/all/>`
in the {+server-docs-name+}.

.. _laravel-query-builder-size:

Match Array Size Example
~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to pass the ``size``
query operator with the ``where()`` query builder
method to match documents that contain a ``directors``
field that contains an array of exactly five elements:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query size
   :end-before: end query size

To learn more about this query operator, see
:manual:`$size </reference/operator/query/size/>`
in the {+server-docs-name+}.

.. _laravel-query-builder-type:

Match Data Type Example
~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to pass the ``type``
query operator with the ``where()`` query builder
method to match documents that contain a type ``4`` value,
which corresponds to an array data type, in the
``released`` field.

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query type
   :end-before: end query type

To learn more about the type codes and query operator, see
:manual:`$type </reference/operator/query/type/>`
in the {+server-docs-name+}.

.. _laravel-query-builder-mod:

Match a Value Computed with Modulo Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to pass the ``mod``
query operator with the ``where()`` query builder
method to match documents by using the expression
``year % 2 == 0``, which matches even values for
the ``year`` field:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query modulo
   :end-before: end query modulo

To learn more about this query operator, see
:manual:`$mod </reference/operator/query/mod/>`
in the {+server-docs-name+}.

.. _laravel-query-builder-regex:

Match a Regular Expression
~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to pass the ``REGEX``
query operator with the ``where()`` query builder
method to match documents that contain a ``title``
field that matches the specified regular expression:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query whereRegex
   :end-before: end query whereRegex

To learn more about regular expression queries in MongoDB, see
:manual:`$regex </reference/operator/query/regex/>`
in the {+server-docs-name+}.

.. _laravel-query-builder-whereRaw:

Run MongoDB Query API Operations Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``whereRaw()``
query builder method to run a query operation written by
using the MongoDB Query API syntax:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query raw
   :end-before: end query raw

The following code shows the equivalent MongoDB Query API syntax:

.. code-block::

   db.movies.find({
     "imdb.votes": { $gte: 1000 },
     $or: [{
       imdb.rating: { $gt: 7 },
       directors: { $in: [ "Yasujiro Ozu", "Sofia Coppola", "Federico Fellini" ] }
   }]});

To learn more about the MongoDB Query API, see
:manual:`MongoDB Query API </query-api/>` in the {+server-docs-name+}.

.. _laravel-query-builder-elemMatch:

Match Array Elements Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to pass the ``elemMatch``
query operator with the ``where()`` query builder
method to match documents that contain an array element
that matches at least one of the conditions in the
specified query:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query elemMatch
   :end-before: end query elemMatch

To learn more about regular expression queries in MongoDB, see
the :manual:`$elemMatch operator </reference/operator/query/elemMatch/>`
in the {+server-docs-name+}.

.. _laravel-query-builder-cursor-timeout:

Specify Cursor Timeout Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``timeout()`` method
to specify a maximum duration to wait for cursor operations to complete.

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query cursor timeout
   :end-before: end query cursor timeout

.. note::

   This setting specifies a ``maxTimeMS`` value in seconds instead of
   milliseconds. To learn more about the ``maxTimeMS`` value, see
   `MongoDB\Collection::find() <https://www.mongodb.com/docs/php-library/current/reference/method/MongoDBCollection-find/>`__
   in the PHP Library documentation.

.. _laravel-query-builder-geospatial:

Match Locations by Using Geospatial Operations
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The examples in this section show the query builder syntax you
can use to perform geospatial queries on GeoJSON or coordinate
pair data to retrieve the following types of locations:

- :ref:`Near a position <laravel-query-builder-geospatial-near>`
- :ref:`Within a the boundary of a GeoJSON object <laravel-query-builder-geospatial-geoWithin>`
- :ref:`Intersecting a GeoJSON object <laravel-query-builder-geospatial-geoIntersects>`
- :ref:`Proximity data for nearby matches <laravel-query-builder-geospatial-geoNear>`

.. important::

   To perform GeoJSON queries in MongoDB, you must create either ``2d`` or
   ``2dsphere`` index on the collection. To learn how to create geospatial
   indexes, see the :ref:`laravel-eloquent-geospatial-index` section in the
   Schema Builder guide.

To learn more about GeoJSON objects that MongoDB supports,
see :manual:`GeoJSON Objects </reference/geojson/>`
in the {+server-docs-name+}.

.. _laravel-query-builder-geospatial-near:

Near a Position Example
~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``near`` query operator
with the ``where()`` query builder method to match documents that
contain a location that is up to ``50`` meters from a GeoJSON Point
object:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query near
   :end-before: end query near

To learn more about this operator, see
:manual:`$near operator </reference/operator/query/near/>`
in the {+server-docs-name+}.

.. _laravel-query-builder-geospatial-geoWithin:

Within an Area Example
~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``geoWithin``
query operator with the ``where()``
query builder method to match documents that contain a
location within the bounds of the specified ``Polygon``
GeoJSON object:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query geoWithin
   :end-before: end query geoWithin

.. _laravel-query-builder-geospatial-geoIntersects:

Intersecting a Geometry Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``geoInstersects``
query operator with the ``where()`` query builder method to
match documents that contain a location that intersects with
the specified ``LineString`` GeoJSON object:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query geoIntersects
   :end-before: end query geoIntersects

.. _laravel-query-builder-geospatial-geoNear:

Proximity Data for Nearby Matches Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``geoNear`` aggregation operator
with the ``raw()`` query builder method to perform an aggregation that returns
metadata, such as proximity information for each match:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin query geoNear
   :end-before: end query geoNear

To learn more about this aggregation operator, see
:manual:`$geoNear operator </reference/operator/aggregation/geoNear/>`
in the {+server-docs-name+}.

.. _laravel-mongodb-write-query-builder:

Write Data by Using MongoDB Write Operations
--------------------------------------------

This section includes query builder examples that show how to use the
following MongoDB-specific write operations:

- :ref:`Upsert a document <laravel-mongodb-query-builder-upsert>`
- :ref:`Increment a numerical value <laravel-mongodb-query-builder-increment>`
- :ref:`Decrement a numerical value <laravel-mongodb-query-builder-decrement>`
- :ref:`Add an array element <laravel-mongodb-query-builder-push>`
- :ref:`Remove a value from an array <laravel-mongodb-query-builder-pull>`
- :ref:`Remove a field from a document <laravel-mongodb-query-builder-unset>`

.. _laravel-mongodb-query-builder-upsert:

Upsert a Document Example
~~~~~~~~~~~~~~~~~~~~~~~~~

Starting in v4.7, you can perform an upsert operation by using either of
the following query builder methods:

- ``upsert()``: When you use this method, you can perform a **batch
  upsert** to change or insert multiple documents in one operation.

- ``update()``: When you use this method, you must specify the
  ``upsert`` option to update all documents that match the query filter
  or insert one document if no documents are matched. Only this upsert method
  is supported in versions v4.6 and earlier.

Upsert Method
^^^^^^^^^^^^^

The ``upsert(array $values, array|string $uniqueBy, array|null
$update)`` query builder method accepts the following parameters:

- ``$values``: Array of fields and values that specify documents to update or insert.
- ``$uniqueBy``: List of fields that uniquely identify documents in your
  first array parameter.
- ``$update``: Optional list of fields to update if a matching document
  exists. If you omit this parameter, the {+odm-short+} updates all fields.

The following example shows how to use the ``upsert()`` query builder method
to update or insert documents based on the following instructions:

- Specify a document in which the value of the ``title`` field is
  ``'Inspector Maigret'``, the value of the ``recommended`` field is ``false``,
  and the value of the ``runtime`` field is ``128``.

- Specify a document in which the value of the ``title`` field is
  ``'Petit Maman'``, the value of the ``recommended`` field is
  ``true``, and the value of the ``runtime`` field is ``72``.

- Indicate that the ``title`` field uniquely identifies documents in the
  scope of your operation.

- Update only the ``recommended`` field in matched documents.

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin upsert
   :end-before: end upsert

The ``upsert()`` query builder method returns the number of
documents that the operation updated, inserted, and modified.

.. note::

   The ``upsert()`` method does not trigger events. To trigger events
   from an upsert operation, you can use the ``createOrFirst()`` method
   instead.

Update Method
^^^^^^^^^^^^^

The following example shows how to use the ``update()`` query builder method
and ``upsert`` option to update the matching document or insert one with the
specified data if it does not exist. When you set the ``upsert`` option to
``true`` and the document does not exist, the command inserts both the data
and the ``title`` field and value specified in the ``where()`` query operation:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin update upsert
   :end-before: end update upsert

The ``update()`` query builder method returns the number of documents that the
operation updated or inserted.

.. _laravel-mongodb-query-builder-increment:

Increment a Numerical Value Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``increment()``
query builder method to add ``3000`` to the value of
the ``imdb.votes`` field in the matched document:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin increment
   :end-before: end increment

The ``increment()`` query builder method returns the number of documents that the
operation updated.

Starting in {+odm-short+} v4.8, you can also use the ``incrementEach()`` query
builder method to increment multiple values in a single operation. The following
example uses the ``incrementEach()`` method to increase the values of the ``awards.wins``
and ``imdb.votes`` fields in the matched document:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin increment each
   :end-before: end increment each

.. note::

   If you pass a field to the ``increment()`` or ``incrementEach()`` method that
   has no value or doesn't exist in the matched documents, these methods initialize
   the specified field to the increment value.

.. _laravel-mongodb-query-builder-decrement:

Decrement a Numerical Value Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``decrement()`` query builder
method to subtract ``0.2`` from the value of the ``imdb.rating`` field in the
matched document:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin decrement
   :end-before: end decrement

The ``decrement()`` query builder method returns the number of documents that the
operation updated.

Starting in {+odm-short+} v4.8, you can also use the ``decrementEach()`` query builder
method to decrement multiple values in a single operation. The following example uses
the ``decrementEach()`` method to decrease the values of the ``metacritic`` and ``imdb.rating``
fields in the matched document:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin decrement each
   :end-before: end decrement each

.. note::

   If you pass a field to the ``decrement()`` or ``decrementEach()`` method that
   has no value or doesn't exist in the matched documents, these methods initialize
   the specified field to the decrement value.

.. _laravel-mongodb-query-builder-push:

Add an Array Element Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``push()`` query builder method to
add ``"Gary Cole"`` to the ``cast`` array field in the matched document:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin push
   :end-before: end push

The ``push()`` query builder method returns the number of documents that the
operation updated.

.. _laravel-mongodb-query-builder-pull:

Remove an Array Element Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``pull()`` query builder method
to remove the ``"Adventure"`` value from the ``genres`` field from the document
matched by the query:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin pull
   :end-before: end pull

The ``pull()`` query builder method returns the number of documents that the
operation updated.

.. _laravel-mongodb-query-builder-unset:

Remove a Field Example
~~~~~~~~~~~~~~~~~~~~~~

The following example shows how to use the ``unset()`` query builder method
to remove the ``tomatoes.viewer`` field and value from the document matched
by the query:

.. literalinclude:: /includes/query-builder/QueryBuilderTest.php
   :language: php
   :dedent:
   :start-after: begin unset
   :end-before: end unset

The ``unset()`` query builder method returns the number of documents that the
operation updated.