tcb13 / sequel-mongo-php
Requires
- php: >=7.2
- ext-curl: *
- ext-json: *
- ext-mongodb: *
- mongodb/mongodb: *
README
A lightweight, expressive, framework agnostic query builder for PHP that empowers you to run SQL-like queries on MongoDB databases. Enjoy the best of the two worlds!
Installation
Pull the package via composer.
$ composer require TCB13/sequel-mongo-php
Usage
Use MongoDB\Client
to connect to your Database:
// Start a MongoDB Client to create a connection to your Database $mongoConnection = new \MongoDB\Client("mongodb://127.0.0.1", [ "username" => "user", "password" => "pass" ]); /** @var \MongoDB\Database $mongo */ $mongo = $mongoConnection->selectDatabase("DatabaseName");
Get one item from a collection:
$qb = new QueryBuilder($mongo); $qb->collection("Users") ->select("_id") ->find(); $result = $qb->toObject(); var_dump($result);
Get multiple items:
$qb = new QueryBuilder($mongo); $qb->collection("Users") ->select("_id") ->limit(2) ->findAll(); $result = $qb->toObject(); var_dump($result);
select()
takes the desired fields as anarray
of strings or a variable number of parameters.
Run a complex query with multiple where
conditions, limit
and order
.
// Use the Query Builder $qb = new QueryBuilder($mongo); $qb->collection("Users") ->select("_id", "name", "email", "active") ->where("setting", "!=", "other") ->where("active", false) ->where(function ($qb) { $qb->where("isValid", true) ->orWhere("dateActivated", "!=", null); }) ->limit(100) ->order("dateCreated", "ASC") ->findAll(); $result = $qb->toObject(); var_dump($result);
- If the operator is omitted in
where()
queries,=
will be used. Eg.->where("active", false)
; - You can group
where
conditions by providing closures. Eg.WHERE id = 1 AND (isValid = true OR dateActivated != null)
can be written as:
->where("id", 1) ->where(function ($qb) { $qb->where("isValid", true) ->orWhere("dateActivated", "!=", null); })
where()
supports the following operators=
,!=
,>
,>=
,<
,<=
;- SQL's
WHERE IN()
is also available:
$qb = new QueryBuilder($mongo); $qb->collection("Orders") ->whereIn("itemCount", [22,4]) ->findAll(); $result = $qb->toArray();
-
WHERE NOT IN()
is also available viawhereNotIn()
andorWhereNotIn()
respectively; -
Examples of other useful String queries:
->whereStartsWith("item", "start") ->whereEndsWith("item", "end") ->whereContains("item", "-middle-") ->whereRegex("item", ".*-middle-.*")
- For more complex String queries you may also use regex:
->whereRegex("item", ".*-middle-.*")
Count Documents
You may count the number of documents/records that match a query with the count()
method:
$qb = new QueryBuilder($mongo); $result = $qb->collection("Users") ->where("userid", $this->id) ->count(); var_dump($result);
Note that you may not call find()
or findAll()
in combination with this method.
Insert a document:
$qb = new QueryBuilder($mongo); $result = $qb->collection("TestCollection") ->insert([ "item" => "test-insert", "xpto" => microtime(true) ]); var_dump($result);
- You may also insert multiple documents at once:
$items = [ [ "item" => "test-insert-multi", "xpto" => microtime(true) ], [ "item" => "test-insert-multi", "xpto" => microtime(true) ] ]; $qb = new QueryBuilder($mongo); $result = $qb->collection("TestCollection") ->insert($items);
Delete a Document:
$qb = new QueryBuilder($mongo); $result = $qb->collection("TestCollection") ->whereStartsWith("item", "test-insert-") ->delete(); var_dump($result);
Update a Document:
// Update item $qb = new QueryBuilder($mongo); $result = $qb->collection($collection) ->where("_id", new MongoID("51ee74e944670a09028d4fc9")) ->update([ "item" => "updated-value " . microtime(true) ]); var_dump($result);
- You may update only a few fields or an entire document - like like an SQL
update
statement.
Join Collections:
join($collectionToJoin, $localField, $operatorOrForeignField, $foreignField)
$qb = new QueryBuilder($mongo); $qb->collection("Orders") //->select("_id", "products#joined.sku") //->join(["products" => "products#joined"], "sku", "=", "item"]) //->join("products", "sku", "=", "item") ->join("Products", "sku", "item") ->findAll(); $result = $qb->toArray(); var_dump($result);
Special Functions:
Max(string $property, ?string $alias = null) - get the maximum value in a set of values:
$qb = new QueryBuilder($mongo); $qb->collection("Orders") ->select("id", new Max("datecreated", "lastorder")) ->where("userid", "u123") ->find(); $result = $qb->toArray(); var_dump($result);
Min(string $property, ?string $alias = null) - get the minimum value in a set of values:
$qb = new QueryBuilder($mongo); $qb->collection("Orders") ->select("id", new Min("datecreated", "lastorder")) ->where("userid", "u123") ->find(); $result = $qb->toArray(); var_dump($result);
Increment(string $propertyName, int $incrementBy = 1) - increment or decrement a document property by a value:
$qb = new QueryBuilder($mongo); $qb->collection("Orders") ->where("id", 12345) ->update([ new Increment("status") ]);
ArrayContains(string $arrayProperty, $needles) - check if an array in a document contains a value (or at least one value if an array is passed):
$qb = new QueryBuilder($mongo); $qb->collection("Orders") ->select("id") ->where(new ArrayContains("prioritaryItems", "123")) ->findAll(); $result = $qb->toArray(); var_dump($result);
ArrayLength(string $arrayProperty, ?string $alias = null) - get the length of an array:
$qb = new QueryBuilder($mongo); $qb->collection("Orders") ->select("id", new ArrayLength("prioritaryItems", "prioritaryItems_lenght")) ->where("prioritaryItems_lenght", ">", 0) ->findAll(); $result = $qb->toArray(); var_dump($result);
ArrayPush(string $arrayProperty, mixed $value) - add an element to an array. Example: document with a tokens
property that is an array:
$qb = new QueryBuilder($mongo); $qb->collection("Users") ->where("id", 123) ->update([ new ArrayPush("tokens", "...") ]); $result = $qb->toArray(); var_dump($result);
ArrayPull(string $arrayProperty, mixed $value) - remove an element from an array. Example: document with a tokens
property that is an array:
$qb = new QueryBuilder($mongo); $qb->collection("Users") ->where("id", 123) ->update([ new ArrayPull("tokens", "...") ]); $result = $qb->toArray(); var_dump($result);
Debug Queries:
It is possible possible to debug the query pipeline built by the Query Builder for each query.
QueryBuilder::$pipelineDebug = true; // Enable pipeline debugging! // Run a query $result = (new QueryBuilder())->collection("xyz") ->where("active", true) ->findAll() ->toArray(); // Fetch the pipeline built by the Query Builder var_dump(QueryBuilder::getLastPipelineLog()); // Get the pipeline built for the last query var_dump(QueryBuilder::getPipelineLogs()); // Get all pipelines ever built by the query builder
For more examples check out the examples
directory.