withCount not working?

[SilverKenn]

Hello,

Thank you so much for this very helpful package, really powerful 👍

I'm having issue though where eager loading count produce SQL error.

This is my basic data structure User has parent_id column which is targeted to its own user table through id Applicant has user_id foreign key column Applicant and Application has many-to-many relationship

this is what Im try to achieve parent user → hasmany children user → hasmany applicant → belongsToMany applications

here is my User model

class User extends BaseModel  {
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;
        //\Staudenmeir\EloquentEagerLimit\HasEagerLimit;

    public function applicants() {
        return $this->hasMany(Applicant::class);
    }

    public function applications() {
        return $this->hasManyDeep(Application::class, [Applicant::class, 'applicant_application']);
    }

    public function children() {
        return $this->hasMany(User::class, 'parent_id');
    }

    public function parent() {
        return $this->belongsTo(User::class, 'parent_id');
    }

    public function childApplications() {
                // parent user → hasmany children user → hasmany applicant → belongsToMany applications
        return $this->hasManyDeep(
            Application::class, 
            [ User::class, Applicant::class, 'applicant_application' ], 
            ['parent_id']
        );
    }

    public function childApplicants() {
                // Parent → hasmany children → hasmany applicatant
        return $this->hasManyThrough(Applicant::class, User::class, 'parent_id');
                //return $this->hasManyDeep( Applicant::class, [ User::class ], ['parent_id'] );
    }
}

All of this methods are working fine when I eager load them using with() however, childApplications method doesn't work on withCount

This one is working fine and returns proper collections

return User::whereIn('type', [1,2])->with(['childApplications']) ->paginate();

I can see the sql query working fine as well

SELECT `applications`.*, `users`.`parent_id` AS `laravel_through_key` 
FROM   `applications` 
       INNER JOIN `applicant_application` 
               ON `applicant_application`.`application_id` = `applications`.`id` 
       INNER JOIN `applicants` 
               ON `applicants`.`id` = `applicant_application`.`applicant_id` 
       INNER JOIN `users` 
               ON `users`.`id` = `applicants`.`user_id` 
WHERE  `users`.`deleted_at` IS NULL 
AND `users`.`parent_id` IN ( 2, 3, 4, 5, 6, 7 ) 

But this one doesn't work and I can see the sql query is a mess

return User::whereIn('type', [1,2])->withCount(['childApplications'])->paginate();

the above code produces an error SQLSTATE[42S22]: Column not found: 1054 Unknown column 'applications.user_id' in 'on clause' which I understand as its trying to access non-existing column.

below is the sql query when using withCount, its joining the users table directly and skipping the applicant_application and applicants table unlike the query with using with()

SELECT `users`.*, 
    (SELECT COUNT(*) 
        FROM   `applications` 
               INNER JOIN `users` AS `laravel_reserved_0` 
                       ON `laravel_reserved_0`.`id` = `applications`.`user_id` 
        WHERE  `laravel_reserved_0`.`deleted_at` IS NULL 
        AND `users`.`id` = `laravel_reserved_0`.`parent_id` 
        AND `users`.`deleted_at` IS NULL) AS `child_applications_count` 
FROM   `users` 
WHERE  `type` IN ( 1, 2 ) 
AND `users`.`deleted_at` IS NULL 
LIMIT  15 OFFSET 0 

Would appreciate your guidance and help on where I miss something

[staudenmeir]

I'll look into it.

[staudenmeir]

This is a special kind of relationship and not supported yet with withCount()/has()/whereHas(): The fact that the parent table (users) also appears as an intermediate table requires custom treatment.

I'll work on a fix.

[SilverKenn]

Hey, Thank you so much for looking into this, really appreciate if we can have it working with this special relationship I have :)

BTW, do you have a donation button? Ive been searching through your packages and referred site finding a way to buy you a beer :)

[staudenmeir]

Thank you very much, I appreciate it! I've added a donation button to my repositories: https://paypal.me/JonasStaudenmeir

You can temporarily fix the issue by adding an alias to the intermediate table:

class User extends BaseModel {
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships,
        \Staudenmeir\EloquentHasManyDeep\HasTableAlias;

    public function childApplications() {
        return $this->hasManyDeep(
            Application::class, 
            [ User::class.' as alias', Applicant::class, 'applicant_application'], 
            ['parent_id']
        );
    }
}

[SilverKenn]

Thank you very much, working perfectly now even with using withCount()

[staudenmeir]

I released a new version that handles this issue. Unfortunately, the package can't completely fix it, but now detects it and explains what to do. The package can set the alias itself, but still requires the user to add the HasTableAlias trait to the affected model.