how we do search with join

[REDAL]

how we do search in data with join, where the fields we search are in both tables (stores )

[REDAL]

@rakibtg any thaugths

[rakibtg]

@REDAL i think the best solution would be to run that logic in the application layer

[REDAL]

@REDAL i think the best solution would be to run that logic in the application layer

can you give me an example ? please

[REDAL]

@rakibtg ?! any help here Hasan

[Timu57]

Hi @REDAL sorry for the late response.
You can see the order of execution here: https://sleekdb.github.io/#/execution-order
As you can see search comes after join.
That means you can do a join and search afterwards. For example:

$userStore = new Store("users", $dataDir);
$commentStore = new Store("comments", $dataDir);

$searchQuery = "SleekDB is the best database solution";

$users = $usersStore
  ->createQueryBuilder()
  ->join(function($user) use ($commentStore){
    // returns result
    return $commentStore->findBy([ "userId", "=", $user["_id"] ]);
  }, "comments")
  ->search(['comments.0.title', 'comments.0.content'], $searchQuery)
  ->orderBy(["searchScore" => "DESC"])
  ->getQuery()
  ->fetch();

The example above will first join users and comments. Afterwards it will search the first joined comment of every user.
Unfortunately we currently haven't implemented wildcards like comments.*.title. That's the reason why searching after a join probably will not do what you need.

[REDAL]

Its ok Hopefully in the near future with sleekdb 3 Thanks for your response Regards

…

________________________________ From: Timucin Ünal ***@***.***> Sent: Wednesday, May 17, 2023 5:35:52 PM To: rakibtg/SleekDB ***@***.***> Cc: redaloui ***@***.***>; Mention ***@***.***> Subject: Re: [rakibtg/SleekDB] how we do search with join (Issue #251) Hi @REDAL<https://github.com/REDAL> sorry for the late response. You can see the order of execution here: https://sleekdb.github.io/#/execution-order As you can see search comes after join. That means you can do a join and search afterwards. For example: $userStore = new Store("users", $dataDir); $commentStore = new Store("comments", $dataDir); $searchQuery = "SleekDB is the best database solution"; $users = $usersStore ->createQueryBuilder() ->join(function($user) use ($commentStore){ // returns result return $commentStore->findBy([ "userId", "=", $user["_id"] ]); }, "comments") ->search(['comments.0.title', 'comments.0.content'], $searchQuery) ->orderBy(["searchScore" => "DESC"]) ->getQuery() ->fetch(); The example above will first join users and comments. Afterwards it will search the first joined comment of every user. Unfortunately we currently haven't implemented wildcards like comments.*.title. That's the reason why searching after a join probably will not do what you need. — Reply to this email directly, view it on GitHub<#251 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ABZR4KD2GMBQEWOL4LXSCEDXGT46RANCNFSM6AAAAAATUFMERY>. You are receiving this because you were mentioned.Message ID: ***@***.***>