Select with alias at join

[armanist]

In the documentation (QueryBuilder section) there is an example how to use select() method and filter the columns.

There is no much insights about it, but assuming the userBiography is a table that was joined with users table?

When I'm trying to use dot notation like userBiography.age in the result set it's always null

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

[armanist]

I can give more insights if required, I just need to know it's not supported or I'm doing something wrong?

[Timu57]

Hi @armanist thank you for your question.
Sorry for the late response.

If you want to use select after a join it should be possible.
You can read more about that at https://sleekdb.github.io/#/execution-order

Could you please provide an example result without the select? Perhaps I can then help you with what is going wrong 😊

[Timu57]

@armanist I think I know what the problem is:

$users = $userQueryBuilder
 ->join(function($user) use ($userBuiographyStore) {
    return $userBiographyStore->findBy(["user", "=", $user["_id"]]);
  }, "userBiographies")
  ->getQuery()
  ->fetch();
// $users should be something like this:
// {'_id' => 2, 'name' => 'Tom',  'userBiographies' => [{'age' => 16, ...}, {'age' => 18, ...}, ...], ... }

As you can see userBiographies is an array of documents.

Now you can try the following to select:

$users = $userQueryBuilder
  ->select(['age' => 'userBiographies.0.age'])
  ->join(function($user) use ($userBuiographyStore) {
    return $userBiographyStore->findBy(["user", "=", $user["_id"]]);
  }, "userBiographies")
  ->getQuery()
  ->fetch();
// or with a custom select function if the above does not work
$users = $userQueryBuilder
  ->select([
    'age' => function($user) {
      return $user['userBiography'][0]['age'];
    }
  ])
  ->join(function($user) use ($userBuiographyStore) {
    return $userBiographyStore->findBy(["user", "=", $user["_id"]]);
  }, "userBiographies")
  ->getQuery()
  ->fetch();

We are aware of the problem of handling documents within arrays. We plan to implement a wildcard like userBiographies.*.age or a built-in select function to get the first result, with the next major update. 😊

Instead of saving the userBiography in another "table" (store) and handle the situation the "relational"-way, you should think about saving the userBiography directly in the user document (the document/nosql-based-approach).

{
  "_id": 2,
  "name": "Tom",
  "userBiography": {
    "age": 18,
    "work": "some work"
  },
  "address": {
    "street": "some street",
    "postalCode": "0129479",
  }
}

Hope I could help 😁

[armanist]

Hi @Timu57, thank you very much for great details, I will try your suggested way above ->select(['age' => 'userBiographies.0.age']) and will let you know here, the wildcard implementation will be great addition though ( userBiographies.* ). Meantime you can have look at SleekDB integration to the framework we are developing https://github.com/softberg/quantum-php-core/tree/master/src/Libraries/Database/Sleekdb.

[Timu57]

I'm closing this issue for now. Feel free to reopen it if needed.