I’m using Laravel and MongoDB, but I have a problem in my NoSQL query that filters using aggregation operation . The SQL query works perfectly, it returns the filtering I want, but the NoSQL query does not return anything, even with the right value defined for the filtering. It simply returns nothing. The variable dd($rewarded) return me an array empty.
SQL query filter (Work, just to demonstrate the structure)
$filter = $request->input('search');
$rewarded = RewardedModel::query()
->select('rewardedes.id', 'hunters.name','rewards.description','rewards.value','rewardedes.done')
->join('hunters', 'rewardedes.hunter_id', '=', 'hunters.id')
->join('rewards', 'rewardedes.reward_id', '=', 'rewards.id')
->where('hunters.name', 'LIKE', "%$filter%")
->orWhere('rewards.description', 'LIKE', "%$filter%")->paginate(5);
NoSQL query filter (Doesn't work, empty query returned)
$filter = $request->input('search');
$rewarded = RewardedModel::raw(function ($collection) use ($filter) {
return $collection->aggregate([
[
'$lookup' => [
'from' => 'hunters',
'localField' => 'hunter_id',
'foreignField' => '_id',
'as' => 'hunters'
]
],
[
'$lookup' => [
'from' => 'rewards',
'localField' => 'reward_id',
'foreignField' => '_id',
'as' => 'rewards'
]
],
[
'$match' => [
'$or' => [
['hunters.name' => ['$regex' => $filter, '$options' => 'i']],
['rewards.description' => ['$regex' => $filter, '$options' => 'i']]
]
]
],
[
'$project' => [
'_id' => 1,
'name' => '$hunters.name',
'description' => '$rewards.description',
'value' => '$rewards.value',
'done' => 1,
]
],
[
'$limit' => 5
]
]);
});
My model RewardedModel.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Jenssegers\Mongodb\Eloquent\Model;
use Jenssegers\Mongodb\Eloquent\SoftDeletes;
class RewardedModel extends Model
{
use HasFactory, SoftDeletes;
protected $connection = 'mongodb';
protected $collection = 'rewarded';
protected $primary_key = '_id';
protected $fillable = [
'hunter_id',
'reward_id',
'done',
];
public function hunter()
{
return $this->belongsTo(HunterModel::class, 'hunter_id');
}
public function reward()
{
return $this->belongsTo(RewardModel::class, 'reward_id');
}
}