SleekDB - A NoSQL Database made using PHP

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.

😎 Manage Static Data With Ease

SleekDB works great as the database engine for low to medium traffic websites. It makes simple database effortless.

📝 Your Dynamic Website Become Static

SleekDB will cache all query data by default, in a way that would make any dynamic site as fast as static sites.

🗄 Flat File Database

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.

🥳 You Are Not Alone!

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.

👋 Say Hi!

Join our Discord channel. We would love to know about you and your feedbacks.

Features

Installing SleekDB

• ⚡ Lightweight & Fast

  Stores data in plain-text utilizing JSON format, no binary conversion needed to store or fetch the data. Default query cache layer.

• 🔆 Schema free data storage

  SleekDB does not require any schema, so you can insert any types of data you want.

• 🔍 Query on nested properties

  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"
    }
  }
  }

• ✨ Dependency free, only needs PHP to run

  Supports PHP 7+. Requires no third-party plugins or software.

• 🚀 Default caching layer

  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.

• 🌈 Rich Conditions and Filters

  Use multiple conditional comparisons, text search, sorting on multiple properties and nested properties.

  where orWhere select except in not in

  join like sort skip orderBy update

  limit search distinct exists first delete

  like not like between not between group by having

• 👍 Process data on demand

  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 everywhere

  Runs perfectly on shared-servers or VPS too.

• 🍰 Easy to learn and implement

  SleekDB provides a very simple elegant API to handle all of your data.

• 🍰 Easily import/export or backup data.

  SleekDB use files to store information. That makes tasks like backup, import and export very easy.

• 💪 Actively maintained

  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.

• 💌 Support

  For support and other requirements contact.

Installation

To use SleekDB make sure that you have PHP up and running in your system, and SleekDB has write and/or read permission.

Requirements

• PHP >= 7.0
• ext-json
• ext-mbstring

Composer Installation (recommended)

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.

Install Manually (without composer)

• Download the latest version and extract the ZIP file inside your project directory.

• 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.

Using dev version (Not recommended)

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

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.

📌 Important Note

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.

Query Life Cycle

Each query will follow the below steps or execution life cycle.

1. Store

   The Store class is the first, and in most cases also the only part you need to come in contact with.

2. Query Builder

   The QueryBuilder class is used to prepare a query. To execute it getQuery method can be used.

3. Query

   At this step the Query class contains all information needed to execute the query.

4. Cache

   The Cache class handles everything regarding caching. It will decide when to cache or not to cache.

First Example - Insert and Fetch Data

1. 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";

2. 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.

3. 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.

4. 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.

Stores

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.

Your first store

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.

Deleting A Store

To delete a store use the deleteStore() method. It deletes a store and wipes all the data and cache it contains.

Example:

$userStore->deleteStore();

Configurations

SleekDB allows a few configuration options, which are

• auto_cache
• cache_lifetime
• timeout
  ( 🚨 Deprecated since version 2.12, set it to false and if needed use set_time_limit() in your own code! )
• primary_key
• search

They are store wide, which means they will be used on every query, if not changed on a query by query base.

Using Custom Configuration

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"]
  ]
];
$newsStore = new \SleekDB\Store("news", $dataDir, $configuration);

Let's get familiar with the available configuration options.

auto_cache

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()

cache_lifetime

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

timeout

🚨 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.

primary_key

Set the primary key into any string you want.
SleekDB will then use that key instead of the default _id key.

search

On this documentation page we will provide you a brief description regarding the configuration options.

Note: You will find more on searching at Searching

min_length

It has to be an integer > 0.
The minimum length of a word within the search query that will be used for searching.

mode

Has to be a string.
SleekDB provides two modes.

• "or"
• "and"

score_key

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.

algorithm

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

Insert Data

To insert data first you make a PHP array, and simply insert that array into a store.

Brief repetition of Store object creation

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");

Summary

• insert
• insertMany
• updateOrInsert
• updateOrInsertMany

Insert a single document

function insert(array $data): array

Parameters

1. $data: array

   One document that will be insert into the Store

   • ["name" => "Josh", "age" => 23, "city" => "london"]

Return value

Returns the inserted document as an array including the automatically generated and unique _id property.

Example

// 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);

Insert multiple documents

function insertMany(array $data): array

Parameters

1. $data: array

   Multiple documents that will be insert into the Store

   • [ ["name" => "Josh", "age" => 23], ["name" => "Mike", "age" => 19], ... ]

Return value

Returns the inserted documents in an array including their automatically generated and unique _id property.

Example

// 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);

updateOrInsert()

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

Parameters

1. $data: array

   Document to update or insert.

2. $autoGenerateIdOnInsert: bool

   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.

Return value

Returns updated / inserted document.

Example 1

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);

Example 2

Update or Insert a user with _id = 23.

$user = [
    "_id" => 23,
    "name" => "John",
    ...
];
$userStore->updateOrInsert($user, false);

Example 3

Update or Insert a user with no _id.

$user = [
    "name" => "John",
    ...
];
$userStore->updateOrInsert($user);

updateOrInsertMany()

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

Parameters

1. $data: array

   Documents to update or insert.

2. $autoGenerateIdOnInsert: bool

   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.

Return value

Returns updated / inserted documents.

Example 1

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);

Example 2

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!

Fetch Data

To get data from the store SleekDB provides some simple yet powerful methods.

ℹ️ If you need to make more complex queries look into QueryBuilder.

Brief repetition of Store object creation

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");

Summary

• findAll
• findById
• findBy
• findOneBy
• count

Get all documents

function findAll(array $orderBy = null, int $limit = null, int $offset = null): array

Parameters

1. $orderBy: array

   Sort the result by one or multiple fields.

   • ["name" => "asc"]
   • ["name" => "asc", "age" => "desc"]

2. $limit: int

   Limit the result to a specific amount.

3. $offset: offset

   Skip a specific amount of documents.

Return value

Returns either an array containing all documents of that store or an empty array if there are no documents.

Example

$allNews = $newsStore->findAll();

Example result

[["_id" => 12, "title" => "We love SleekDB"], ["_id" => 14, "title" => "NoSQL with just PHP"], ...]

Get a single document with its _id

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

Parameters

1. $id: int|string

   The _id of a document located in the store.

Return value

Returns one document or null if document could not be found.

Example

$news = $newsStore->findById(12);

Example result

["_id" => 12, "title" => "SleekDB is the Best", ...]

Get one or multiple documents

function findBy(array $criteria, array $orderBy = null, int $limit = null, int $offset = null): array

Parameters

1. $criteria: array

   One or multiple where conditions.
   Visit the $criteria documentation page to learn more on how to define conditions.

2. $orderBy: array

   Sort the result by one or multiple fields.

   • ["name" => "asc"]
   • ["name" => "asc", "age" => "desc"]

3. $limit: int

   Limit the result to a specific amount.

4. $offset: offset

   Skip a specific amount of documents.

Return value

Returns found documents in an array or an empty array if nothing is found.

Example 1

$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.

Result

[ ["_id" => 12, ... "title" => "Best Database"], ["_id" => 4, ... "title" => "Why SleekDB"], ...]

Example 2

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
   );

Get one document

function findOneBy(array $criteria): array|null

Parameters

1. $criteria: array

   One or multiple where conditions.
   Visit the $criteria documentation page to learn more on how to define conditions.

Return value

Returns one document or null if nothing is found.

Example 1

$news = $newsStore->findOneBy(["author", "=", "Mike"]);
// Returns one news article of the author called Mike

Result

["_id" => 18, "title" => "SleekDB is super fast", "author" => "Mike"]

Example 2

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%"]
         ]
      ]
   );

Get the amount of all documents stored

This method is especially fast because it just counts the amount of files.

function count(): int

Return value

Returns the amount of documents in the store as an int.

Example

$newsCount = $newsStore->count();
// Returns: 27 

Edit Data

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.

Brief repetition of Store object creation

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");

Summary

• updateById
• update
• removeFieldsById
• updateOrInsert
• updateOrInsertMany

updateById()

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

Parameters

1. $id: int|string

   Id of document to update.

2. $updatable: array

   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.

Return value

Returns updated document on success or false if document could not be found.

Example 1

Change the status of the user with _id = 23.

$userStore->updateById(23, [ "status" => "active" ]);

Example 2

Change the name and the age of the user with _id = 24.

$userStore->updateById(24, [ "name" => "Georg", "age" => 22 ]);

Example 3

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" ]);

Result

[
    "_id" => 24,
    "address" => [
        "street" => "first street",
        "postalCode" => "47129"
    ],
    ...
]

update()

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.

Parameters

1. $updatable: array

   One or multiple documents

   • ["_id" => 12, "title" => "SleekDB rocks!", ...]
   • [ ["_id" => 12, "title" => "SleekDB rocks!", ...], ["_id" => 13, "title" => "Multiple Updates", ...], ... ]

Return value

Returns true on success or false if document with given _id does not exist.

Example 1: Update one user that we got beforehand

$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

Example 2: Update multiple users

// 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 );

removeFieldsById()

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

Parameters

1. $id: int|string

   Id of document to remove fields of.

2. $fieldsToRemove: array

   Array containing fields to remove.
   Removal of nested fields possible by using a dot between fieldNames (Example 3)

Return value

Returns updated document on success or false if document could not be found.

Example 1

Remove the status field of the user with _id = 23.

$userStore->updateById(23, [ "status" ]);

Example 2

Remove the name and the age fields of the user with _id = 24.

$userStore->updateById(24, [ "name", "age" ]);

Example 3

Remove the street field of the user with _id = 24.
Note: The street is stored in a nested array.

$userStore->updateById(24, [ "address.street" ]);

Result

[
    "_id" => 24,
    "address" => [
        "postalCode" => "47129"
    ],
    ...
]

updateOrInsert()

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

Parameters

1. $data: array

   Document to update or insert.

2. $autoGenerateIdOnInsert: bool

   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.

Return value

Returns updated / inserted document.

Example 1

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);

Example 2

Update or Insert a user with _id = 23.

$user = [
    "_id" => 23,
    "name" => "John",
    ...
];
$userStore->updateOrInsert($user, false);

Example 3

Update or Insert a user with no _id.

$user = [
    "name" => "John",
    ...
];
$userStore->updateOrInsert($user);

updateOrInsertMany()

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

Parameters

1. $data: array

   Documents to update or insert.

2. $autoGenerateIdOnInsert: bool

   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.

Return value

Returns updated / inserted documents.

Example 1

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);

Example 2

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!

Delete Data

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.

Brief repetition of Store object creation

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");

Summary

• deleteBy
• deleteById

Delete one or multiple documents

function deleteBy(array $criteria, int $returnOption = Query::DELETE_RETURN_BOOL): array|bool|int

Parameters

1. $criteria: array

   One or multiple where conditions.
   Visit the $criteria documentation page to learn more on how to define conditions.

2. $returnOption: int

   Different return options provided with constants of the Query class

   • Query::DELETE_RETURN_BOOL (Default)
     Return true or false
   • Query::DELETE_RETURN_RESULTS
     Retrieve deleted files as an array
   • Query::DELETE_RETURN_COUNT
     Returns the amount of deleted documents

Return value

This method returns based on the given return option either boolean, int or array.

Example 1

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);

Example result

[ ["_id" => 12, "name" => "Joshua Edwards"], ["_id" => 14, "name" => "Joshua Edwards"], ... ]

Example 2

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]
      ]
    ]
  );

Delete one document with its _id

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

Parameters

1. $id: int|string

   The _id of a document located in the store.

Return value

Returns true if document does not exist or deletion was successful or false on failure.

Example

$userStore->deleteById(12);
// Returns true

$criteria

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:

• Store->findBy()
• Store->findOneBy()
• Store->deleteBy()
• QueryBuilder->where()
• QueryBuilder->orWhere()
• QueryBuilder->having()

Summary

• $criteria: array
• Examples

$criteria: array

One or multiple conditions.
The criteria can be nested as much as needed.

• [$fieldName, $condition, $value]
• [ [$fieldName, $condition, $value], [$fieldName, $condition, $value], ... ]
• [ [$fieldName, $condition, $value], OPERATION ,[$fieldName, $condition, $value], ... ]

With SleekDB version 2.10 we introduce closures to filter data with your own custom functions.

• [ CLOSURE ]
• [ CLOSURE, CLOSURE, ... ]
• [ CLOSURE, [$fieldName, $condition, $value], ... ]

• [ CLOSURE, OPERATION, [$fieldName, $condition, $value], ... ]

  • $fieldName: string

    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.

  • $condition: string

    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.
      Supported wildcards:
      • % Represents zero or more characters
        Example: bl% finds bl, black, blue, and blob
      • _ Represents a single character
        Example: h_t finds hot, hat, and hit
      • [] Represents any single character within the brackets
        Example: h[oa]t finds hot and hat, but not hit
      • ^ Represents any character not in the brackets
        Example: h[^oa]t finds hit, but not hot and hat
      • - Represents a range of characters
        Example: c[a-b]t finds cat and cbt
    • IN $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.
      Check if data is within first and second value.
      Begin and end values are included.
    • NOT BETWEEN $value has to be an array with a length of 2.
      Check if data is not within first and second value.
      Begin and end values are included.

  • $value

    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.

  • OPERATION: string

    It is used to connect multiple conditions.
    The operation is optional and can be set to AND or OR.
    Default: AND

  • CLOSURE: Closure

    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.

Examples

Example 1

Find all news articles of the author "John".

$news = $newsStore->findBy(["author", "=", "John"]);

Example 2

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%"]
]);

Example 3

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%"]
]);

Example 4

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%"]
]);

QueryBuilder

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();

Summary

• getQuery
• where
• orWhere
• select
• except
• skip
• limit
• orderBy
• groupBy
• having
• search
• distinct
• Cache management
• join
• nestedWhere ( 🚨 Deprecated since version 2.3, use where and orWhere instead! )
• in ( 🚨 Deprecated since version 2.4, use where and orWhere instead! )
• notIn ( 🚨 Deprecated since version 2.4, use where and orWhere instead! )

getQuery()

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:

• fetch()

  Retrieve multiple documents

• first()

  Retrieve first found document

• exists()

  Check if a document for given query exist

• delete()

  Delete all documents that are found with given query

• update()

  Update all documents that are found with given query

For more details on query execution please visit the Query page.

where()

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

Parameters

1. $criteria

   One or multiple where conditions.
   Visit the $criteria documentation page to learn more on how to define conditions.

Example 1

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();

Example 2

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();

Example 3

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()

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

Properties

1. $criteria

   One or multiple where conditions.
   Visit the $criteria documentation page to learn more on how to define conditions.

Example 1

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();

Example 2

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();

Example 3

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();

select()

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

Parameters

1. $fieldNames: array

   Specify specific fields to output.

   • ["fieldName1", "fieldName2", ...]
   • ["ALIAS" => "fieldName1", "fieldName2", ...]
   • ["ALIAS" => ["FUNCTION" => PARAMETER], ...]
   • ["ALIAS" => CLOSURE, ...]

Select-Functions

When using select functions you always need to define an alias!

String functions

String functions just apply to strings and return null on all other types.

1. UPPER

   Make a field uppercase.
   Usage:

   ["ALIAS" => ["UPPER" => "fieldName"], ...]

2. LOWER

   Make a field lowercase.
   Usage:

   ["ALIAS" => ["LOWER" => "fieldName"], ...]

3. LENGTH

   Get the length of a field.
   Can also be used to get the length of an array!
   Usage:

   ["ALIAS" => ["LENGTH" => "fieldName"], ...]

4. CONCAT

   Concatenate multiple fields with a glue between each.
   Usage:

   ["ALIAS" => ["CONCAT" => [GLUE, "fieldName1", "fieldName2", ...]], ...]

   • GLUE: string
     The glue between the concatenated fields.

5. POSITION

   Get the position of a sub string.
   Usage:

   ["ALIAS" => ["POSITION" => [SUBSTRING, "fieldName"]], ...]

   • SUBSTRING: string
     The sub string to get the position of.

Numeric functions

Numeric functions just apply to numeric fields and return null on all other types.

1. SUM

   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"], ...]

2. MAX

   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"], ...]

3. MIN

   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"], ...]

4. AVG

   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"], ...]

5. ROUND

   Round the value of a field.
   Usage:

   ["ALIAS" => ["ROUND" => ["fieldName", PRECISION]], ...]

   • PRECISION: int
     The optional number of decimal digits to round to.
     If the precision is positive, val is rounded to precision significant digits after the decimal point.
     If the precision is negative, val is rounded to precision significant digits before the decimal point, e.g. for a precision of -1 val is rounded to tens, for a precision of -2 to hundreds, etc.

6. ABS

   Get the absolute value of a field.
   Usage:

   ["ALIAS" => ["ABS" => "fieldName"], ...]

Custom select function

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, ...]

Example 1

Retrieve just the name of all users.

$users = $userQueryBuilder
  ->select(['name'])
  ->getQuery()
  ->fetch();
// output: [["_id" => 1, "name" => "Max"], ["_id" => 2, "name" => "Hasan"]]

Example 2

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]]

Example 3

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();

Result

[
  ["followerSum" => 81482, "followerAvg" => 501.319205]
]

Example 4

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();

Result

[
  ["age" => 18, "followerSum" => 901],
  ["age" => 20, "followerSum" => 435],
  ...
]

Example 5

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();

Result

[
  ["age" => 18, "fullName" => "Tom, Great", "follower" => 901],
  ["age" => 20, "fullName" => "John, West", "follower" => 435],
  ...
]

except()

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

1. $fieldNames: array

   Specify specific fields to exclude from the output.

Example 1

Retrieve all information of an user except its _id and name.

$users = $userQueryBuilder
  ->except(["_id", "name"])
  ->getQuery()
  ->fetch();
// output: [["age" => 28], ["age" => 18]]

Example 2

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();

Result

[ ["_id" => 2, "address" => [ "street" => "Firststreet" ]], ... ]

skip()

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

Parameters

1. $skip: int|string

   The value of how many documents should be skipped.

Example

Retrieve all users except the first 10 found.

$users = $userQueryBuilder
  ->skip(10)
  ->getQuery()
  ->fetch();

$users = $userQueryBuilder
  ->skip('10')
  ->getQuery()
  ->fetch();

limit()

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

Parameters

1. $limit: int|string

   Limit the amount of documents in the result set. Has to be greater than 0.

Example

Retrieve just the first ten users.

$users = $userQueryBuilder
  ->limit(10)
  ->getQuery()
  ->fetch();

$users = $userQueryBuilder
  ->limit('10')
  ->getQuery()
  ->fetch();

orderBy()

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

Parameters

1. $criteria: array

• [ $fieldName => $order ]
• [ $fieldName => $order, $fieldName => $order, ... ]

  • $fieldName: string

    Name of the field that will be used to sort the result.

  • $order: string

    Either desc for a descending sort or asc for a ascending sort.

Example 1

Retrieve all users sorted by their name.

$users = $userQueryBuilder
  ->orderBy(["name" => "asc"])
  ->getQuery()
  ->fetch();

Result

[["_id" => 13, "name" => "Anton"], ["_id" => 2, "name" => "Berta"], ...]

Example 2

Retrieve all users sorted by their name and age.

$users = $userQueryBuilder
  ->orderBy(["name" => "asc", "age" => "asc"])
  ->getQuery()
  ->fetch();

Result

[
  ["_id" => 13, "name" => "Anton", "age" => 20],
  ["_id" => 4, "name" => "Aragon", "age" => 16], 
  ["_id" => 2, "name" => "Aragon", "age" => 17], 
  ...
]

groupBy()

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.

Parameters

1. $groupByFields: array

   One or multiple fields to group documents by.

2. $counterKeyName: string

   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!

3. $allowEmpty: bool

   If true documents having an empty value like null, in the fields that are relevant for grouping, will be grouped together.

Example 1

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();

Result

[ 
  ["category" => "pants", "productAmount" => 200], 
  ["category" => "shirts", "productAmount" => 29], 
  ... 
]

Example 2

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();

Result

[ 
  ["Category" => "pants", "subcategory" => "shorts", "productAmount" => 20, "views" => 212], 
  ["Category" => "pants", "subcategory" => "jeans", "productAmount" => 83, "views" => 331], 
  ... 
]

having()

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

Parameters

1. $criteria: array

   One or multiple where conditions.
   Visit the $criteria documentation page to learn more on how to define conditions.

Example 1

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();

Result

[ 
  ["category" => "pants", "totalViews" => 502], 
  ["category" => "shirts", "totalViews" => 931], 
  ... 
]

Example 2

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();

Result

[ 
  ["name" => "Best Schoe", "roundedPrice" => 519], 
  ["name" => "Black Jeans", "roundedPrice" => 549], 
  ... 
]

search()

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!

Parameters

1. $fields: array|string

   An array containg all fields to search against.

   • "title"
   • ["title"]
   • ["title", "content", ...]

   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

2. $query: string

   The search query.

3. $options: array

   Configure the search behaviour on a query by query base.

distinct()

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

Parameters

1. $fields: array|string

   Specify one or multiple fields you want to be distinct.

Example

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();

Cache management

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.

join()

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

nestedWhere()

🚨 Deprecated since version 2.3, use where and orWhere instead!

With the nestedWhere(...) method you are able to make complex nested where statements.

function nestedWhere(array $conditions): QueryBuilder

Conditions array structure

[
  "OUTERMOST_OPERATION" => [
    // conditions
  ]
];

Properties

1. $conditions

   Multiple where conditions.
   The criteria can be nested as much as needed.

• Small Example:
  [ "OUTERMOST_OPERATION" => [ [$fieldName, $condition, $value], "OPERATION", [$fieldName, $condition, $value] ] ]

  • $fieldName: string

    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.

  • $condition: string

    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.
      Supported wildcards:
      • % Represents zero or more characters
        Example: bl% finds bl, black, blue, and blob
      • _ Represents a single character
        Example: h_t finds hot, hat, and hit
      • [] Represents any single character within the brackets
        Example: h[oa]t finds hot and hat, but not hit
      • ^ Represents any character not in the brackets
        Example: h[^oa]t finds hit, but not hot and hat
      • - Represents a range of characters
        Example: c[a-b]t finds cat and cbt
    • IN $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.
      Check if data is within first and second value.
      Begin and end values are included.
    • NOT BETWEEN $value has to be an array with a length of 2.
      Check if data is not within first and second value.
      Begin and end values are included.

  • $value

    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.

  • OUTERMOST_OPERATION: string

    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.

  • OPERATION: string

    Can be set to AND or OR.
    The operation is used to connect multiple conditions.

Example 1

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();

Example 2

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();

in()

🚨 Deprecated since version 2.3, use where and orWhere 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

Parameters

1. $fieldName: string

   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 ".".

2. $values: array

   This argument takes an array to match documents within its items.

Examples

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();

notIn()

🚨 Deprecated since version 2.3, use where and orWhere 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

Parameters

1. $fieldName: string

   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 ".".

2. $values: array

   This argument takes an array to match documents within its items.

Examples

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();

Cache Management

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();

Summary

• useCache
• disableCache
• regenerateCache

useCache()

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

Parameters

1. $lifetime: int|null

   The lifetime of the cache.

• null (Default)
  Cache will not have a lifetime and will be regenerated on every update, delete and insert.
• int
  Cache lifetime in seconds. 0 means infinite lifetime.

Example 1

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();

Example 2

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();

Example 3

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();

disableCache()

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

Example

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();

regenerateCache()

Regenerate the cache of a query regardless of its lifetime.

function regenerateCache(): QueryBuilder

Example

// 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();

Join Stores

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!

Quick Example

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();

Result

[
  [
    "_id" => 1, 
    "name" => "John", 
    "comments" => [
      [
        "_id" => 1,
        "articleId" => 3
        "content" => "I love SleekDB"
      ],
      ...
    ]
  ],
  ...
]

join()

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

Parameters

1. $joinFunction: Closure

   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.

2. $propertyName: string

   Name of the new property added to each document.

Example 1

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();

Example 2

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();

Example 3

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();

Query

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();

Summary

• fetch
• first
• exists
• update
• delete
• removeFields

fetch()

Execute a query and retrieve an array containing all documents found.

function fetch(): array

Return value

An array containing all documents found or an empty array.

Example

Retrieve all users that are located in london.

$user = $userQueryBuilder
  ->where([ "city", "=", "london" ])
  ->getQuery()
  ->fetch();

Result

[
    [
        "_id" => 1,
        "name" => "John",
        "city" => "london"
    ],
    [
        "_id" => 4,
        "name" => "Max",
        "city" => "london"
    ],
    ...
]

first()

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

Return value

Returns the very first document discovered or an empty array if nothing found.

Example

$user = $userQueryBuilder
  ->where([ "email", "=", "foo@bar.com" ])
  ->getQuery()
  ->first();

Result

[
    "_id" => 1,
    "name" => "John",
    "email" => "foo@bar.com"
]

exists()

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

Return value

Returns true if a document exists and false if no document found.

Example

$userNameExists = $userQueryBuilder
  ->where([ "username", "=", "foobar" ])
  ->getQuery()
  ->exists();

update()

Update one or multiple documents based on a given query.

function update(array $updatable, bool $returnUpdatedDocuments = false): array|bool

Parameters

1. $updatable: array

   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.

Return value

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.

Example 1

Set the status of all users that are located in london to VIP.

$userStore
  ->createQueryBuilder()
  ->where([ "city", "=", "london" ])
  ->getQuery()
  ->update([ "status" => "VIP" ]);

Example 2

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" ]);

Result

[
  [
    "city" => "london".
    "address" => [
      "street" => "first street",
      "postalCode" => "41824"
    ],
    ...
  ],
  ...
]

delete()

Delete one or multiple documents based on a given query.

function delete(int $returnOption = Query::DELETE_RETURN_BOOL)

Parameters

1. $returnOption: int

   Different return options provided with constants of the Query class

   • Query::DELETE_RETURN_BOOL (Default)
     Return true or false
   • Query::DELETE_RETURN_RESULTS
     Retrieve deleted files as an array
   • Query::DELETE_RETURN_COUNT
     Returns the amount of deleted documents

Return value

This method returns based on the given return option either boolean, int or array.

Example 1

Delete all users that are not active.

$result = $userStore
  ->createQueryBuilder()
  ->where([ "active", "=", false ])
  ->getQuery()
  ->delete();
// output: true

Example 2

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

Example 3

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"], ... ]

removeFields()

Remove fields of one or multiple documents based on current query.

function removeFields(array $fieldsToRemove): array|false

Parameters

1. $fieldsToRemove: array

   An array containing the fields to remove.
   Update of nested values possible by using a dot between fieldNames (Example 2)

Return value

Returns updated documents on success and false on failure.

Example 1

Remove the status field of all users that are located in london.

$userStore
  ->createQueryBuilder()
  ->where([ "city", "=", "london" ])
  ->getQuery()
  ->removeFields([ "status" ]);

Example 2

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" ]);

Result

[
  [
    "city" => "london".
    "address" => [
      "postalCode" => "41824"
    ],
    ...
  ],
  ...
]

Working with Dates

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:

• Store->findBy()
• Store->findOneBy()
• Store->deleteBy()
• QueryBuilder->where()
• QueryBuilder->orWhere()
• QueryBuilder->having()

The conditions you can use DateTime objects with are:

• =
• ===
• == (Type unsafe comparison)
• !=
• !==
• <> (Type unsafe comparison)
• >
• >=
• <
• <=
• IN
• NOT IN
• BETWEEN
• NOT BETWEEN
• CONTAINS
• NOT CONTAINS

Quick example

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 ] ] );

How does SleekDB use DateTime objects to filter data

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.

• 0
• 0.0
• "0"
• ""
• NULL
• FALSE
• array()

Best practice to store dates in the database

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.

Example 1

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);

Example 2

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);

Example 3

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);

Not using DateTime

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")

Searching

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!

Summary

• Configuration options
• Mode: "or" (default)
• Mode: "and"
• Algorithm: "hits" (default)
• Algorithm: "hits_prioritze"
• Algorithm: "prioritize"
• Algorithm: "prioritize_position"
• search() method of QueryBuilder class
• search() method of Store class

Cofiguration options

You 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();

"min_length" and "minLength"

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.

"mode"

Has to be a string.
SleekDB provides two modes.

• "or"
• "and"

"score_key" and "scoreKey"

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.

algorithm

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

Mode: "or" (default)

With this mode a field has to contain one of the words in the search query to be considered as a search result. For example our query is "Where School" and we have three values to check agains:

• "Where do you think is the best school?"
  • Two search hits
• "School is cool, but where is the best one?"
  • Two search hits
• "Why everybody love School"
  • One search hit

Mode: "and"

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:

• "School is cool, but where is the best one?"
  • Search hit
• "Is your school the best?"
  • No Search hit

Algorithm: "hits" (default)

Query::SEARCH_ALGORITHM["hits"]

This is the default algorithm.
The score is based on the amount of search hits.

Algorithm: "hits_prioritize"

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.

Algorithm: "prioritize"

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.

Algorithm: "prioritize_position"

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.

search() method of QueryBuilder class

Do a fulltext like search against one or multiple fields.

function search(array|string $fields, string $query, array $options = []): QueryBuilder

Parameters

1. $fields: array|string

   An array containg all fields to search against.

   • "title"
   • ["title"]
   • ["title", "content", ...]

   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

2. $query: string

   The search query.

3. $options: array

   Configure the search behaviour on a query by query base.

Example 1

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();

Example 2

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();

search() method of Store class

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

Parameters

1. $fields: array

   An array containg all fields to search against.

   • ["title"]
   • ["title", "content", ...]

   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

2. $query: string

   The search query.

3. $orderBy: array

   Sort the result by one or multiple fields.

   • ["name" => "asc"]
   • ["name" => "asc", "age" => "desc"]

4. $limit: int

   Limit the result to a specific amount.

5. $offset: offset

   Skip a specific amount of documents.

Return value

Will return an array containg all documents having at least one search hit or an empty array.

Example 1

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"]);

Example 2

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
);

Order of Execution

Summary

• General Life Cycle
• Order of query execution
  1. WHERE
  2. DISTINCT
  3. JOIN
  4. SEARCH
  5. SELECT - Step 1
  6. GROUP BY
  7. SELECT - Step 2
  8. HAVING
  9. EXCEPT (Remove fields)
  10. ORDER BY
  11. SKIP
  12. LIMIT

General Life Cycle

Each query will follow the below steps or execution life cycle.

1. Store

   The Store class is the first, and in most cases also the only part you need to come in contact with.

2. Query Builder

   The QueryBuilder class is used to prepare a query. It can be retrieved with Store->createQueryBuilder().

3. Query

   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.

4. Cache

   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().

Order of query execution

The following diagram represents the internal order of execution when using the Query->fetch() method.

• Query->fetch()

Single document

1. WHERE

   The where and orWhere conditions are used to determine if a document will be a part of the result set or not.

   • QueryBuilder->where()
   • QueryBuilder->orWhere()

2. DISTINCT

   If the WHERE resulted in true the document is checked against the result set using the fields defined with the distict() method.

   • QueryBuilder->distinct()

All documents in result set

Now we have all documents in a result array.

3. JOIN

   All documents will be joined with one or multiple stores.

   • QueryBuilder->join()

4. SEARCH

   We go through all documents and check if they contain what we search, if not we remove them from the result array.

   • QueryBuilder->search()

5. SELECT - Step 1

   Apply select functions that do not reduce the result amount to one and apply their aliases.

   • QueryBuilder->select()

6. GROUP BY

   Group documents by fields defined with groupBy().

   • QueryBuilder->groupBy()

7. SELECT - Step 2

   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.

   • QueryBuilder->select()

   Functions that reduce the amount of the result set to one:

   • SUM
   • MAX
   • MIN
   • AVG

8. HAVING

   Check all documents using the criteria specified with having.

   • QueryBuilder->having()

9. EXCEPT (Remove fields)

   Exclude/ Remove fields from all documents.

   • QueryBuilder->except()

10. ORDER BY

    Sort the result array using one or multiple fields.

    • QueryBuilder->orderBy()

11. SKIP

    Skip a specific amount of documents. (Slice the result array)

    • QueryBuilder->skip()

12. LIMIT

    Limit the amount of documents in result. (Slice the result array)

    • QueryBuilder->limit()

Advanced

Changing Store destination

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

Parameters

1. $storeName: string

   Name of new store destination

2. $dataDir: string

   If null previous database directory destination will be used.

3. $configuration

   If empty previous configurations remain.

Example

/* 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."]);

Keeping QueryBuilder object

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.

Example

$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();

Keeping Query object

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:

Example

$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();

Some complete code examples

We assume you read the documentation so you can understand the following small code examples.

Summary

• Insert one document

• Insert multiple documents

• Retrieve document/s using just the Store class

• Retrieve document/s using the QueryBuilder class

• Editing/ Updating documents using the QueryBuilder class

• Grouping documents

• Searching documents using just the Store object

• Searching documents using the QueryBuilder object

Insert one document

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);

Insert multiple documents

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);

Retrieving documents using just the Store object

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);

Retrieving documents using the QueryBuilder object

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);

Editing/ Updating documents using the QueryBuilder object

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);

Grouping documents

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);

Searching documents using just the Store object

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);

Searching documents using the QueryBuilder object

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);

Cache

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();

Summary

• getToken
• delete
• deleteAll
• deleteAllWithNoLifetime
• set
• get
• getCachePath
• setLifetime
• getLifetime

getToken()

Returns the unique token for the current query, that will be used to save and retrieve a cache file.

function getToken(): string

Return value

The unique token for the current query as a string.

delete()

Deletes the cache file for the current query.

function delete()

deleteAll()

Delete all cache files of current store.

function deleteAll()

deleteAllWithNoLifetime()

Delete all cache files that have no lifetime (null) of current store.

function deleteAllWithNoLifetime()

set()

Set and cache the content for the current query / token.

function set(array $content)

Parameters

1. $content: array

   The content that will be cached.

get()

Retrieve the content of the cache file for the current query / token.

function get(): array|null

Return value

The content as an array or null if no cache file found.

getCachePath()

Returns the path to the cache directory.

function getCachePath(): string

setLifetime()

Set the lifetime for the current query / token.

function setLifetime(int|null $lifetime): Cache

Parameters

1. $lifetime: int|null

   • int in seconds. 0 means infinite
   • null no cache lifetime
   • Cache gets deleted on every update / delete / insert.

getLifetime()

Get the lifetime for the current query / token.

function getLifetime(): int|null

Return value

Either int >= 0 in seconds, where 0 means infinite, or null.

Reference

This page contains a brief overview of all the classes and their methods. For more detailed information please visit the other documentation pages.

Store

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

QueryBuilder

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

Query

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

Cache

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()

🎉 Release Notes

📢 Optimizations, new query methods and more control

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.

Summary

• Changes from 2.11 to 2.12
• Changes from 2.10 to 2.11
• Changes from 2.9 to 2.10
• Changes from 2.8 to 2.9
• Changes from 2.7 to 2.8
• Changes from 2.6 to 2.7
• Changes from 2.5 to 2.6
• Changes from 2.4 to 2.5
• Changes from 2.3 to 2.4
• Changes from 2.2 to 2.3
• Changes from 2.1 to 2.2
• Changes from 2.0 to 2.1
• Changes from 1.5 to 2.0

Changes from 2.11 to 2.12

• 🚨 Deprecated timeout configuration

  Set it to false and if needed use set_time_limit() in your own code!

Changes from 2.10 to 2.11

• ✨ New configuration option

  • Set "timeout" to false if you can't use or don't want to use set_time_limit() function.

    • timeout

Changes from 2.9 to 2.10

• ✨ New functionality!

  • Create and use your own select functions

    • QueryBuilder->select()

  • Filter documents with your own custom functions

    • $criteria

Changes from 2.8 to 2.9

• ✨ New functionality!

  • New select functions like CONCAT, ROUND and much more!

    • QueryBuilder->select()

  • New CONTAINS and NOT CONTAINS conditions available!

    • $criteria

  • New Store->updateOrInsert() and Store->updateOrInsertMany() methods!

    • Documentation of updateOrInsert() method
    • Documentation of updateOrInsertMany() method

• 📝 New documentation page!

  • Order of execution description and diagram to better understand the internals!

    • "Order of Execution"

• 🌈 Improved functionality!

  • findAll() now accept orderBy, limit and offset as optional parameters!

    • Documentation of findAll() method

  • limit() and skip() now accept strings!

    • Documentation of limit() method
    • Documentation of skip() method

  • Having can now be used without Group-By!

    • QueryBuilder->having()

Changes from 2.7 to 2.8

• ✨ New functionality!

  • Retrieve the amount of documents stored in a fast way.

    • Store->count()

Changes from 2.6 to 2.7

• 🚨 New requirement!

  • In order to implement the new search functionality we had to add a new PHP module requirement to SleekDB.

    • ext-mbstring

• 🚨 Deprecated method!

  • The getDataDirectory() method of the Store class is now deprecated. Use getDatabasePath() instead!

• 📝 New documentation page!

  • This new documentation page contains some complete code examples!

    • "Some complete code examples"

• ✨ New functionality!

  • SleekDB now has multiple enhanced searching algorithms with scored results!

    • New "Searching" documentation

  • New store wide search configurations!

    • Configure the search behavion when creating a Store object.

  • New shorthand search() method for Store class!

    • Documentation of new shorthand search() method!

  • New comparisons!

    • Type safe

      • ===
      • !==

    • Type unsafe

      • ==
      • <>

  • Now you can easily remove fields from a document!

    • removeFieldsById()
    • removeFields()

• 🌈 Improved functionality!

  • Configuration on a query by query base with the search() method of the QueryBuilder class

    • Documentation of search() method

  • All *byId methods now also accept strings as id!

    For more details please visit the corresponding documentation pages:

    • findById()
    • updateById()
    • deleteById()
    • removeFieldsById()

Changes from 2.5 to 2.6

• ✨ New functionality

  • SleekDB now can group results and filter grouped results!

    • groupBy()
    • having()

  • Easily update parts of a document with its id!

    • updateById()

  • Use one Store object to manage multiple stores!

    • changeStore()

• 🌈 Improved functionality

  • select()

    For more details please visit the documentation of select.

    • The select method now accepts aliase!
    • When using select in conjunction with groupBy you can use functions!
    • You can now select nested fields!

  • except()

    For more details please visit the documentation of except.

    • You can now exclude nested fields!

  • update() of Query class

    For more details please visit the documentation of update.

    • You can now update nested fields!
    • Can now return the updated results!

Changes from 2.4 to 2.5

• ✨ New conditions:

  The following conditions can now be used with the findBy(), findOneBy(), deleteBy(), where() and orWhere() methods!

  • BETWEEN
  • NOT BETWEEN

• 🌈 SleekDB now accepts DateTime objects to filter data!

  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.

Changes from 2.3 to 2.4

• 🚨 Deprecated in() & notIn() methods

  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.

Changes from 2.2 to 2.3

• 🚨 Deprecated nestedWhere() method

  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.

• 🌟 Nested where statements everywhere!

  The findBy(), findOneBy, deleteBy(), where() and orWhere() methods now can handle nested statements!

  findBy() findOneBy()	Fetch Data documentation
  deleteBy()	Delete Data documentation
  where() orWhere()	QueryBuilder documentation

• ✨ New conditions

  • not like
  • in
  • not in

  These new conditions can now be used with the findBy(), findOneBy(), deleteBy(), where() and orWhere() methods!

• 💡 Logical connection

  All condition methods are now logically connected and the order when using them is now important.
  See #114 for more details.

Changes from 2.1 to 2.2

• 🗃 Order by multiple fields

  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.

• 🔍 New nestedWhere() method

  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.

Changes from 2.0 to 2.1

• ⚙️ New Configuration

  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.

Changes from 1.5 to 2.0

• 🔎 Improving document discovery process

  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.

• Support for PHP >= 7.0

  The support of PHP 5 is dropped to provide more modern features.

• ✨ New query methods

  • first()
  • exists()
  • select()
  • except()
  • distinct()
  • join()
  • findAll()
  • findById()
  • findBy()
  • findOneBy()
  • updateBy()
  • deleteBy()
  • deleteById()

• Improved Code Quality

  We isolated most of the existing logics into different classes to make the codebase easy to understand and easy to maintain.

• Unit Testing

  There was some concern among developers as it was lacking UNIT Testing, now its added!

• SleekDB class now deprecated

  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.

  A new era for SleekDB begins!

• Better Caching Solution

  Data caching has been improved significantly.

  • Add custom expiry of a cache file with lifetime.
  • Ability of query specific caching rules.

Issues

• Deprecate SleekDB Class (#84)
• first and exists methods does not use cache (#82)
• Make where and orWhere just accept one array (#80)
• Make dataDir required (#79)
• Add update & delete to Store class #78)
• Change delete method of Query class to accept return options (#77)
• Add find methods to new Store class to make the QueryBuilder optional for simple queries. (#75)
• Allow to query on read-only file system (#67)
• Use "results" property instead of returning data from methods (#59)
• Return the first item only (#56)
• Check if data exists (#54)
• update return value (#51)
• delete status (#48)
• Extend not possible due to private helper methods (#44)
• JOIN feature (#42)
• Return distincted value (#41)
• Suppress Key on fetch (#38)
• Like Condition (#34)
• Better code base (#32)
• OR Condition Query (#31)
• Possibility of duplicate ID’s (#30)

Contributing

• Fork SleekDB
• Create your feature branch git checkout -b feature/my-new-feature
• Commit your changes git commit -am 'Added some feature'
• Push to the branch git push origin feature/my-new-feature
• Create new Pull Request targeting dev branch

Please write details about your PR.

Contact

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

About

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.

Versions

• 2.15 (Actively Maintained Version)
• 2.14
• 2.13
• 2.12
• 2.11
• 2.10
• 2.9
• 2.8
• 2.7
• 2.6
• 2.5
• 2.4
• 2.3
• 2.2
• 2.1
• 2.0
• 1.5 (Bug Fixes Only)