Skip to content

withCount and loadCount methods not working on hybrid hasMany relationships #2470

@m-ostadi

Description

@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) 

Activity

GromNaN

GromNaN commented on Nov 9, 2023

@GromNaN
Member

Issue confirmed. Tracked in PHPORM-69

hans-thomas

hans-thomas commented on Nov 21, 2023

@hans-thomas
Contributor

@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

GromNaN commented on Nov 21, 2023

@GromNaN
Member

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @GromNaN@m-ostadi@hans-thomas

        Issue actions

          withCount and loadCount methods not working on hybrid hasMany relationships · Issue #2470 · mongodb/laravel-mongodb