hasManyDeepFromRelations and belongsToMany

[shelestovas]

Hi! Thank you for the super package, I solved a lot of problems with it)But there was another one that I didn't understand how to solve yet

`<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model; use Staudenmeir\EloquentEagerLimit\HasEagerLimit; use Staudenmeir\EloquentHasManyDeep\HasRelationships;

class EduLevelType extends Model { use HasRelationships; use HasEagerLimit;

public function eduLevels()
{
    return $this->hasMany(EduLevel::class);
}

public function specialities()
{
    // EduLevelType hasMany EduLevel -> EduLevel hasMany Direction -> Direction hasMany Speciality
    return $this->hasManyDeep(
        Speciality::class,
        [
            EduLevel::class,
            Direction::class
        ]
    );
}

public function professions()
{
    // EduLevelType hasMany EduLevel -> EduLevel hasMany Direction -> Direction hasMany Speciality -> Speciality belongsToMany Profession
    return $this->hasManyDeepFromRelations($this->specialities(), (new Speciality)->professions());
}

} `

in controller:

$allCounts = EduLevelType::withCount([ 'specialities', 'professions' ]) ->get(); This works, but profession counts based on an intermediate table "profession_speciality", but need to use the "professions" table

[staudenmeir]

What does Speciality::professions() look like?

[shelestovas]

public function professions() { return $this->belongsToMany(Profession::class); }

[shelestovas]

so far I have solved the problem this way

$counts = EduLevelType::withCount([
            'specialities',
            'professions' => function ($query) {
                $query->select(DB::raw('count(distinct professions.id)'));
            }
        ])
            ->get();

[staudenmeir]

Can you log the executed SQL?

[shelestovas]

Without $query->select(DB::raw('count(distinct professions.id)'));

SELECT 
    `edu_level_types`.*,
    (SELECT 
            COUNT(*)
        FROM
            `specialities`
                INNER JOIN
            `directions` ON `directions`.`id` = `specialities`.`direction_id`
                INNER JOIN
            `edu_levels` ON `edu_levels`.`id` = `directions`.`edu_level_id`
        WHERE
            `edu_level_types`.`id` = `edu_levels`.`edu_level_type_id`) AS `specialities_count`,
    (SELECT 
            COUNT(*)
        FROM
            `professions`
                INNER JOIN
            `profession_speciality` ON `profession_speciality`.`profession_id` = `professions`.`id`
                INNER JOIN
            `specialities` ON `specialities`.`id` = `profession_speciality`.`speciality_id`
                INNER JOIN
            `directions` ON `directions`.`id` = `specialities`.`direction_id`
                INNER JOIN
            `edu_levels` ON `edu_levels`.`id` = `directions`.`edu_level_id`
        WHERE
            `edu_level_types`.`id` = `edu_levels`.`edu_level_type_id`) AS `professions_count`
FROM
    `edu_level_types`

[staudenmeir]

Can you provide a database dump with the actual data or some sample data?