JOIN feature

[rakibtg]

I have started working on JOIN feature. This is how the query would look like:

  $articleStore = \SleekDB\SleekDB::store('article', './sleekdb_test_scripts');
  $userStore = \SleekDB\SleekDB::store('user', './sleekdb_test_scripts');

  $users = $userStore
      ->join(function ($user) use ($articleStore) {
          return $articleStore->where('author', '=', $user['_id']);
      }, 'articles')
      ->fetch();

Code lives in feature/join branch.

Let me know your thoughts.

[rakibtg]

Data output for above sample query:

Array
(
    [0] => Array
        (
            [name] => Foo
            [followers] => 10
            [_id] => 1
            [articles] => Array
                (
                    [0] => Array
                        (
                            [title] => Lorem ipsum
                            [author] => 1
                            [likes] => 0
                            [_id] => 1
                        )

                    [1] => Array
                        (
                            [title] => Flock of birds
                            [author] => 1
                            [likes] => 66
                            [_id] => 3
                        )

                    [2] => Array
                        (
                            [title] => Flying in above
                            [author] => 1
                            [likes] => 0
                            [_id] => 4
                        )

                )

        )

    [1] => Array
        (
            [name] => Bar
            [followers] => 0
            [_id] => 2
            [articles] => Array
                (
                    [0] => Array
                        (
                            [title] => Dollar sit amet
                            [author] => 2
                            [likes] => 12
                            [_id] => 2
                        )

                )

        )

    [2] => Array
        (
            [name] => Bif
            [followers] => 5
            [_id] => 3
            [articles] => Array
                (
                    [0] => Array
                        (
                            [title] => The sun rise
                            [author] => 3
                            [likes] => 2842
                            [_id] => 5
                        )

                )

        )

    [3] => Array
        (
            [name] => Baf
            [followers] => 12
            [_id] => 4
            [articles] => Array
                (
                )

        )

)

[Timu57]

That is an awesome idea!

Wouldn't it be nicer if we could tweak the join method, for a much easier use?

I am thinking about something like this:

public function join($storeToJoin, $condition, $resultPropertyName)

With an usage like this:

$articleStore = \SleekDB\SleekDB::store('article', './sleekdb_test_scripts');
$userStore = \SleekDB\SleekDB::store('user', './sleekdb_test_scripts');

$users = $userStore->join($articleStore, ['author', '=', '_id'], 'articles')->fetch();

Or add a new method called leftJoin with a more easy use.

[rakibtg]

@Timu57 Thanks for your input. I have thought about it at first, but then I realised that we could achieve more with closure JOIN function.

For example,

• Nested join
• Nested join pagination
• Sorting ... etc.

Code example:

<?php
$userStore
      ->join(function ($user) use ($articleStore) {
            return $articleStore
                  ->where('author', '=', $user['_id'])
                  ->join(function ($article) use ($userStore) {
                        return $userStore
                            ->in('_id', '=', $article['likedUsers'])
                            ->orderBy('desc', '_id' )
                            ->limit(10);
                  }, 'likedBy');
      }, 'articles')
      ->fetch();

Let me know your opinion.

[Timu57]

@rakibtg Your right, for more complex joins that is excellent and a very nice solution.

I think if the developer just needs simple joins, providing an alternative method can make the business code much cleaner and the usage much easier.

$userStore = \SleekDB\SleekDB::store('user', './sleekdb_test_scripts');
$articleStore = \SleekDB\SleekDB::store('article', './sleekdb_test_scripts');

$users = $userStore
            ->simpleJoin($articleStore, ['_id', '=', 'author'], 'articles')
            ->fetch();

If the developer has to join three stores:

$userStore = \SleekDB\SleekDB::store('user', './sleekdb_test_scripts');
$articleStore = \SleekDB\SleekDB::store('article', './sleekdb_test_scripts');
$commentStore = \SleekDB\SleekDB::store('comments', './sleekdb_test_scripts');

$users = $userStore
            ->simpleJoin($articleStore, ['_id', '=', 'author'], 'articles')
            ->simpleJoin($commentStore, ['_id', '=', 'user'], 'comments')
            ->fetch();

If the developer needs for example also the articles related to the comments:

$userStore = \SleekDB\SleekDB::store('user', './sleekdb_test_scripts');
$articleStore = \SleekDB\SleekDB::store('article', './sleekdb_test_scripts');
$commentStore = \SleekDB\SleekDB::store('comments', './sleekdb_test_scripts');

$users = $userStore
            ->simpleJoin($articleStore, ['_id', '=', 'author'], 'articles')
            ->simpleJoin([$commentStore, $articleStore], [['_id', '=', 'user'], ['comments.article', '=', '_id']], 'comments')
            ->fetch();

Or:

$userStore = \SleekDB\SleekDB::store('user', './sleekdb_test_scripts');
$articleStore = \SleekDB\SleekDB::store('article', './sleekdb_test_scripts');
$commentStore = \SleekDB\SleekDB::store('comments', './sleekdb_test_scripts');

$users = $userStore
            ->simpleJoin($articleStore, ['_id', '=', 'author'], 'articles')
            ->simpleJoin([$commentStore, $articleStore], [['_id', '=', 'user'], ['article', '=', '_id']], 'comments')
            ->fetch();

Where the second condition just consider the comments-result and joins it with the article store.

But I don't know what could be a nice name for the method.

[rakibtg]

Added in SleekDB 2.0!