SleekDB is a simple flat file NoSQL like database implemented in PHP without any third-party dependencies that store data in plain JSON files.
It is not comparable with databases like Sqlite, MySQL, PostgreSQL and MariaDB because they are relational databases! SleekDB is a NoSQL database and therefore more comparable with for example MongoDB.
It is not designed to handle heavy-load IO operations, it is designed to have a simple solution where all we need a database for is managing a few gigabytes of data. You can think of it as a database for low to medium operation loads.
SleekDB works great as the database engine for low to medium traffic websites. It makes simple database effortless.
SleekDB will cache all query data by default, in a way that would make any dynamic site as fast as static sites.
SleekDB is also a flat file database, but instead of a single file it stores data in multiple JSON files. That allows it to have a better concurrency support compared to fully FlatFile database systems. SleekDB can be compared with other flat file database systems because the final query output will be cached and later reused from a single file instead of traversing all the available files.
SleekDB is being used with many websites and applications which are in production right now. The core team consists of two developers. They actively maintain, contribute to, and develop the SleekDB Database.
Join our Discord channel. We would love to know about you and your feedbacks.
Stores data in plain-text utilizing JSON format, no binary conversion needed to store or fetch the data. Default query cache layer.
SleekDB does not require any schema, so you can insert any types of data you want.
As it supports schema free data, so you can filter and use conditions on nested properties of the JSON documents!
If you write this where clause:
where( 'post.author.role', '=', 'admin' )
SleekDB will look for data at:
{
"post": {
"author": {
"role": "admin"
}
}
}
Supports PHP 7+. Requires no third-party plugins or software.
SleekDB will serve data from cache by default and regenerate cache automatically! Query results will be cached and later reused from a single file instead of traversing all the available files.
Use multiple conditional comparisons, text search, sorting on multiple properties and nested properties.
|
|
|
|
SleekDB does not require any background process or network protocol in order to process data when you use it in a PHP project. All data for a query will be fetched at runtime within the same PHP process.
Runs perfectly on shared-servers or VPS too.
SleekDB provides a very simple elegant API to handle all of your data.
SleekDB use files to store information. That makes tasks like backup, import and export very easy.
SleekDB is created by @rakibtg who is using it in various types of applications which are in production right now. Our other contributor and active maintainer is Timucin who is making SleekDB much better in terms of code quality and new features.
For support and other requirements contact.
To use SleekDB make sure that you have PHP up and running in your system, and SleekDB has write and/or read permission.
To install SleekDB using composer, open a terminal, cd into your project root directory where "composer.json" file lives and run this:
composer require rakibtg/sleekdb
SleekDB should be auto loaded into your app using the composer. Find SleekDB on packagist.
Import the SleekDB.php file where you want to use SleekDB.
Example:
require_once "../SleekDB/Store.php";
To download older versions please check the releases.
We do a lot of experiments on the dev branch, which are not properly documented and the API will not be consistence in some cases. Use from dev
branch if you have any specific reason or if you want play around with it.
composer require rakibtg/sleekdb dev-dev
Getting started with SleekDB is super easy. We keep data in a "store", which is similar to MySQL "table" or MongoDB "collection". Each store contains multiple documents, which are equivalent to rows in SQL based relational database.
As an example for a blog it can be something like this: posts > documents
Here "posts" is a store and "documents" are a collection of JSON files that contains all the blog posts.
With the release of version 2.0 we have updated the internal implementation.
We introduce a new API and the old SleekDB object has to retire!
Although, we are providing downwards compatibility in this version, but will remove the old SleekDB object with version 3.0.
This documentation focus on the new API.
Each query will follow the below steps or execution life cycle.
The Store
class is the first, and in most cases also the only part you need to come in contact with.
The QueryBuilder
class is used to prepare a query. To execute it getQuery
method can be used.
At this step the Query
class contains all information needed to execute the query.
The Cache
class handles everything regarding caching. It will decide when to cache or not to cache.
To begin with, we need a valid "path" where we want to store our data.
Both absolute and relative paths are supported.
$databaseDirectory = __DIR__ . "/myDatabase";
Once we have the data directory, we can initialize the store.
If the store doesn't exist it will be created automatically.
$newsStore = new \SleekDB\Store("news", $databaseDirectory);
Optionally you can pass a configuration array as a third parameter.
Read more about configurations.
Inserting your first news article using the insert
method. The article has to be an array.
$article = [
"title" => "Google Pixel XL",
"about" => "Google announced a new Pixel!",
"author" => [
"avatar" => "profile-12.jpg",
"name" => "Foo Bar"
]
];
$results = $newsStore->insert($article);
The results variable will contain all the inserted data and with their _id
property which will be unique and added automatically.
To find all news articles we will use the findAll
method.
$allNews = $newsStore->findAll();
print_r($allNews);
🎉 Congrats! You just inserted your first data into a SleekDB store and also learned how to fetch them.
Store is a simple directory where SleekDB will write all your data in JSON documents. "Store" is similar with the idea of "Table" in MySQL or "Collection" in MongoDB. You don't need to create a "store" directory manually.
To start working with a store we need to create an object.
Later, we can use that object to work with the data in that store.
Creating a store
use SleekDB\Store;
$newsStore = new Store('news', $dataDir);
Creating an additional store; assuming we are working on a community platform, where need a users store too.
$userStore = new Store('users', $dataDir);
Another store to keep all the posts shared by the user.
$postStore = new Store('posts', $dataDir);
Creating a new user
$userStore->insert([
'name' => 'Mike Doe',
'email' => 'miked@example.com',
'avatar' => [
'sm' => "/img-sm.jpg",
'lg' => "/img-lg.jpg"
]
]);
In the above example we have created a new user to understand the purpose of a store in SleekDB. In this documentation later we will see more examples on this.
To delete a store use the deleteStore()
method. It deletes a store and wipes all the data and cache it contains.
Example:
$userStore->deleteStore();
SleekDB allows a few configuration options, which are
false
and if needed use set_time_limit() in your own code! )They are store wide, which means they will be used on every query, if not changed on a query by query base.
You can pass the configurations array as a third parameter when initializing the Store object:
// default configurations
$configuration = [
"auto_cache" => true,
"cache_lifetime" => null,
"timeout" => 120, // deprecated! Set it to false!
"primary_key" => "_id",
"search" => [
"min_length" => 2,
"mode" => "or",
"score_key" => "scoreKey",
"algorithm" => Query::SEARCH_ALGORITHM["hits"]
],
"folder_permissions" => 0777
];
$newsStore = new \SleekDB\Store("news", $dataDir, $configuration);
Let's get familiar with the available configuration options.
The auto_cache
is set to true
by default!
This tells SleekDB to use the build in caching system.
To disable build in caching set auto_cache
to false
in the config array.
Note that you can manually manage caching on a query by query base with methods that SleekDB provides. Available caching method's are:
QueryBuilder->regenerateCache()
QueryBuilder->useCache()
QueryBuilder->disableCache()
The cache_lifetime
is set to null
by default!
Can be an int >= 0
, that specifies the lifetime in seconds, or null
to define that there is no lifetime.
This specifies the default cache time to live store wide.
If set to null the cache files made with that store instance will have no lifetime and will be regenerated on every insert/update/delete operation.
0
means infinite lifetime.
Note that you can specify the cache lifetime on a query by query base by using the useCache method of the QueryBuilder and pass a lifetime.
QueryBuilder->useCache($lifetime)
Note: You will find more details on caching at Cache Management
🚨 Deprecated since version 2.12, set it to
false
and if needed use set_time_limit() in your own code!
Set timeout value. Default value is 120
seconds.
It uses the build in php set_time_limit() function.
You can set the timeout option to false
if you do not want to use or can not use set_time_limit.
Set the primary key into any string you want.
SleekDB will then use that key instead of the default _id
key.
On this documentation page we will provide you a brief description regarding the configuration options.
Note: You will find more on searching at Searching
It has to be an integer
> 0.
The minimum length of a word within the search query that will be used for searching.
Has to be a string
.
SleekDB provides two modes.
Can be a string
or null
.
If this configuration is null
no field containing the score will be applied to the documents.
If it is a string
a new field with the specified field name will be applied to every document, which will contain the search score and can be used to for example sort the documents.
SleekDB provides 4 search algorithms. hits
, hits_prioritize
, prioritize
, prioritize_position
These algorithms change the score generation.
They are available as a constant of the Query
class.
Example:
Query::SEARCH_ALGORITHM["hits"] // default
folder_permissions
is set to 0777
by default!
It has to be a valid int
. Refer to the official documentation to learn more about permissions.
The given permission is only used when creating a new folder. For example when creating a new store, that does not already exist.
To insert data first you make a PHP array, and simply insert that array into a store.
For a more detailed documentation on Store
object creation refer to the corresponding "Managing Store" documentation.
use SleekDB\Store;
$userStore = new Store('users', __DIR__ . "/database");
function insert(array $data): array
One document that will be insert into the Store
Returns the inserted document as an array
including the automatically generated and unique _id
property.
// Prepare a PHP array to insert.
$user = [
'name' => 'Kazi Hasan',
'products' => [
'totalSaved' => 19,
'totalBought' => 27
],
'location' => [
'town' => 'Nagar',
'city' => 'Dhaka',
'country' => 'Bangladesh'
]
];
// Insert the data.
$user = $userStore->insert($user);
function insertMany(array $data): array
Multiple documents that will be insert into the Store
Returns the inserted documents in an array
including their automatically generated and unique _id
property.
// Prepare users data.
$users = [
[
'name' => 'Russell Newman',
'products' => [
'totalSaved' => 5,
'totalBought' => 3
],
'location' => [
'town' => 'Andreas Ave',
'city' => 'Maasdriel',
'country' => 'England'
]
],
[
'name' => 'Willard Bowman',
'products' => [
'totalSaved' => 0,
'totalBought' => 0
],
],
[
'name' => 'Tommy Mendoza',
'products' => [
'totalSaved' => 172,
'totalBought' => 54
],
],
[
'name' => 'Joshua Edwards',
'phone' => '(382)-450-8197'
]
];
// Insert all data.
$users = $userStore->insertMany($users);
Update or insert one document.
This method is especially fast because it uses the _id to update or insert the document directly instead of traversing through all documents.
public function updateOrInsert(array $data, bool $autoGenerateIdOnInsert = true): array
Document to update or insert.
Default: true
If false
and the document has a _id, that does not already exist (insert), the _id will be to insert the new document, an auto-generated _id will be used otherwise.
Returns updated / inserted document
.
Update or Insert a user with _id = 23, apply an auto-generated _id if it is an insert.
$user = [
"_id" => 23,
"name" => "John",
...
];
$userStore->updateOrInsert($user);
Update or Insert a user with _id = 23.
$user = [
"_id" => 23,
"name" => "John",
...
];
$userStore->updateOrInsert($user, false);
Update or Insert a user with no _id.
$user = [
"name" => "John",
...
];
$userStore->updateOrInsert($user);
Update or insert multiple documents.
This method is especially fast because it uses the _id to update or insert the documents directly instead of traversing through all documents.
public function updateOrInsertMany(array $data, bool $autoGenerateIdOnInsert = true): array
Documents to update or insert.
Default: true
If false
and a document has a _id, that does not already exist (insert), the _id will be to insert the new document, an auto-generated _id will be used otherwise.
Returns updated / inserted documents
.
Update or Insert multiple users and apply auto-generated _id's on inserts.
$users = [
[
"_id" => 23,
"name" => "John",
...
],
[
"_id" => 25,
"name" => "Max",
...
],
[
"name" => "Lisa",
...
]
...
];
$userStore->updateOrInsertMany($users);
Update or Insert a users with their _id's.
$users = [
[
"_id" => 23,
"name" => "John",
...
],
[
"_id" => 25,
"name" => "Max",
...
],
[
"name" => "Lisa", // <-- will get auto-generated _id
...
]
...
];
$userStore->updateOrInsertMany($users, false);
// Lisa will get a auto-generated _id, because there is no _id in the document!
To get data from the store SleekDB provides some simple yet powerful methods.
ℹ️ If you need to make more complex queries look into QueryBuilder.
For a more detailed documentation on Store
object creation refer to the corresponding "Managing Store" documentation.
use SleekDB\Store;
$newsStore = new Store('news', __DIR__ . "/database");
function findAll(array $orderBy = null, int $limit = null, int $offset = null): array
Sort the result by one or multiple fields.
Limit the result to a specific amount.
Skip a specific amount of documents.
Returns either an array containing all documents
of that store or an empty array
if there are no documents.
$allNews = $newsStore->findAll();
[["_id" => 12, "title" => "We love SleekDB"], ["_id" => 14, "title" => "NoSQL with just PHP"], ...]
With this method SleekDB doesn't traverse through all files. Instead, it accesses the file directly, what makes this method especially fast.
function findById(int|string $id): array|null
The _id of a document located in the store.
Returns one document
or null
if document could not be found.
$news = $newsStore->findById(12);
["_id" => 12, "title" => "SleekDB is the Best", ...]
function findBy(array $criteria, array $orderBy = null, int $limit = null, int $offset = null): array
One or multiple where conditions.
Visit the $criteria documentation page to learn more on how to define conditions.
Sort the result by one or multiple fields.
Limit the result to a specific amount.
Skip a specific amount of documents.
Returns found documents in an array
or an empty array
if nothing is found.
$news = $newsStore->findBy(["author", "=", "John"], ["title" => "asc"], 10, 20);
// First 20 documents skipped, limited to 10 and ascending sort by title where author is John.
[ ["_id" => 12, ... "title" => "Best Database"], ["_id" => 4, ... "title" => "Why SleekDB"], ...]
More complex where clause.
WHERE ( author = "John" OR author = "Mark" ) AND ( topic like "School%" OR topic like "Work%" )
$news = $newsStore->findBy(
[
[
["author", "=", "John"], "OR", ["author", "=", "Mark"],
],
"AND", // <-- Optional
[
["topic", "like", "School%"], "OR", ["topic", "like", "Work%"]
]
],
["title" => "asc"],
10,
20
);
function findOneBy(array $criteria): array|null
One or multiple where conditions.
Visit the $criteria documentation page to learn more on how to define conditions.
Returns one document
or null
if nothing is found.
$news = $newsStore->findOneBy(["author", "=", "Mike"]);
// Returns one news article of the author called Mike
["_id" => 18, "title" => "SleekDB is super fast", "author" => "Mike"]
More complex where clause.
WHERE ( author = "John" OR author = "Mark" ) AND ( topic like "School%" OR topic like "Work%" )
$news = $newsStore->findOneBy(
[
[
["author", "=", "John"], "OR", ["author", "=", "Mark"],
],
"AND", // <-- Optional
[
["topic", "like", "School%"], "OR", ["topic", "like", "Work%"]
]
]
);
This method is especially fast because it just counts the amount of files.
function count(): int
Returns the amount of documents in the store as an int
.
$newsCount = $newsStore->count();
// Returns: 27
To edit a data object you can use the update()
and updateById()
method of the Store
class.
ℹ️ If you need to make more complex updates look into QueryBuilder and Query.
For a more detailed documentation on Store
object creation refer to the corresponding "Managing Store" documentation.
use SleekDB\Store;
$userStore = new Store('users', __DIR__ . "/database");
Update parts of a document.
This method is especially fast because it uses the _id to update the document directly instead of traversing through all documents.
function updateById(int|string $id, array $updatable): array|false
Id of document to update.
Array containing the parts to update.
Update of nested values possible by using a dot between fieldNames (Example 3)
If a field does not exist in that document, it will be added.
Returns updated document
on success or false
if document could not be found.
Change the status of the user with _id = 23.
$userStore->updateById(23, [ "status" => "active" ]);
Change the name and the age of the user with _id = 24.
$userStore->updateById(24, [ "name" => "Georg", "age" => 22 ]);
Change the street of the user with _id = 24.
Note: The street is stored in a nested array.
$userStore->updateById(24, [ "address.street" => "first street" ]);
[
"_id" => 24,
"address" => [
"street" => "first street",
"postalCode" => "47129"
],
...
]
Update a whole document.
This method is especially fast because it uses the _id of the given document/s to update it directly instead of traversing through all documents.
function update(array $updatable): bool;
Note: This method of the Store
class updates/overrides entire document/s, not just parts.
One or multiple documents
Returns true
on success or false
if document with given _id does not exist.
$user = [
'name' => 'Willard Bowman',
'products' => [
'totalSaved' => 0,
'totalBought' => 0
],
];
//store the user
$userStore->insert($user); // has _id = 1
// retrieve a user
$user = $userStore->findById(1);
// update user
$user["name"] = "Luke Bowman";
$userStore->update( $user ); // updates the user by using his _id
// retrieve users
$users = $userStore->findBy(["name", "=", "Josh"]);
foreach($users as $key => $user){
// change the properties of the users
$user["name"] = "Luke Bowman";
// push changed user back to the users array
$users[$key] = $user;
}
// update all users that had the name Josh
$userStore->update( $users );
Update parts of a document.
This method is especially fast because it uses the _id to update the document directly instead of traversing through all documents.
function removeFieldsById(int|string $id, array $fieldsToRemove): array|false
Id of document to remove fields of.
Array containing fields to remove.
Removal of nested fields possible by using a dot between fieldNames (Example 3)
Returns updated document
on success or false
if document could not be found.
Remove the status field of the user with _id = 23.
$userStore->updateById(23, [ "status" ]);
Remove the name and the age fields of the user with _id = 24.
$userStore->updateById(24, [ "name", "age" ]);
Remove the street field of the user with _id = 24.
Note: The street is stored in a nested array.
$userStore->updateById(24, [ "address.street" ]);
[
"_id" => 24,
"address" => [
"postalCode" => "47129"
],
...
]
Update or insert one document.
This method is especially fast because it uses the _id to update or insert the document directly instead of traversing through all documents.
public function updateOrInsert(array $data, bool $autoGenerateIdOnInsert = true): array
Document to update or insert.
Default: true
If false
and the document has a _id, that does not already exist (insert), the _id will be to insert the new document, an auto-generated _id will be used otherwise.
Returns updated / inserted document
.
Update or Insert a user with _id = 23, apply an auto-generated _id if it is an insert.
$user = [
"_id" => 23,
"name" => "John",
...
];
$userStore->updateOrInsert($user);
Update or Insert a user with _id = 23.
$user = [
"_id" => 23,
"name" => "John",
...
];
$userStore->updateOrInsert($user, false);
Update or Insert a user with no _id.
$user = [
"name" => "John",
...
];
$userStore->updateOrInsert($user);
Update or insert multiple documents.
This method is especially fast because it uses the _id to update or insert the documents directly instead of traversing through all documents.
public function updateOrInsertMany(array $data, bool $autoGenerateIdOnInsert = true): array
Documents to update or insert.
Default: true
If false
and a document has a _id, that does not already exist (insert), the _id will be to insert the new document, an auto-generated _id will be used otherwise.
Returns updated / inserted documents
.
Update or Insert multiple users and apply auto-generated _id's on inserts.
$users = [
[
"_id" => 23,
"name" => "John",
...
],
[
"_id" => 25,
"name" => "Max",
...
],
[
"name" => "Lisa",
...
]
...
];
$userStore->updateOrInsertMany($users);
Update or Insert a users with their _id's.
$users = [
[
"_id" => 23,
"name" => "John",
...
],
[
"_id" => 25,
"name" => "Max",
...
],
[
"name" => "Lisa", // <-- will get auto-generated _id
...
]
...
];
$userStore->updateOrInsertMany($users, false);
// Lisa will get a auto-generated _id, because there is no _id in the document!
To delete documents you can use the deleteBy()
and deleteById()
methods of the Store
class.
ℹ️ If you need to make a more complex delete look into QueryBuilder and Query.
For a more detailed documentation on Store
object creation refer to the corresponding "Managing Store" documentation.
use SleekDB\Store;
$userStore = new Store('users', __DIR__ . "/database");
function deleteBy(array $criteria, int $returnOption = Query::DELETE_RETURN_BOOL): array|bool|int
One or multiple where conditions.
Visit the $criteria documentation page to learn more on how to define conditions.
Different return options provided with constants of the Query
class
Query::DELETE_RETURN_BOOL
(Default)Query::DELETE_RETURN_RESULTS
Query::DELETE_RETURN_COUNT
This method returns based on the given return option either boolean
, int
or array
.
Lets delete all user whose name is "Joshua Edwards"
$userStore->deleteBy(["name", "=", "Joshua Edwards"]);
// Returns true
Lets delete all user whose name is "Joshua Edwards" and retrieve deleted documents.
use SleekDB/Query;
$deletedUsers = $userStore->deleteBy(["name", "=", "Joshua Edwards"], Query::DELETE_RETURN_RESULTS);
[ ["_id" => 12, "name" => "Joshua Edwards"], ["_id" => 14, "name" => "Joshua Edwards"], ... ]
Deletion with more complex where statement.
WHERE ( name = "Joshua Edwards" OR name = "Mark Schiffer" ) AND ( age > 30 OR age < 10 )
$userStore->deleteBy(
[
[
["name", "=", "Joshua Edwards"],
"OR",
["name", "=", "Mark Shiffer"],
],
"AND", // <-- Optional
[
["age", ">", 30],
"OR",
["age", "<", 10]
]
]
);
This method is especially fast because SleekDB uses the _id to directly delete the document and does not traverse through all files.
function deleteById(int|string $id): bool
The _id of a document located in the store.
Returns true
if document does not exist or deletion was successful or false
on failure.
$userStore->deleteById(12);
// Returns true
The $criteria is an argument accepted by many different methods.
It is used to define one or multiple conditions to filter documents.
Methods that accept criteria as an argument:
One or multiple conditions.
The criteria can be nested as much as needed.
With SleekDB version 2.10 we introduce closures to filter data with your own custom functions.
[ CLOSURE, OPERATION, [$fieldName, $condition, $value], ... ]
The field name argument is the property that we want to check in our data object.
As our data object is basically a JSON document so it could have nested properties.
To target nested properties we use a single dot between the property/field name.
Example: From our above users object if we want to target the "country" property of a user, then we would pass location.country
in this argument, because "location" is the parent property of the "country" property in our data object.
To apply the comparison filters we use this argument.
The condition is case insensitive.
Allowed conditional operators are:
=
Match equal against data.===
Match equal against data.==
Match equal against data. (Type unsafe comparison)!=
Match not equal against data.!==
Match not equal against data.<>
Match not equal against data. (Type unsafe comparison)>
Match greater than against data.>=
Match greater equal against data.<
Match less than against data.<=
Match less equal against data.LIKE
Match using wildcards.NOT LIKE
Match using wildcards. Negation of result.\
.\%
.%
Represents zero or more characters_
Represents a single character[]
Represents any single character within the brackets^
Represents any character not in the brackets-
Represents a range of charactersIN
$value has to be an array
. Check if data is in given list. NOT IN
$value has to be an array
. Check if data is not in given list. CONTAINS
Returns true
if stored data is an array and the array contains $value.NOT CONTAINS
Returns true
if stored data is not an array or the array not contains $value.BETWEEN
$value has to be an array
with a lengeth of 2.NOT BETWEEN
$value has to be an array
with a length of 2.EXISTS
$value has to be a boolean
. Check if field exists.true
, it returns the documents that contain the field $fieldName,false
, it returns only the documents that do not contain the field $fieldName.Data that will be checked against the property value of the JSON documents.
Can also be a DateTime object if the property value of the JSON document is convertable into a DateTime object. Refer to the Working with Dates documentation to learn more about that.
It is used to connect multiple conditions.
The operation is optional and can be set to AND
or OR
.
Default: AND
Refer to the Official PHP documentation to learn more about closures and anonymous functions.
The closure receives a document as parameter and has to return a boolean
.
In Example 4 (below) we use a closure.
Find all news articles of the author "John".
$news = $newsStore->findBy(["author", "=", "John"]);
Find all news articles of the author "John", that have "cat" in their title.
$news = $newsStore->findBy([
["author", "=", "John"],
"AND", // <-- Optional
["title", "LIKE", "%cat%"]
]);
$news = $newsStore->findBy([
["author", "=", "John"],
["title", "LIKE", "%cat%"]
]);
Find all news articles of the author "John" or "Smith", that have "cat" in their title.
$news = $newsStore->findBy([
[
["author", "=", "John"],
"OR",
["author", "=", "Smith"],
],
"AND", // <-- Optional
["title", "LIKE", "%cat%"]
]);
$news = $newsStore->findBy([
[
["author", "=", "John"],
"OR",
["author", "=", "Smith"],
],
["title", "LIKE", "%cat%"]
]);
With this example we show you the usage of closures to filter data.
Every codeblock below does the same thing.
Find all news articles of the author "John" or "Smith", that have "cat" in their title.
// A closure in conjunction with a normal condition
$news = $newsStore->findBy([
function($article){
return ($article['author'] === 'John' || $article['author'] === 'Smith');
},
"AND", // <-- Optional
["title", "LIKE", "%cat%"]
]);
// A closure in conjunction with a normal condition
$news = $newsStore->findBy([
function($article){
return ($article['author'] === 'John' || $article['author'] === 'Smith');
},
["title", "LIKE", "%cat%"]
]);
// Extracted the closure from the criteria array
$johnOrSmithCondition = function($article){
return ($article['author'] === 'John' || $article['author'] === 'Smith')
};
// A closure in conjunction with a normal condition
$news = $newsStore->findBy([
$johnOrSmithCondition,
["title", "LIKE", "%cat%"]
]);
// Use external variables
$wantedAuthors = ['John', 'Smith'];
$johnOrSmithCondition = function($article) use ($wantedAuthors){
return in_array($article['author'], $wantedAuthors, true);
};
// A closure in conjunction with a normal condition
$news = $newsStore->findBy([
$johnOrSmithCondition,
["title", "LIKE", "%cat%"]
]);
The QueryBuilder is used to prepare more complex queries, not to execute the query!
. You can create a QueryBuilder with the createQueryBuilder()
method of the Store
class.
$userQueryBuilder = $userStore->createQueryBuilder();
With the getQuery
method of the QueryBuilder class you can retrieve the Query object to execute the query
.
function getQuery(): Query
The most important methods of the Query class to execute a query are:
Retrieve multiple documents
Retrieve first found document
Check if a document for given query exist
Delete all documents that are found with given query
Update all documents that are found with given query
For more details on query execution please visit the Query page.
To filter data we use the where() method of the QueryBuilder object.
If you provide multiple conditions they are connected with an AND
.
function where(array $criteria): QueryBuilder
One or multiple where conditions.
Visit the $criteria documentation page to learn more on how to define conditions.
To only get the user whose country is equal to "England" we would query like this:
// inline
$users = $userStore
->createQueryBuilder()
->where( [ "name", "=", "Joshua Edwards" ] )
->getQuery()
->fetch();
// creating the QueryBuilder
$userQueryBuilder = $userStore->createQueryBuilder();
// preparing the query with the QueryBuilder
$userQueryBuilder->where( [ "name", "=", "Joshua Edwards" ] );
// executing the query
$users = $userQueryBuilder->getQuery()->fetch();
You can also use multiple where
conditions.
Retrieve all users that have products.totalSaved > 10 AND products.totalBought > 20
.
// inline & using where method multiple times
$users = $userQueryBuilder
->where( ["products.totalSaved", ">", 10] )
->where( ["products.totalBought", ">", 20] )
->getQuery()
->fetch();
// inline & using where method once
$users = $userQueryBuilder
->where(
[
["products.totalSaved", ">", 10],
["products.totalBought", ">", 20]
]
)
->getQuery()
->fetch();
// retrieve QueryBuilder
$userQueryBuilder = $userStore->createQueryBuilder()
// prepare query
$userQueryBuilder->where(
[
["products.totalSaved", ">", 10],
["products.totalBought", ">", 20]
]
);
// execute query
$users = $userQueryBuilder->getQuery()->fetch();
Retrieve all users that have the status "premium", live in "london" or "new york" and are between the age of 16 and 20 or older than 30.
// example in sql
WHERE
status = "premium"
AND
(
( city = "london" OR city = "new york" )
AND
( (age >= 16 AND age < 20) OR age > 30 )
)
$users = $userQueryBuilder
->where(
[
["status", "=", "premium"], // <-- If no operation is provided, "AND" is used.
[
[ "city", "=", "london" ], "OR", ["city", "=", "new york"]
],
"AND",
[
[
[ "age", ">=", 16 ], "AND", [ "age", "<", 20 ]
],
"OR",
[ "age", ">", 30 ]
]
]
)
->getQuery()
->fetch();
orWhere(...)
works as the OR condition of SQL. SleekDB supports multiple orWhere as object chain.
If you provide multiple conditions they are connected with an AND
.
function orWhere(array $criteria): QueryBuilder
One or multiple where conditions.
Visit the $criteria documentation page to learn more on how to define conditions.
Retrieve all users that have (products.totalSaved > 10 AND products.totalBought > 20) OR products.shipped = 1
$users = $userQueryBuilder
->where(
[
["products.totalSaved", ">", 10],
["products.totalBought", ">", 20]
]
)
->orWhere( ["products.shipped", "=", 1] )
->getQuery()
->fetch();
Retrieve all users that have products.totalSaved > 10 OR (products.totalBought > 20 AND products.shipped = 1) OR totalBought = 0
$users = $userQueryBuilder
->where( ["products.totalSaved", ">", 10] )
->orWhere(
[
[ "products.totalBought", ">", 20 ],
[ "products.shipped", "=", 1 ]
]
)
->orWhere( ["products.totalBought", "=", 0] )
->getQuery()
->fetch();
Retrieve all users that have the status "premium" or live in "london" or "new york" and are between the age of 16 and 20 or older than 30.
// example in sql
WHERE
status = "premium"
OR
(
( city = "london" OR city = "new york" )
AND
( (age >= 16 AND age < 20) OR age > 30 )
)
$users = $userQueryBuilder
->where(["status", "=", "premium"])
->orWhere(
[
[
[ "city", "=", "london" ], "OR", ["city", "=", "new york"]
],
"AND",
[
[
[ "age", ">=", 16 ], // <-- If no operation is provided, "AND" is used.
[ "age", "<", 20 ]
],
"OR",
[ "age", ">", 30 ]
]
]
)
->getQuery()
->fetch();
With select(...) you can specify specific fields to output, like after the SELECT keyword in SQL. SleekDB supports multiple select()
as object chain for multiple fields.
function select(array $fieldNames): QueryBuilder
Specify specific fields to output.
When using select functions you always need to define an alias!
String functions just apply to strings and return null
on all other types.
Make a field uppercase.
Usage:
["ALIAS" => ["UPPER" => "fieldName"], ...]
Make a field lowercase.
Usage:
["ALIAS" => ["LOWER" => "fieldName"], ...]
Get the length of a field.
Can also be used to get the length of an array
!
Usage:
["ALIAS" => ["LENGTH" => "fieldName"], ...]
Concatenate multiple fields with a glue between each.
Usage:
["ALIAS" => ["CONCAT" => [GLUE, "fieldName1", "fieldName2", ...]], ...]
Get the position of a sub string.
Usage:
["ALIAS" => ["POSITION" => [SUBSTRING, "fieldName"]], ...]
Numeric functions just apply to numeric fields and return null
on all other types.
Retrieve the sum of a field.
Reduces amount of documents in result set to one. (If Group-By is not used)
Usage:
["ALIAS" => ["SUM" => "fieldName"], ...]
Retrieve the max value of a field.
Reduces amount of documents in result set to one. (If Group-By is not used)
Usage:
["ALIAS" => ["MAX" => "fieldName"], ...]
Retrieve the min value of a field.
Reduces amount of documents in result set to one. (If Group-By is not used)
Usage:
["ALIAS" => ["MIN" => "fieldName"], ...]
Retrieve the average value of a field. (sum / amount)
Reduces amount of documents in result set to one. (If Group-By is not used)
Usage:
["ALIAS" => ["AVG" => "fieldName"], ...]
Round the value of a field.
Usage:
["ALIAS" => ["ROUND" => ["fieldName", PRECISION]], ...]
Get the absolute value of a field.
Usage:
["ALIAS" => ["ABS" => "fieldName"], ...]
With SleekDB version 2.10 we introduce closures to select fields with custom functions.
Refer to the Official PHP documentation to learn more about closures and anonymous functions.
Currently it is not possible to create a custom select function that considers the whole result set and reduces the amount of the result set to one, like AVG
, MIN
, MAX
and SUM
does.
When using a custom select function you always have to provide an alias.
The closure receives a document as parameter.
In Example 5 you can see how to use custom functions to select fields.
Usage:
["ALIAS" => CLOSURE, ...]
Retrieve just the name of all users.
$users = $userQueryBuilder
->select(['name'])
->getQuery()
->fetch();
// output: [["_id" => 1, "name" => "Max"], ["_id" => 2, "name" => "Hasan"]]
Retrieve just the age of all users as age.
Note: The age of the user is stored in a nested array and we use an alias.
$users = $userQueryBuilder
->select(["age" => "userBiography.age"])
->getQuery()
->fetch();
// output: [["_id" => 1, "age" => 20], ["_id" => 2, "age" => 17]]
Using select functions that reduce the amount of the result set to one.
SELECT SUM(follower) as followerSum;
$users = $userQueryBuilder
->select([
"followerSum" => ["SUM" => "follower"],
"followerAvg" => ["AVG" => "follower"]
])
->getQuery()
->fetch();
[
["followerSum" => 81482, "followerAvg" => 501.319205]
]
Using a select function in conjunction with groupBy.
SELECT age, SUM(follower) as followerSum GROUP BY age;
$users = $userQueryBuilder
->select(["age", "followerSum" => ["SUM" => "follower"]])
->groupBy(["age"])
->getQuery()
->fetch();
[
["age" => 18, "followerSum" => 901],
["age" => 20, "followerSum" => 435],
...
]
Using a custom select function (closure).
Every codeblock below does the same thing.
For demonstration purposes this example is very simple and you can achieve the same result with the CONCAT
function!
The result of the query is shown at the bottom of this section.
$users = $userQueryBuilder
->select([
"age",
"fullName" => function($user){
return $user['forename'] . ', ' . $user['surname'];
},
"follower"
])
->getQuery()
->fetch();
// Extract the custom function from the select array
$fullNameSelectFunction = function($user){
return $user['forename'] . ', ' . $user['surname'];
}
$users = $userQueryBuilder
->select([
"age",
"fullName" => $fullNameSelectFunction,
"follower"
])
->getQuery()
->fetch();
// Use external variables
$nameSeparator = ', ';
$fullNameSelectFunction = function($user) use ($nameSeparator){
return $user['forename'] . $nameSeparator . $user['surname'];
};
$users = $userQueryBuilder
->select([
"age",
"fullName" => $fullNameSelectFunction,
"follower"
])
->getQuery()
->fetch();
[
["age" => 18, "fullName" => "Tom, Great", "follower" => 901],
["age" => 20, "fullName" => "John, West", "follower" => 435],
...
]
except(...)
works as the opposite of select()
method. Use it when you don't want a property in the result. For example, if you don't want the password field in your returned data set.
function except(array $fieldNames): QueryBuilder
Specify specific fields to exclude from the output.
Retrieve all information of an user except its _id and name.
$users = $userQueryBuilder
->except(["_id", "name"])
->getQuery()
->fetch();
// output: [["age" => 28], ["age" => 18]]
Retrieve all information of an user except its name and streetNumber.
Note: The steet number is stored in a nested array.
$users = $userQueryBuilder
->except(["name", "address.streetNumber"])
->getQuery()
->fetch();
[ ["_id" => 2, "address" => [ "street" => "Firststreet" ]], ... ]
Skip works as the OFFSET clause of SQL. You can use this to skip a specific amount of documents.
function skip(int|string $skip = 0): QueryBuilder
The value of how many documents should be skipped.
Retrieve all users except the first 10 found.
$users = $userQueryBuilder
->skip(10)
->getQuery()
->fetch();
$users = $userQueryBuilder
->skip('10')
->getQuery()
->fetch();
Works as the LIMIT clause of SQL. You can use this to limit the results to a specific amount.
function limit(int|string $limit = 0): QueryBuilder
Limit the amount of documents in the result set. Has to be greater than 0.
Retrieve just the first ten users.
$users = $userQueryBuilder
->limit(10)
->getQuery()
->fetch();
$users = $userQueryBuilder
->limit('10')
->getQuery()
->fetch();
Works as the ORDER BY clause of SQL. With this method you can sort the result. You can use this method to sort the result by one or multiple fields.
function orderBy(array $criteria): QueryBuilder
Name of the field that will be used to sort the result.
Either desc
for a descending sort or asc
for a ascending sort.
Retrieve all users sorted by their name.
$users = $userQueryBuilder
->orderBy(["name" => "asc"])
->getQuery()
->fetch();
[["_id" => 13, "name" => "Anton"], ["_id" => 2, "name" => "Berta"], ...]
Retrieve all users sorted by their name and age.
$users = $userQueryBuilder
->orderBy(["name" => "asc", "age" => "asc"])
->getQuery()
->fetch();
[
["_id" => 13, "name" => "Anton", "age" => 20],
["_id" => 4, "name" => "Aragon", "age" => 16],
["_id" => 2, "name" => "Aragon", "age" => 17],
...
]
Group documents using one or multiple fields.
function groupBy(array $groupByFields, string $counterKeyName = null, bool $allowEmpty = false): QueryBuilder
Note: If you use select in conjunction with groupBy you have to explicitly specify all fields you want in the result set.
One or multiple fields to group documents by.
If counterKeyName is not null a counter that represents how many documents are grouped together will be applied to the result documents automatically using the given string as the name for the counter.
Note: You can not specify an alias with the select method for this new field, because it is already an alias!
If true
documents having an empty value like null, in the fields that are relevant for grouping, will be grouped together.
Group active products based on category.
SELECT category, COUNT(*) AS productAmount WHERE active = true GROUP BY category;
$productQueryBuilder
->where(["active", "=", true])
->groupBy(["category"], "productAmount")
->getQuery()
->fetch();
[
["category" => "pants", "productAmount" => 200],
["category" => "shirts", "productAmount" => 29],
...
]
Group active products based on category and return the sum of the views.
SELECT
category AS Category,
subcategory,
COUNT(*) AS productAmount,
SUM(views) AS views
WHERE
active = true
GROUP BY
category, subcategory
ORDER BY
category ASC, subcategory DESC;
$productQueryBuilder
->select([
"Category" => "category",
"subcategory",
"productAmount",
"views" => ["SUM" => "views"]
])
->where(["active", "=", true])
->groupBy(
["category", "subcategory"],
"productAmount"
)
->orderBy(["category" => "ASC", "subcategory" => "DESC"])
->getQuery()
->fetch();
[
["Category" => "pants", "subcategory" => "shorts", "productAmount" => 20, "views" => 212],
["Category" => "pants", "subcategory" => "jeans", "productAmount" => 83, "views" => 331],
...
]
The having()
method was added, because the where()
and orWhere()
methods do not consider select functions and grouped documents.
As you can see in the Order of query execution documentation you can use having()
to further filter the result after join()
, search()
, select()
and groupBy()
is applied.
function having(array $criteria): QueryBuilder
One or multiple where conditions.
Visit the $criteria documentation page to learn more on how to define conditions.
Group active products based on category and just retrieve categories with views higher than 500 and lower than 1000.
SELECT
category,
SUM(views) AS totalViews
WHERE
active = true
GROUP BY
category
HAVING
totalViews > 500 AND totalViews < 1000;
$productQueryBuilder
->select([ "category", "totalViews" => ["SUM" => "views"] ])
->where(["active", "=", true])
->groupBy(["category"])
->having([
["totalViews", ">", 500],
"AND", // <- Optional
["totalViews", "<", 1000]
])
->getQuery()
->fetch();
[
["category" => "pants", "totalViews" => 502],
["category" => "shirts", "totalViews" => 931],
...
]
Group active products based on category and just retrieve categories with views higher than 500 and lower than 1000.
SELECT
name,
ROUND(price, 0) AS roundedPrice
WHERE
active = true
HAVING
roundedPrice > 500 AND roundedPrice < 1000;
$productQueryBuilder
->select([ "name", "roundedPrice" => ["ROUND" => ["price", 0]] ])
->where(["active", "=", true])
->having([
["roundedPrice", ">", 500],
"AND", // <- Optional
["roundedPrice", "<", 1000]
])
->getQuery()
->fetch();
[
["name" => "Best Schoe", "roundedPrice" => 519],
["name" => "Black Jeans", "roundedPrice" => 549],
...
]
Do a fulltext like search against one or multiple fields.
function search(array|string $fields, string $query, array $options = []): QueryBuilder
Please visit the "Searching" documentation for more details and examples!
An array containg all fields to search against.
As our documents are basically JSON documents it also could have nested properties.
To target nested properties we use a single dot between the property/field name.
Example: address.street
The search query.
Configure the search behaviour on a query by query base.
The distinct method is used to retrieve unique values from the store. It will remove all the duplicate documents while fetching data from a store.
function distinct(array|string $fields): QueryBuilder
Specify one or multiple fields you want to be distinct.
Retrieve all users, but just the first user if there is another one with the same name.
// providing a string
$distinctUsers = $userQueryBuilder
->distinct("name")
->getQuery()
->fetch();
// providing an array
$distinctUsers = $userQueryBuilder
->distinct(["name"])
->getQuery()
->fetch();
The QueryBuilder provides the useCache
, disableCache
and regenerateCache
methods to manage caching on a query by query base.
Please visit the Cache Management page for more details.
This method is used to join two or multiple stores together.
For more details please visit the Join Stores page.
function join(Closure $joinedStore, string $propertyName): QueryBuilder
🚨 Deprecated since version 2.3, use
where
andorWhere
instead!
With the nestedWhere(...)
method you are able to make complex nested where statements.
function nestedWhere(array $conditions): QueryBuilder
[
"OUTERMOST_OPERATION" => [
// conditions
]
];
Multiple where conditions.
The criteria can be nested as much as needed.
Small Example:
[ "OUTERMOST_OPERATION" => [ [$fieldName, $condition, $value], "OPERATION", [$fieldName, $condition, $value] ] ]
The field name argument is the property that we want to check in our data object.
As our data object is basically a JSON document so it could have nested properties.
To target nested properties we use a single dot between the property/field name.
Example: From our above users object if we want to target the "country" property of a user, then we would pass location.country
in this argument, because "location" is the parent property of the "country" property in our data object.
To apply the comparison filters we use this argument. The condition is case insensitive.
Allowed conditional operators are:
=
Match equal against data.===
Match equal against data.==
Match equal against data. (Type unsafe comparison)!=
Match not equal against data.!==
Match not equal against data.<>
Match not equal against data. (Type unsafe comparison)>
Match greater than against data.>=
Match greater equal against data.<
Match less than against data.<=
Match less equal against data.LIKE
Match using wildcards.NOT LIKE
Match using wildcards. Negation of result.%
Represents zero or more characters_
Represents a single character[]
Represents any single character within the brackets^
Represents any character not in the brackets-
Represents a range of charactersIN
$value has to be an array
. Check if data is in given list. NOT IN
$value has to be an array
. Check if data is not in given list. BETWEEN
$value has to be an array
with a lengeth of 2.NOT BETWEEN
$value has to be an array
with a length of 2.Data that will be checked against the property value of the JSON documents.
Can also be a DateTime object if the property value of the JSON document is convertable into a DateTime object. Refer to the Working with Dates documentation to learn more about that.
Can be set to AND
or OR
.
It is optional and will default to an AND
operation.
The outermost operation is optional and does specify how the given conditions are connected with other conditions, like the ones that are specified using the where
, orWhere
, in or notIn
methods.
Can be set to AND
or OR
.
The operation is used to connect multiple conditions.
Retrieve all users whos name start with "a" or "b", that have products.totalSaved > 10 and products totalBought > 10 and are between 16 and 20 or 24 and 28 years old.
WHERE
(products.totalSaved > 10 AND products.totalBought > 20)
AND // <- Outermost Operation
(
(name like 'a%' OR name like 'b%')
AND
(
(age >= 16 AND age < 20)
OR
(age >= 24 AND age < 28))
)
$users = $userQueryBuilder
->where(
[
["products.totalSaved", ">", 10],
["products.totalBought", ">", 20]
]
)
->nestedWhere(
[
"AND" => [ // <- Outermost operation
[
[ "name", "like", "a%" ], "OR", [ "name", "like", "b%" ]
],
"AND",
[
[
[ "age", ">=", 16 ], "AND", [ "age", "<", 20 ]
],
"OR",
[
[ "age", ">=", 24 ], "AND", [ "age", "<", 28 ]
]
]
]
]
)
->getQuery()
->fetch();
Retrieve all users that have the status "premium", live in "london" and are between the age of 16 and 20 or older than 30.
$users = $userQueryBuilder
->where(
[
["status", "=", "premium"]
]
)
->nestedWhere(
[
[ // <- Outermost operation is optional (Default: "AND")
[ "city", "=", "london" ]
"AND",
[
[
[ "age", ">=", 16 ], "AND", [ "age", "<", 20 ]
],
"OR",
[ "age", ">", 30 ]
]
]
]
)
->getQuery()
->fetch();
🚨 Deprecated since version 2.3, use
where
andorWhere
instead!
in(...) works as the IN clause of SQL. SleekDB supports multiple IN as object chain for different fields.
in(string $fieldName, array $values = []): QueryBuilder
The field name argument is the property that we want to check in our data object.
You can also provide have nested properties separated with a dot ".".
This argument takes an array to match documents within its items.
Retrieve all users that are from the country BD, CA, SE or NA.
$users = $userQueryBuilder
->in("country", ["BD", "CA", "SE", "NA"])
->getQuery()
->fetch();
Retrieve all users that are from country BD, CA, SE, NA and are at the age of 18, 20, 23 or 30.
$users = $userQueryBuilder
->in("country", ["BD", "CA", "SE", "NA"])
->in("age", [18, 20, 23, 30])
->getQuery()
->fetch();
🚨 Deprecated since version 2.3, use
where
andorWhere
instead!
notIn(...) works as the opposite of in() method.
It will filter out all documents that has particular data items from the given array. SleekDB supports multiple notIn as object chain for different fields.
The notIn() method takes two arguments as in() method, those are:
function notIn(string $fieldName, array $values = []): QueryBuilder
The field name argument is the property that we want to check in our data object.
You can also provide have nested properties separated with a dot ".".
This argument takes an array to match documents within its items.
Retrieve all users that are not from the coutry IN, KE or OP.
$users = $userQueryBuilder
->notIn("country", ["IN", "KE", "OP"])
->getQuery()
->fetch();
Retrieve all users that are not from the country IN, KE or OP and do not have products.totalSaved 100, 150 or 200.
$users = $userQueryBuilder
->notIn("country", ["IN", "KE", "OP"])
->notIn("products.totalSaved", [100, 150, 200])
->getQuery()
->fetch();
The QueryBuilder
class does provide a couple of methods to manage caching on a query by query bases
.
By default caching is enabled store wide and does not need to be enabled on a query by query bases. For more information please look into the store configurations.
Create a QueryBuilder:
$userQueryBuilder = $userStore->createQueryBuilder();
With the useCache method you can activate caching
and define the cache lifetime
on a query by query bases.
function useCache(int $lifetime = null): QueryBuilder
The lifetime of the cache.
null
(Default)int
0
means infinite lifetime.Retrieve and cache 20 users that are active.
The results in this example will use caching untill any documents gets updated/ deleted or inserted.
// fetch and insert in cache
$users = $userQueryBuilder
->where( ['active', '=', 1] )
->limit( 20 )
->useCache() // Use caching with lifetime = null
->getQuery()
->fetch();
// retrieve from cache
$users = $userQueryBuilder
->where( ['active', '=', 1] )
->limit( 20 )
->useCache()
->getQuery()
->fetch();
// insert a new user (cache with no lifetime will be deleted)
$newUser = ["name" => "Max", "active" => 0];
$userStore->insert($newUser);
// fetch and insert in cache
$users = $userQueryBuilder
->where( ['active', '=', 1] )
->limit( 20 )
->useCache()
->getQuery()
->fetch();
Retrieve and cache result for 2 minutes, 20 users that are active.
// fetch and insert in cache
$users = $userQueryBuilder
->where( ['active', '=', 1] )
->limit( 20 )
->useCache(120) // 2 minutes = 120 seconds
->getQuery()
->fetch();
Retrieve and cache result forever, 20 users that are active.
// fetch and insert in cache
$users = $userQueryBuilder
->where( ['active', '=', 1] )
->limit( 20 )
->useCache(0) // 0 means infinite caching
->getQuery()
->fetch();
Disable the build in caching solution on a query by query bases.
By default caching is enabled store wide. If you want to disable caching store instead of disabling it on a query by query bases visit the store configurations page.
function disableCache(): QueryBuilder
Retrieve 20 users that are active and do not use caching.
// fetch and insert in cache
$users = $userQueryBuilder
->where( ['active', '=', 1] )
->limit( 20 )
->disableCache()
->getQuery()
->fetch();
Regenerate the cache of a query regardless of its lifetime.
function regenerateCache(): QueryBuilder
// cache with infinite lifetime
$users = $userQueryBuilder
->where( ['active', '=', 1] )
->limit( 20 )
->useCache(0)
->getQuery()
->fetch();
// returns documents not from cache and caches results for 20 seconds
$users = $userQueryBuilder
->where( ['active', '=', 1] )
->limit( 20 )
->useCache(20)
->regenerateCache()
->getQuery()
->fetch();
With SleekDB it is easy to join multiple stores. You can add more than one join
as well as nested join
methods are also supported!
Query into the "users" store to fetch all users. Each user also will get an additional property called "comments", that contains all comments of the user.
$usersWithComments = $userStore
->createQueryBuilder()
->join(function($user) use ($commentStore) {
return $commentStore->findBy(["user", "=", $user["_id"]]);
}, "comments")
->getQuery()
->fetch();
[
[
"_id" => 1,
"name" => "John",
"comments" => [
[
"_id" => 1,
"articleId" => 3
"content" => "I love SleekDB"
],
...
]
],
...
]
To join stores we use the join() method of the QueryBuilder object.
The join() method takes two arguments, those are:
function join(Closure $joinFunction, string $propertyName): QueryBuilder
This anonymous function has to return the result of an executed sub query
or prepare a sub query for the join and return it as a QueryBuilder object
.
Name of the new property added to each document.
To get the users with their comments we would join like this:
use SleekDB\Store;
$userStore = new Store("users", $dataDir);
$commentStore = new Store("comments", $dataDir);
$users = $usersStore
->createQueryBuilder()
->join(function($user) use ($commentStore){
// returns result
return $commentStore->findBy([ "userId", "=", $user["_id"] ]);
}, "comments")
->getQuery()
->fetch();
// or
$users = $userStore
->createQueryBuilder()
->join(function($user) use ($commentStore){
// returns Querybuilder
return $commentStore
->createQueryBuilder()
->where([ "userId", "=", $user["_id"] ]);
}, "comments")
->getQuery()
->fetch();
Use multiple join()
.
Retrieve all users with their comments and their articles.
use SleekDB\Store;
$userStore = new Store("users", $dataDir);
$commentStore = new Store("comments", $dataDir);
$articleStore = new Store("articles", $dataDir);
$users = $userStore
->createQueryBuilder()
->join(function($user) use ($commentStore) {
// returns result
return $commentStore->findBy([ "userId", "=", $user["_id"] ]);
}, "comments")
->join(function($user) use ($articleStore) {
// returns result
return $articleStore->findBy([ "author", "=", $user["_id"] ]);
}, "articles")
->getQuery()
->fetch();
Use join()
within a join sub query.
Retrieve all users with their created articles containing the comments.
use SleekDB\Store;
$userStore = new Store("users", $dataDir);
$commentStore = new Store("comments", $dataDir);
$articleStore = new Store("articles", $dataDir);
$users = $userStore
->createQueryBuilder()
->join(function($user) use ($articleStore, $commentStore){
// returns QueryBuilder
return $articleStore
->createQueryBuilder()
->where([ "author", "=", $user["_id"] ])
->join(function($article) use ($commentStore){
// returns result
return $commentStore->findBy("articleId", "=", $article["_id"]);
}, "comments");
}, "articles")
->getQuery()
->fetch();
With the Query object you can execute a query.
To get the Query object use the getQuery
method of the QueryBuilder class:
$userQuery = $userQueryBuilder->getQuery();
Execute a query and retrieve an array containing all documents found.
function fetch(): array
An array containing all documents found or an empty array.
Retrieve all users that are located in london.
$user = $userQueryBuilder
->where([ "city", "=", "london" ])
->getQuery()
->fetch();
[
[
"_id" => 1,
"name" => "John",
"city" => "london"
],
[
"_id" => 4,
"name" => "Max",
"city" => "london"
],
...
]
It is more efficient than fetch
but one caveat is that the orderBy
will not work when using this method to get the very first item.
function first(): array
Returns the very first document discovered or an empty array if nothing found.
$user = $userQueryBuilder
->where([ "email", "=", "foo@bar.com" ])
->getQuery()
->first();
[
"_id" => 1,
"name" => "John",
"email" => "foo@bar.com"
]
It is more efficient than using fetch
to check if some data exists or not. For example, you may use exists method to check if a username or email address already exists or not.
function exists(): bool
Returns true
if a document exists and false
if no document found.
$userNameExists = $userQueryBuilder
->where([ "username", "=", "foobar" ])
->getQuery()
->exists();
Update one or multiple documents based on a given query.
function update(array $updatable, bool $returnUpdatedDocuments = false): array|bool
An array containing the properties to update.
Update of nested values possible by using a dot between fieldNames (Example 2)
If a field does not exist in a document, it will be added.
If $returnUpdatedDocuments = false:
Returns true
on success and false
if no documents found to update.
If $returnUpdatedDocuments = true:
Returns updated documents
on success and false
if no documents found to update.
Set the status of all users that are located in london to VIP.
$userStore
->createQueryBuilder()
->where([ "city", "=", "london" ])
->getQuery()
->update([ "status" => "VIP" ]);
Change the street of the user with _id = 24.
Note: The street is in a nested array.
$userStore
->createQueryBuilder()
->where("city", "=", "london")
->update([ "address.street" => "first street" ]);
[
[
"city" => "london".
"address" => [
"street" => "first street",
"postalCode" => "41824"
],
...
],
...
]
Delete one or multiple documents based on a given query.
function delete(int $returnOption = Query::DELETE_RETURN_BOOL)
Different return options provided with constants of the Query
class
Query::DELETE_RETURN_BOOL
(Default)Query::DELETE_RETURN_RESULTS
Query::DELETE_RETURN_COUNT
This method returns based on the given return option either boolean
, int
or array
.
Delete all users that are not active.
$result = $userStore
->createQueryBuilder()
->where([ "active", "=", false ])
->getQuery()
->delete();
// output: true
Delete all users that are not active and retrieve the amount of deleted users.
use SleekDB\Query;
$result = $userStore
->createQueryBuilder()
->where([ "active", "=", false ])
->getQuery()
->delete(Query::DELETE_RETURN_COUNT);
// output: 14
Delete all users that are not active and retrieve the deleted users.
use SleekDB\Query;
$result = $userStore
->createQueryBuilder()
->where([ "active", "=", false ])
->getQuery()
->delete(Query::DELETE_RETURN_RESULT);
// output: [ ["_id" => 1, "name" => "Max"], ["_id" => 4, "name" => "John"], ... ]
Remove fields of one or multiple documents based on current query.
function removeFields(array $fieldsToRemove): array|false
An array containing the fields to remove.
Update of nested values possible by using a dot between fieldNames (Example 2)
Returns updated documents
on success and false
on failure.
Remove the status field of all users that are located in london.
$userStore
->createQueryBuilder()
->where([ "city", "=", "london" ])
->getQuery()
->removeFields([ "status" ]);
Remove the street field of the user with _id = 24.
Note: The street is in a nested array.
$userStore
->createQueryBuilder()
->where("city", "=", "london")
->removeFields([ "address.street" ]);
[
[
"city" => "london".
"address" => [
"postalCode" => "41824"
],
...
],
...
]
SleekDB accepts instances of PHP's DateTime class to filter data.
Means you can check against an DateTime object.
The methods that accept DateTime objects as a value to check against are:
The conditions you can use DateTime objects with are:
=
===
==
(Type unsafe comparison)!=
!==
<>
(Type unsafe comparison)>
>=
<
<=
IN
NOT IN
BETWEEN
NOT BETWEEN
CONTAINS
NOT CONTAINS
require_once './vendor/autoload.php';
use SleekDB\Store;
// create store
$databaseDirectory = __DIR__ . "/database";
$newsStore = new Store("news", $databaseDirectory);
// Convert the date-strings to timestamps
$startDate = new \DateTime("2020-12-01");
$endDate = new \DateTime("2021-01-04");
// Get result
// WHERE releaseDate >= "2020-12-01" AND releaseDate <= "2021-01-04"
$news = $newsStore->findBy([ "releaseDate", "BETWEEN", [ $startDate, $endDate ] ] );
Internally SleekDB converts the value of the field ("releaseDate" in the example above) into a DateTime object and compares it with the given DateTime object.
That means if there is something stored in the field (for example "releaseDate") that can not be converted into a DateTime object SleekDB will throw an InvalidArgumentException.
Refer to the official PHP documentation to learn more about DateTime objects.
SleekDB already handles the following values correctly, so no error will be thrown and you can use them for example to clarify that there is no release date for that document.
You should either store the date and time as a string or as a timestamp.
We recommend storing the date and time as a timestamp.
Store date as a string.
$newArticle = [
"author" => "John",
"title" => "Why everybody love SleekDB",
"content" => "Because it's the best!",
"releaseDate" => "2021-01-17"
];
$newArticle = $newsStore->insert($newArticle);
Store date as a timestamp.
$releaseDate = new \DateTime("2021-01-17");
$newArticle = [
"author" => "John",
"title" => "Why everybody love SleekDB",
"content" => "Because it's the best!",
"releaseDate" => $releaseDate->getTimestamp()
];
$newArticle = $newsStore->insert($newArticle);
Store current date and time.
$releaseDate = new \DateTime();
$newArticle = [
"author" => "John",
"title" => "Why everybody love SleekDB",
"content" => "Because it's the best!",
"createdAt" => $releaseDate->format("Y-m-d H:i:s")
];
// OR
$newArticle = [
"author" => "John",
"title" => "Why everybody love SleekDB",
"content" => "Because it's the best!",
"createdAt" => $releaseDate->getTimestamp()
];
$newArticle = $newsStore->insert($newArticle);
If you don't want to use DateTime objects to filter data you don't have to.
To filter data without the need of DateTime objects we have to store the dates as a timestamp.
require_once './vendor/autoload.php';
use SleekDB\Store;
// create store
$databaseDirectory = __DIR__ . "/database";
$newsStore = new Store("news", $databaseDirectory);
// Insert an article
$releaseDate = new \DateTime("2021-01-17");
$newArticle = [
"author" => "John",
"title" => "Why everybody love SleekDB",
"content" => "Because it's the best!",
"createdAt" => $releaseDate->getTimestamp()
];
$newArticle = $newsStore->insert($newArticle);
// retrieve articles
$datesToFilter = [
(new \DateTime("2020-12-01"))->getTimestamp(),
(new \DateTime("2021-01-04"))->getTimestamp()
(new \DateTime("2021-01-19"))->getTimestamp()
];
$news = $newsStore->findBy([ "releaseDate", "IN", $datesToFilter ] );
// WHERE releaseDate IN ("2020-12-01", "2021-01-04", "2021-01-19")
SleekDB introduces with version 2.7 enhanced search functionality with search scores, different search algorithms and two search modes.
Searching through documents was never so easy and good before!
search()
method of QueryBuilder
classsearch()
method of Store
classYou can configure the bahaviour of SleekDB regarding searching Store wide and on a query by query bases.
// Store wide configuration when creating a store
$configuration = [
"search" => [
"min_length" => 2,
"mode" => "or",
"score_key" => "scoreKey",
"algorithm" => Query::SEARCH_ALGORITHM["hits"]
]
];
$store = new Store("users", __DIR__ . "/database", $configuration);
// On a query by query bases
$searchOptions = [
"minLength" => 2,
"mode" => "or",
"scoreKey" => "scoreKey",
"algorithm" => Query::SEARCH_ALGORITHM["hits"]
];
$userStore = new Store("users", __DIR__ . "/database");
$userStore->createQueryBuilder()
->search(["profileDescription"], "SleekDB", $searchOptions)
->getQuery()
->fetch();
It has to be an integer
> 0.
The minimum length of a word within the search query that will be used for searching.
If it is for example set to the value of 2 (default) and the user searches for: "I love SleekDB".
The words "love" and "SleekDB" will be used for searching and the word "I" will be ignored.
That means with the configuration of 2 all words >= 2 are considered when searching.
Has to be a string
.
SleekDB provides two modes.
Can be a string
or null
.
If this configuration is null
no field containing the score will be applied to the documents.
If it is a string
a new field with the specified field name will be applied to every document, which will contain the search score and can be used to for example sort the documents.
SleekDB provides 4 search algorithms. hits
, hits_prioritize
, prioritize
, prioritize_position
These algorithms change the score generation.
They are available as a constant of the Query
class.
Example:
Query::SEARCH_ALGORITHM["hits"] // default
With this mode a field has to contain one of the words in the search query to be considered as a search result.
With this search mode a field has to contain all words of the search query to be considered as a search result.
For example our query is "Where School" and we have two values to check agains:
Query::SEARCH_ALGORITHM["hits"]
This is the default algorithm.
The score is based on the amount of search hits.
Query::SEARCH_ALGORITHM["hits_prioritize"]
The score is based on the amount of search hits but if two or more documents have the same amount of search hits the order of the given fields to search through is taken into consideration.
For example we search through two fields: ["title", "content"]
Then the title field gets more weight.
If one document has two hits in the title and three in the content field, it will have a lesser score than a document that has three hits in the title and two in the content field.
Query::SEARCH_ALGORITHM["prioritize"]
The order of the fields are a big part of the score.
For example we search through two fields: ["title", "content"]
If one document has one search hit in the title field it will have a higher score than a document that has no search hits in the title field but 61 search hits in the content field.
Query::SEARCH_ALGORITHM["prioritize_position"]
This algorithm does the same as the "prioritize" algorithm.
The difference is, that it also consideres the position of the first search hit when generating a score.
For example we have two documents with the same amount of search hits in the same fields.
The first one has the first search hit at position 3 and the second one at position 4. Then the first document will have a higher score.
QueryBuilder
classDo a fulltext like search against one or multiple fields.
function search(array|string $fields, string $query, array $options = []): QueryBuilder
An array containg all fields to search against.
As our documents are basically JSON documents it also could have nested properties.
To target nested properties we use a single dot between the property/field name.
Example: address.street
The search query.
Configure the search behaviour on a query by query base.
Search phrase: "SleekDB is the best database solution".
Search through all news articles "title", "description" and "content" fields, sort the result by relevance and dont return the score key field.
$newsStore = new Store("news", __DIR__ . "/database");
$searchQuery = "SleekDB is the best database solution";
$result = $newsStore->createQueryBuilder()
->search(["title", "description", "content"], $searchQuery)
->orderBy(["searchScore" => "DESC"]) // sort result
->except(["searchScore"]) // exclude field from result
->getQuery()
->fetch();
Search phrase: "SleekDB is the best database solution".
Search through all news articles "title.mainTitle" and "content" fields, sort the result by relevance and exclude the score key from the result.
Also change the search algorithm to "prioritize" for this query and limit the result to 20.
// Example article:
// [
// "_id" => 1,
// "title" => [
// "mainTitle" => "SleekDB is the best database!"
// ],
// "content" => "A NoSQL dabase completely made with PHP.",
// ]
$newsStore = new Store("news", __DIR__ . "/database");
$searchQuery = "SleekDB is the best database solution";
$searchOptions = [
"algorithm" => Query::SEARCH_ALGORITHM["prioritize"]
];
$result = $newsStore
->search(["title.mainTitle", "content"], $searchQuery, $searchOptions)
->orderBy(["searchScore" => "DESC"])
->except(["searchScore"])
->limit(20)
->getQuery()
->fetch();
Store
classDo a fulltext like search against one or multiple fields.
function search(array $fields, string $query, array $orderBy = null, int $limit = null, int $offset = null): array
An array containg all fields to search against.
As our documents are basically JSON documents it also could have nested properties.
To target nested properties we use a single dot between the property/field name.
Example: address.street
The search query.
Sort the result by one or multiple fields.
Limit the result to a specific amount.
Skip a specific amount of documents.
Will return an array
containg all documents having at least one search hit or an empty array
.
Search phrase: "SleekDB is the best database solution".
Search through all news articles "title", "description" and "content" fields and sort the result by relevance.
$newsStore = new Store("news", __DIR__ . "/database");
$searchQuery = "SleekDB is the best database solution";
$result = $newsStore->search(["title", "description", "content"], $searchQuery, ["searchScore" => "DESC"]);
Search phrase: "SleekDB is the best database solution".
Search through all news articles "title.mainTitle", "title.subTitle" and "content" fields and sort the result by relevance.
// Example article:
// [
// "_id" => 1,
// "title" => [
// "mainTitle" => "SleekDB is the best database!",
// "subTitle" => "Just believe me!"
// ],
// "content" => "A NoSQL dabase completely made with PHP.",
// ]
$newsStore = new Store("news", __DIR__ . "/database");
$searchQuery = "SleekDB is the best database solution";
$result = $newsStore->search(
["title.mainTitle", "title.subTitle", "content"], // fields
$searchQuery, // query
["searchScore" => "DESC"] // orderBy
);
Each query will follow the below steps or execution life cycle.
The Store
class is the first, and in most cases also the only part you need to come in contact with.
The QueryBuilder
class is used to prepare a query. It can be retrieved with Store->createQueryBuilder()
.
The Query
class contains all information needed to execute the query and is used to do so.
The Query object
can be retrieved using the QueryBuilder->getQuery()
method.
The Cache
class handles everything regarding caching. It will decide when to cache or not to cache.
It is mainly designed for internal use but can be retrieved using Query->getCache()
.
The following diagram represents the internal order of execution when using the Query->fetch()
method.
The where
and orWhere
conditions are used to determine if a document will be a part of the result set or not.
If the WHERE
resulted in true
the document is checked against the result set using the fields defined with the distict()
method.
Now we have all documents in a result array.
All documents will be joined with one or multiple stores.
We go through all documents and check if they contain what we search, if not we remove them from the result array.
Apply select functions that do not reduce the result amount to one and apply their aliases.
Group documents by fields defined with groupBy()
.
Select specific fields and apply their aliases, if they have one.
Apply select functions that reduce result amount to one and their aliases.
Amount will not be reduced when using Group-By.
Functions that reduce the amount of the result set to one:
SUM
MAX
MIN
AVG
Check all documents using the criteria specified with having
.
Exclude/ Remove fields from all documents.
Sort the result array using one or multiple fields.
Skip a specific amount of documents. (Slice the result array)
Limit the amount of documents in result. (Slice the result array)
With the changeStore()
method of the Store
class you can change the destination of that store object.
That allows you to use one Store object to manage multiple stores.
function changeStore(string $storeName, string $dataDir = null, array $configuration = []): Store
Name of new store destination
If null previous database directory destination will be used.
If empty previous configurations remain.
/* create store */
$store = new Store("users", __DIR__ . "/database");
/* insert new user */
$store->insert(["username" => "admin"]);
/* change store destination */
$store->changeStore("alerts");
/* insert a new alert into the alerts store */
$store->insert(["content" => "new user with username admin added."]);
When you use the createQueryBuilder()
method you get a new QueryBuilder object, that means your conditions are reset to default.
You can keep the QueryBuilder object and reuse it to add new conditions.
$userQueryBuilder = $userStore->createQueryBuilder();
$userQueryBuilder->where([ 'products.totalBought', '>', 0 ]);
$userQueryBuilder->where([ 'products.totalSaved', '>', 0 ]);
// fetch all users that have totalBought > 0 and totalSaved > 0
$users = $userQueryBuilder->getQuery()->fetch();
// add new condition
$userQueryBuilder->where([ 'active', '=', true ]);
// fetch all users that have totalBought > 0, totalSaved > 0 and are active
$usersActive = $userQueryBuilder->getQuery()->fetch();
If you want to keep the conditions and perform additional operations then you may want to keep the Query object.
Here is an example showing how you may fetch data and then update on the discovered documents without running an additional query:
$userQuery = $userStore->createQueryBuilder()
->where('products.totalBought', '>', 0)
->where('products.totalSaved', '>', 0)
->getQuery();
// Fetch data.
$users = $userQuery->fetch();
// Update matched documents.
$userQuery->update([
'someRandomData' => '123',
]);
// Fetch data again.
$usersUpdated = $userQuery->fetch();
We assume you read the documentation so you can understand the following small code examples.
require_once "./vendor/autoload.php";
use SleekDB\Store;
use SleekDB\Query;
$databaseDirectory = __DIR__."/database";
// applying the store configuration is optional
$storeConfiguration = [
"auto_cache" => true,
"cache_lifetime" => null,
"timeout" => 120,
"primary_key" => "_id",
"search" => [
"min_length" => 2,
"mode" => "or",
"score_key" => "scoreKey",
"algorithm" => Query::SEARCH_ALGORITHM["hits"]
]
];
// creating a new store object
$userStore = new Store("users", $databaseDirectory, $storeConfiguration);
$newUser = [
"username" => "Bob",
"age" => 20,
"address" => [
"street" => "down street",
"streetNumber" => 12,
"postalCode" => "8174",
],
];
$newUser = $userStore->insert($newUser);
// Output user with its unique id.
header("Content-Type: application/json");
echo json_encode($newUser);
require_once "./vendor/autoload.php";
use SleekDB\Store;
use SleekDB\Query;
$databaseDirectory = __DIR__."/database";
// applying the store configuration is optional
$storeConfiguration = [
"auto_cache" => true,
"cache_lifetime" => null,
"timeout" => 120,
"primary_key" => "_id",
"search" => [
"min_length" => 2,
"mode" => "or",
"score_key" => "scoreKey",
"algorithm" => Query::SEARCH_ALGORITHM["hits"]
]
];
// creating a new store object
$userStore = new Store("users", $databaseDirectory, $storeConfiguration);
$newUsers = [
[
"username" => "Lisa",
"age" => 17,
"address" => [
"street" => "up street",
"streetNumber" => 48,
"postalCode" => "1822",
],
],
[
"username" => "Bob",
"age" => 20,
"address" => [
"street" => "down street",
"streetNumber" => 12,
"postalCode" => "8174",
],
]
];
$newUsers = $userStore->insertMany($newUsers);
// Output users with their unique id.
header("Content-Type: application/json");
echo json_encode($newUsers);
require_once "./vendor/autoload.php";
use SleekDB\Store;
use SleekDB\Query;
$databaseDirectory = __DIR__."/database";
// applying the store configuration is optional
$storeConfiguration = [
"auto_cache" => true,
"cache_lifetime" => null,
"timeout" => 120,
"primary_key" => "_id",
"search" => [
"min_length" => 2,
"mode" => "or",
"score_key" => "scoreKey",
"algorithm" => Query::SEARCH_ALGORITHM["hits"]
]
];
// creating a new store object
$userStore = new Store("users", $databaseDirectory, $storeConfiguration);
$whereCondition = [
["location", "IN", ["new york", "london"]],
"OR",
["age", ">", 29]
];
// Pagination
$page = 1;
$limit = 10;
$skip = ($page - 1) * $limit;
// order by _id and limit result to 10
$result = $userStore->findBy($whereCondition, ["_id" => "DESC"], $limit, $skip);
// Output
header("Content-Type: application/json");
echo json_encode($result);
require_once "./vendor/autoload.php";
use SleekDB\Store;
use SleekDB\Query;
$databaseDirectory = __DIR__."/database";
// applying the store configuration is optional
$storeConfiguration = [
"auto_cache" => true,
"cache_lifetime" => null,
"timeout" => 120,
"primary_key" => "_id",
"search" => [
"min_length" => 2,
"mode" => "or",
"score_key" => "scoreKey",
"algorithm" => Query::SEARCH_ALGORITHM["hits"]
]
];
// creating a new store object
$userStore = new Store("users", $databaseDirectory, $storeConfiguration);
// Pagination
$page = 1;
$limit = 10;
$skip = ($page - 1) * $limit;
$result = $userStore->createQueryBuilder()
->where([
["location", "IN", ["new york", "london"]],
"OR",
["age", ">", 29]
])
->orderBy(["_id" => "DESC"])
->limit($limit)
->skip($skip)
->getQuery()
->fetch();
// Output
header("Content-Type: application/json");
echo json_encode($result);
require_once "./vendor/autoload.php";
use SleekDB\Store;
use SleekDB\Query;
$databaseDirectory = __DIR__."/database";
// applying the store configuration is optional
$storeConfiguration = [
"auto_cache" => true,
"cache_lifetime" => null,
"timeout" => 120,
"primary_key" => "_id",
"search" => [
"min_length" => 2,
"mode" => "or",
"score_key" => "scoreKey",
"algorithm" => Query::SEARCH_ALGORITHM["hits"]
]
];
// creating a new store object
$userStore = new Store("users", $databaseDirectory, $storeConfiguration);
$result = $userStore->createQueryBuilder()
->where([
["location", "IN", ["new york", "london"]],
"OR",
["age", ">", 29]
])
->orderBy(["_id" => "DESC"])
->getQuery()
->update(["status" => "VIP"]);
// Output
header("Content-Type: application/json");
echo json_encode($result);
require_once "./vendor/autoload.php";
use SleekDB\Store;
use SleekDB\Query;
$databaseDirectory = __DIR__."/database";
// applying the store configuration is optional
$storeConfiguration = [
"auto_cache" => true,
"cache_lifetime" => null,
"timeout" => 120,
"primary_key" => "_id",
"search" => [
"min_length" => 2,
"mode" => "or",
"score_key" => "scoreKey",
"algorithm" => Query::SEARCH_ALGORITHM["hits"]
]
];
// creating a new store object
$userStore = new Store("users", $databaseDirectory, $storeConfiguration);
// Pagination
$page = 1;
$limit = 10;
$skip = ($page - 1) * $limit;
$result = $userStore->createQueryBuilder()
->where([ "location", "IN", ["new york", "london"] ])
->select([ "age", "peopleCount", "followerAmount" => ["SUM" => "followers"] ])
->groupBy(["age"], "peopleCount", true)
->having([ ["followerAmount", ">", 100], "OR", ["age", "<", 16] ])
->orderBy(["followerAmount" => "DESC"])
->limit($limit)
->skip($skip)
->getQuery()
->fetch();
// Output
header("Content-Type: application/json");
echo json_encode($result);
require_once "./vendor/autoload.php";
use SleekDB\Store;
use SleekDB\Query;
$databaseDirectory = __DIR__."/database";
// applying the store configuration is optional
$storeConfiguration = [
"auto_cache" => true,
"cache_lifetime" => null,
"timeout" => 120,
"primary_key" => "_id",
"search" => [
"min_length" => 2,
"mode" => "or",
"score_key" => "scoreKey",
"algorithm" => Query::SEARCH_ALGORITHM["hits"]
]
];
// creating a new store object
$newsStore = new Store("news", $databaseDirectory, $storeConfiguration);
// Pagination
$page = 1;
$limit = 10;
$skip = ($page - 1) * $limit;
$searchQuery = "SleekDB best database";
$result = $newsStore->search(
["title", "content"],
$searchQuery,
["scoreKey" => "DESC"],
$limit,
$skip
);
// Output
header("Content-Type: application/json");
echo json_encode($result);
require_once "./vendor/autoload.php";
use SleekDB\Store;
use SleekDB\Query;
$databaseDirectory = __DIR__."/database";
// applying the store configuration is optional
$storeConfiguration = [
"auto_cache" => true,
"cache_lifetime" => null,
"timeout" => 120,
"primary_key" => "_id",
"search" => [
"min_length" => 2,
"mode" => "or",
"score_key" => "scoreKey",
"algorithm" => Query::SEARCH_ALGORITHM["hits"]
]
];
// creating a new store object
$newsStore = new Store("news", $databaseDirectory, $storeConfiguration);
// Pagination
$page = 1;
$limit = 10;
$skip = ($page - 1) * $limit;
$searchQuery = "SleekDB best database";
$result = $newsStore->createQueryBuilder()
->search(["title", "content"], $searchQuery)
->orderBy(["searchScore" => "DESC"])
->except(["searchScore"]) // remove score from result
->limit($limit)
->skip($skip)
->getQuery()
->fetch();
// Output
header("Content-Type: application/json");
echo json_encode($result);
With the Cache class you can control Caching in a deeper way.
That said, this class is mainly for internal use
and normally that kind of deep cache control is not needed.
⚠️️ Be careful when using the
Cache
object.
To retrieve the Cache object use the getCache
method of the Query
class.
$cache = $userQuery->getCache();
Returns the unique token for the current query, that will be used to save and retrieve a cache file.
function getToken(): string
The unique token for the current query as a string
.
Deletes the cache file for the current query.
function delete()
Delete all cache files of current store.
function deleteAll()
Delete all cache files that have no lifetime (null) of current store.
function deleteAllWithNoLifetime()
Set and cache the content for the current query / token.
function set(array $content)
The content that will be cached.
Retrieve the content of the cache file for the current query / token.
function get(): array|null
The content as an array
or null
if no cache file found.
Returns the path to the cache directory.
function getCachePath(): string
Set the lifetime for the current query / token.
function setLifetime(int|null $lifetime): Cache
int
in seconds. 0
means infinitenull
no cache lifetimeGet the lifetime for the current query / token.
function getLifetime(): int|null
Either int
>= 0 in seconds, where 0
means infinite, or null
.
This page contains a brief overview of all the classes and their methods. For more detailed information please visit the other documentation pages.
The Store class is the beginning point and handles everything regarding store configuration. It also provides all methods needed for simple queries.
Create a new Store object. (Internally it creates a new store folder if it doesn't exist)
function __construct(string $storeName, string $databasePath, array $configuration = [])
Returns a new QueryBuilder
object.
function createQueryBuilder(): QueryBuilder
Change the destination of the store object.
function changeStore(string $storeName, string $databasePath = null, array $configuration = []): Store
Create/Insert a new document in the store.
Returns the inserted document with it's new and unique _id.
function insert(array $data): array
Create/Insert many documents in the store.
Returns the inserted documents with their new and unique _id.
function insertMany(array $data): array
Retrieve all documents of that store.
function findAll(array $orderBy = null, int $limit = null, int $offset = null): array
Retrieve one document by its _id. Very fast because it finds the document by its file path.
function findById(int|string $id): array|null
Retrieve one or multiple documents.
function findBy(array $criteria, array $orderBy = null, int $limit = null, int $offset = null): array
Retrieve one document.
function findOneBy(array $criteria): array|null
Do a fulltext like search against one or multiple fields.
function search(array $fields, string $query, array $orderBy = null, int $limit = null, int $offset = null): array
Update parts of one document.
function updateById(int|string $id, array $updatable): array|false
Update one or multiple documents.
function update(array $updatable): bool
Create/Insert a new document in the store or update an existing one.
Returns the inserted/ updated document.
function updateOrInsert(array $data): array
Create/Insert many documents in the store or update existing ones.
Returns the inserted/ updated documents.
function updateOrInsertMany(array $data): array
Delete one or multiple documents.
function deleteBy(array $criteria, int $returnOption = Query::DELETE_RETURN_BOOL): bool|array|null
Delete one document by its _id. Very fast because it deletes the document by its file path.
function deleteById(int|string $id): bool
Remove fields from one document by its primary key.
function removeFieldsById($id, array $fieldsToRemove): array|false
Returns the amount of documents in the store.
function count(): int
Return the last created store object ID.
function getLastInsertedId(): int
Deletes a store and wipes all the data and cache it contains.
function deleteStore(): bool
Get the name of the store.
function getStoreName(): string
Get the path to the database folder.
function getDatabasePath(): string
Get the path to the store. (including store name)
function getStorePath(): string
Get the name of the field used as the primary key.
function getPrimaryKey(): string
This method is used internally. Returns if caching is enabled store wide.
function _getUseCache(): bool
This method is used internally. Returns the search options of the store.
function _getSearchOptions(): array
This method is used internally. Returns the store wide default cache lifetime.
function _getDefaultCacheLifetime(): null|int
Get the location (directory path) of the store.
🚨 Deprecated since version 2.7, use getDatabasePath
instead.
function getDataDirectory(): string
The QueryBuilder class handles everything regarding query creation like for example the where or join methods.
Create a new QueryBuilder object.
function __construct(Store $store)
Returns a new Query
object which can be used to execute the query build.
function getQuery(): Query
Select specific fields.
function select(string[] $fieldNames): QueryBuilder
Exclude specific fields.
function except(string[] $fieldNames): QueryBuilder
Add "where" condition to filter data. Can be used multiple times. All additional uses add an "and where" condition.
function where(array $conditions): QueryBuilder
Add or-where conditions to filter data.
function orWhere($conditions): QueryBuilder
Set the amount of data record to skip.
function skip(int|string $skip = 0): QueryBuilder
Set the amount of data record to limit.
function limit(int|string $limit = 0): QueryBuilder
Set the sort order.
function orderBy(array $criteria): QueryBuilder
Group documents using one or multiple fields.
function groupBy(array $groupByFields, string $counterKeyName = null, bool $allowEmpty = false): QueryBuilder
The having()
method was added, because the where()
and orWhere()
methods do not consider select functions and grouped documents.
As you can see in the Order of query execution documentation you can use having()
to further filter the result after join()
, search()
, select()
and groupBy()
is applied.
function having(array $criteria): QueryBuilder
Do a fulltext like search against one or more fields.
function search(string|array $fields, string $query, array $options = []): QueryBuilder
Join current store with another one. Can be used multiple times to join multiple stores.
function join(Closure $joinFunction, string $propertyName): QueryBuilder
Return distinct values.
function distinct(string|array $fields = []): QueryBuilder
Use caching for current query
function useCache(int $lifetime = null): QueryBuilder
Disable caching for current query.
function disableCache(): QueryBuilder
Re-generate the cache for the query.
function regenerateCache(): QueryBuilder
This method is used internally. Returns a an array used to generate a unique token for the current query.
function _getCacheTokenArray(): array
This method is used internally. Returns an array containing all information needed to execute an query.
function _getConditionProperties(): array
This method is used internally. Returns the Store object used to create the QueryBuilder object.
function _getStore(): Store
Add nested where conditions to filter data.
🚨 Deprecated since version 2.3, use where
and orWhere
instead
function nestedWhere($conditions): QueryBuilder
Add "in" condition to filter data.
🚨 Deprecated since version 2.4, use "in" condition instead
function in(string $fieldName, array $values = []): QueryBuilder
Add "not in" condition to filter data.
🚨 Deprecated since version 2.4, use "not in" condition instead
function notIn(string $fieldName, array $values = []): QueryBuilder
This class handles everything regarding query execution like fetch / first / exists.
Create a new Query object. (Internally it creates a new Cache object)
function __construct(QueryBuilder $queryBuilder)
Get the Cache
object.
function getCache(): Cache
Execute Query and get Results.
function fetch(): array
Check if data is found.
function exists(): bool
Return the first document. (More efficient than fetch
but orderBy
does not work)
function first(): array
Update one or multiple documents, based on the current query.
function update(array $updatable, bool $returnUpdatedDocuments = false): array|bool
Deletes matched documents.
function delete(int $returnOption = Query::DELETE_RETURN_BOOL): bool|array|int
Remove fields of one or multiple documents based on current query.
function removeFields(array $fieldsToRemove): bool
This class handles everything regarding caching like for example cache deletion.
Create a new Cache object.
function __construct(Query $storePath, array &$cacheTokenArray, int|null $cacheLifetime)
Retrieve the cache lifetime for current query.
function getLifetime(): null|int
Retrieve the path to cache folder of current store.
function getCachePath(): string
Retrieve the cache token used as filename to store cache file.
function getToken(): string
Delete all cache files for current store.
function deleteAll()
Delete all cache files with no lifetime (null) in current store.
function deleteAllWithNoLifetime()
Save content for current query as a cache file.
function set(array $content)
Retrieve content of cache file.
function get(): array|null
Delete cache file/s for current query.
function delete()
SleekDB 2.X comes with so many important optimizations and other features that make it faster and more mature. This is the recommended SleekDB release version for new projects, and if you are using an older version consider upgrading as soon as possible.
LIKE
and NOT LIKE
timeout
configurationSet it to false
and if needed use set_time_limit() in your own code!
false
if you can't use or don't want to use set_time_limit() function.CONCAT
, ROUND
and much more!CONTAINS
and NOT CONTAINS
conditions available!Store->updateOrInsert()
and Store->updateOrInsertMany()
methods!findAll()
now accept orderBy, limit and offset as optional parameters!limit()
and skip()
now accept strings!ext-mbstring
getDataDirectory()
method of the Store
class is now deprecated. Use getDatabasePath()
instead!search()
method for Store
class!===
!==
==
<>
search()
method of the QueryBuilder
classFor more details please visit the corresponding documentation pages:
For more details please visit the documentation of select.
groupBy
you can use functions!For more details please visit the documentation of except.
For more details please visit the documentation of update.
The following conditions can now be used with the findBy()
, findOneBy()
, deleteBy()
, where()
and orWhere()
methods!
BETWEEN
NOT BETWEEN
The methods that accept DateTime objects as a value to check against are:
findBy
findOneBy
deleteBy
where
orWhere
The conditions you can use DateTime objects with are:
=
!=
>
>=
<=
IN
NOT IN
BETWEEN
NOT BETWEEN
Visit our new Working with Dates documentation page to learn more about date handling.
They will be removed with the next major update.
Please use the new conditions "in"
and "not in"
instead.
Available with the findBy()
, findOneBy
, deleteBy()
, where()
and orWhere()
methods since version 2.3.
See #118 for more details.
We are sorry to deprecate the nestedWhere()
method so short after it's release.
It will be removed with the next major update.
Please use where()
and orWhere()
instead.
The findBy()
, findOneBy
, deleteBy()
, where()
and orWhere()
methods now can handle nested statements!
|
Fetch Data documentation |
|
Delete Data documentation |
|
QueryBuilder documentation |
not like
in
not in
These new conditions can now be used with the findBy()
, findOneBy()
, deleteBy()
, where()
and orWhere()
methods!
All condition methods are now logically connected and the order when using them is now important.
See #114 for more details.
Now the orderBy()
method of the QueryBuilder accepts multiple fields to sort the result.
Look at the updated orderBy section of the QueryBuilder documentation to learn more.
With the new added nestedWhere()
method you can now use much complexer where statements to filter data.
Look at the new nestedWhere section of the QueryBuilder documentation to learn more.
With the new primary_key
configuration you can now change the _id key name to everything you want. To see how to use the new configuration option visit the Configurations page.
Added better file locking support with reduced nested loops for finding documents. Added methods that can be used to easily find a document without searching for entire available JSON files of a store.
The support of PHP 5 is dropped to provide more modern features.
first()
exists()
select()
except()
distinct()
join()
findAll()
findById()
findBy()
findOneBy()
updateBy()
deleteBy()
deleteById()
We isolated most of the existing logics into different classes to make the codebase easy to understand and easy to maintain.
There was some concern among developers as it was lacking UNIT Testing, now its added!
We beliefe that downwards compatibility is very important.
That's why we always try our best to keep SleekDB as downwards compatible as possible and avoid breaking changes.
Unfortunatelly we had to refactor and rewrite the whole SleekDB project to make it future proof and keep it maintainable. As a consequence the SleekDB class is now deprecated with version 2.0 and will be removed with version 3.0.
Data caching has been improved significantly.
git checkout -b feature/my-new-feature
git commit -am 'Added some feature'
git push origin feature/my-new-feature
dev
branchPlease write details about your PR.
We would love to see how you are using the database, if you have implemented something or how it is working for you.
What changes will make you more interested.
If you want to submit a bug feel free to create a new issue or email me @ rakibtg [-at-] gmail [-dot-] com
Let us know with a tweet if you love or hate SleekDB 😄, or if you have any question mail me or tweet to my twitter handle.