Skip to content

Latest commit

 

History

History
602 lines (443 loc) · 12.7 KB

query-builder.md

File metadata and controls

602 lines (443 loc) · 12.7 KB

Query Builder

The database driver plugs right into the original query builder.

When using MongoDB connections, you will be able to build fluent queries to perform database operations.

For your convenience, there is a collection alias for table as well as some additional MongoDB specific operators/operations.

$books = DB::collection('books')->get();

$hungerGames =
    DB::collection('books')
        ->where('name', 'Hunger Games')
        ->first();

If you are familiar with Eloquent Queries, there is the same functionality.

Available operations

Retrieving all models

$users = User::all();

Retrieving a record by primary key

$user = User::find('517c43667db388101e00000f');

Where

$posts =
    Post::where('author.name', 'John')
        ->take(10)
        ->get();

OR Statements

$posts =
    Post::where('votes', '>', 0)
        ->orWhere('is_approved', true)
        ->get();

AND statements

$users =
    User::where('age', '>', 18)
        ->where('name', '!=', 'John')
        ->get();

NOT statements

$users = User::whereNot('age', '>', 18)->get();

whereIn

$users = User::whereIn('age', [16, 18, 20])->get();

When using whereNotIn objects will be returned if the field is non-existent. Combine with whereNotNull('age') to leave out those documents.

whereBetween

$posts = Post::whereBetween('votes', [1, 100])->get();

whereNull

$users = User::whereNull('age')->get();

whereDate

$users = User::whereDate('birthday', '2021-5-12')->get();

The usage is the same as whereMonth / whereDay / whereYear / whereTime

Advanced wheres

$users =
    User::where('name', 'John')
        ->orWhere(function ($query) {
            return $query
                ->where('votes', '>', 100)
                ->where('title', '<>', 'Admin');
        })->get();

orderBy

$users = User::orderBy('age', 'desc')->get();

Offset & Limit (skip & take)

$users =
    User::skip(10)
        ->take(5)
        ->get();

groupBy

Selected columns that are not grouped will be aggregated with the $last function.

$users =
    Users::groupBy('title')
        ->get(['title', 'name']);

Distinct

Distinct requires a field for which to return the distinct values.

$users = User::distinct()->get(['name']);

// Equivalent to:
$users = User::distinct('name')->get();

Distinct can be combined with where:

$users =
    User::where('active', true)
        ->distinct('name')
        ->get();

Like

$spamComments = Comment::where('body', 'like', '%spam%')->get();

Aggregation

Aggregations are only available for MongoDB versions greater than 2.2.x

$total = Product::count();
$price = Product::max('price');
$price = Product::min('price');
$price = Product::avg('price');
$total = Product::sum('price');

Aggregations can be combined with where:

$sold = Orders::where('sold', true)->sum('price');

Aggregations can be also used on sub-documents:

$total = Order::max('suborder.price');

NOTE: This aggregation only works with single sub-documents (like EmbedsOne) not subdocument arrays (like EmbedsMany).

Incrementing/Decrementing the value of a column

Perform increments or decrements (default 1) on specified attributes:

Cat::where('name', 'Kitty')->increment('age');

Car::where('name', 'Toyota')->decrement('weight', 50);

The number of updated objects is returned:

$count = User::increment('age');

You may also specify additional columns to update:

Cat::where('age', 3)
    ->increment('age', 1, ['group' => 'Kitty Club']);

Car::where('weight', 300)
    ->decrement('weight', 100, ['latest_change' => 'carbon fiber']);

MongoDB-specific operators

In addition to the Laravel Eloquent operators, all available MongoDB query operators can be used with where:

User::where($fieldName, $operator, $value)->get();

It generates the following MongoDB filter:

{ $fieldName: { $operator: $value } }

Exists

Matches documents that have the specified field.

User::where('age', 'exists', true)->get();

All

Matches arrays that contain all elements specified in the query.

User::where('roles', 'all', ['moderator', 'author'])->get();

Size

Selects documents if the array field is a specified size.

Post::where('tags', 'size', 3)->get();

Regex

Selects documents where values match a specified regular expression.

use MongoDB\BSON\Regex;

User::where('name', 'regex', new Regex('.*doe', 'i'))->get();

NOTE: you can also use the Laravel regexp operations. These will automatically convert your regular expression string to a MongoDB\BSON\Regex object.

User::where('name', 'regexp', '/.*doe/i')->get();

The inverse of regexp:

User::where('name', 'not regexp', '/.*doe/i')->get();

Type

Selects documents if a field is of the specified type. For more information check: http://docs.mongodb.org/manual/reference/operator/query/type/#op._S_type

User::where('age', 'type', 2)->get();

Mod

Performs a modulo operation on the value of a field and selects documents with a specified result.

User::where('age', 'mod', [10, 0])->get();

MongoDB-specific Geo operations

Near

$bars = Bar::where('location', 'near', [
    '$geometry' => [
        'type' => 'Point',
        'coordinates' => [
            -0.1367563, // longitude
            51.5100913, // latitude
        ],
    ],
    '$maxDistance' => 50,
])->get();

GeoWithin

$bars = Bar::where('location', 'geoWithin', [
    '$geometry' => [
        'type' => 'Polygon',
        'coordinates' => [
            [
                [-0.1450383, 51.5069158],
                [-0.1367563, 51.5100913],
                [-0.1270247, 51.5013233],
                [-0.1450383, 51.5069158],
            ],
        ],
    ],
])->get();

GeoIntersects

$bars = Bar::where('location', 'geoIntersects', [
    '$geometry' => [
        'type' => 'LineString',
        'coordinates' => [
            [-0.144044, 51.515215],
            [-0.129545, 51.507864],
        ],
    ],
])->get();

GeoNear

You are able to make a geoNear query on mongoDB. You don't need to specify the automatic fields on the model. The returned instance is a collection. So you're able to make the Collection operations. Just make sure that your model has a location field, and a 2ndSphereIndex. The data in the location field must be saved as GeoJSON. The location points must be saved as WGS84 reference system for geometry calculation. That means, basically, you need to save longitude and latitude, in that order specifically, and to find near with calculated distance, you need to do the same way.

Bar::find("63a0cd574d08564f330ceae2")->update(
    [
        'location' => [
            'type' => 'Point',
            'coordinates' => [
                -0.1367563,
                51.5100913
            ]
        ]
    ]
);
$bars = Bar::raw(function ($collection) {
    return $collection->aggregate([
        [
            '$geoNear' => [
                "near" => [ "type" =>  "Point", "coordinates" =>  [-0.132239, 51.511874] ],
                "distanceField" =>  "dist.calculated",
                "minDistance" =>  0,
                "maxDistance" =>  6000,
                "includeLocs" =>  "dist.location",
                "spherical" =>  true,
            ]
        ]
    ]);
});

Inserts, updates and deletes

Inserting, updating and deleting records works just like the original Eloquent. Please check Laravel Docs' Eloquent section.

Here, only the MongoDB-specific operations are specified.

MongoDB specific operations

Raw Expressions

These expressions will be injected directly into the query.

User::whereRaw([
    'age' => ['$gt' => 30, '$lt' => 40],
])->get();

User::whereRaw([
    '$where' => '/.*123.*/.test(this.field)',
])->get();

User::whereRaw([
    '$where' => '/.*123.*/.test(this["hyphenated-field"])',
])->get();

You can also perform raw expressions on the internal MongoCollection object. If this is executed on the model class, it will return a collection of models.

If this is executed on the query builder, it will return the original response.

Cursor timeout

To prevent MongoCursorTimeout exceptions, you can manually set a timeout value that will be applied to the cursor:

DB::collection('users')->timeout(-1)->get();

Upsert

Update or insert a document. Additional options for the update method are passed directly to the native update method.

// Query Builder
DB::collection('users')
    ->where('name', 'John')
    ->update($data, ['upsert' => true]);

// Eloquent
$user->update($data, ['upsert' => true]);

Projections

You can apply projections to your queries using the project method.

DB::collection('items')
    ->project(['tags' => ['$slice' => 1]])
    ->get();

DB::collection('items')
    ->project(['tags' => ['$slice' => [3, 7]]])
    ->get();

Projections with Pagination

$limit = 25;
$projections = ['id', 'name'];

DB::collection('items')
    ->paginate($limit, $projections);

Push

Add one or multiple values to the items array.

// Push the value to the matched documents
DB::collection('users')
  ->where('name', 'John')
  // Push a single value to the items array
  ->push('items', 'boots');
// Result:
// items: ['boots']

DB::collection('users')
  ->where('name', 'John')
  // Push multiple values to the items array
  ->push('items', ['hat', 'jeans']);
// Result:
// items: ['boots', 'hat', 'jeans']

// Or

// Push the values directly to a model object
$user->push('items', 'boots');
$user->push('items', ['hat', 'jeans']);

To add embedded document or array values to the messages array, those values must be specified within a list array.

DB::collection('users')
  ->where('name', 'John')
    // Push an embedded document as a value to the messages array
  ->push('messages', [
      [ 'from' => 'Jane Doe', 'message' => 'Hi John' ]
  ]);
// Result:
// messages: [
//      { from: "Jane Doe", message: "Hi John" }
//  ]

// Or

$user->push('messages', [
    [ 'from' => 'Jane Doe', 'message' => 'Hi John' ]
]);

If you DON'T want duplicate values, set the third parameter to true:

DB::collection('users')
  ->where('name', 'John')
  ->push('items', 'boots');
// Result:
// items: ['boots']

DB::collection('users')
  ->where('name', 'John')
  ->push('items', ['hat', 'boots', 'jeans'], true);
// Result:
// items: ['boots', 'hat', 'jeans']

// Or

$user->push('messages', [
    [ 'from' => 'Jane Doe', 'message' => 'Hi John' ]
]);
// Result:
// messages: [
//      { from: "Jane Doe", message: "Hi John" }
//  ]

$user->push('messages', [
    [ 'from' => 'Jess Doe', 'message' => 'Hi' ],
    [ 'from' => 'Jane Doe', 'message' => 'Hi John' ],
], true);
// Result:
// messages: [
//      { from: "Jane Doe", message: "Hi John" }
//      { from: "Jess Doe", message: "Hi" }
//  ]

Pull

Remove one or multiple values from the items array.

// items: ['boots', 'hat', 'jeans']

DB::collection('users')
  ->where('name', 'John')
  ->pull('items', 'boots'); // Pull a single value
// Result:
// items: ['hat', 'jeans']

// Or pull multiple values

$user->pull('items', ['boots', 'jeans']);
// Result:
// items: ['hat']

Embedded document and arrays values can also be removed from the messages array.

// Latest state:
// messages: [
//      { from: "Jane Doe", message: "Hi John" }
//      { from: "Jess Doe", message: "Hi" }
//  ]

DB::collection('users')
  ->where('name', 'John')
    // Pull an embedded document from the array
  ->pull('messages', [
      [ 'from' => 'Jane Doe', 'message' => 'Hi John' ]
  ]);
// Result:
// messages: [
//      { from: "Jess Doe", message: "Hi" }
//  ]

// Or pull multiple embedded documents

$user->pull('messages', [
    [ 'from' => 'Jane Doe', 'message' => 'Hi John' ],
    [ 'from' => 'Jess Doe', 'message' => 'Hi' ]
]);
// Result:
// messages: [ ]

Unset

Remove one or more fields from a document.

DB::collection('users')
    ->where('name', 'John')
    ->unset('note');

$user->unset('note');

$user->save();

Using the native unset on models will work as well:

unset($user['note']);
unset($user->node);