withCount and loadCount methods not working on hybrid hasMany relationships

[m-ostadi]

• Laravel-mongodb Version: #.#.#
• PHP Version: 8.1.6
• Database Driver & Version: MongoDB 6.0.2 Community

Description:

It seems it's trying to get count from mysql instead of mongodb.

Steps to reproduce

I have User and Post class as below:

use Illuminate\Database\Eloquent\Model;
use Jenssegers\Mongodb\Eloquent\HybridRelations;

class User extends Model
{
    use HybridRelations;

    public function posts()
    {
        return $this->hasMany('App\Models\Post');
    }
}

use Jenssegers\Mongodb\Eloquent\Model;

class Post extends Model
{

    protected $collection = 'posts';

    protected $connection = 'mongodb';

    public function user(){
        return $this->belongsTo('App\Models\User');
    }
}

now when I try to get count with this :

$user->posts()->count()

it's work
but if I try this :

User::withCount('posts')->first()
//or
$user->loadCount('posts');

it fails and gives this error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"testdb"."posts" where "user_id" exists ?) as `posts_count` f' at line 1 (SQL: select `users`.*, (select "user_id" from "testdb"."posts" where "user_id" exists 1) as `posts_count` from `users` where `users`.`id` = 462372 limit 1) 

[GromNaN]

Issue confirmed. Tracked in PHPORM-69

[hans-thomas]

@GromNaN I have been working on this one lately. I noticed there is no way to add an aggregation pipeline to the query instance.
I created this aggregation that can solve the problem.

[
    [
        '$lookup' => [
            'from' => 'items',
            'localField' => '_id', 
            'foreignField' => 'user_id',
            'pipeline' => [['$project' => ['_id' => 1]]], 
            'as' => 'items_count'
        ]
    ], ['$addFields' => ['items_count' => ['$size' => '$items_count']]]
]

I can run this using MongoDB/Collection class, but it's not what we want in this case. There is a problem in the select methods that leads us to an error.

[GromNaN]

That need the aggregation builder that we are adding in #2654. The Query builder will build an aggregation pipeline underneath so we can add $lookup + $addField stages.