Laravel Eloquent join multiple nested tables with condition on the last table, custom column select

Question

Lets say I have models A, B ,C and D as well as A_TYPE which is a data type table for A) I know that in D, there is a column another_system_id (which is what I have.

I want to retrieve all model A entities, which tie into it, while also selecting columns from A, A_TYPE and D (foreign_system_id, another_entity_id)

The relevant relationships:

Model A

public function bs()
{
    return $this->hasMany(B::class);
}

And also

public function b_type()
{
    return $this->belongsTo(BType::class);
}

Model B:

public function cs()
{
    return $this->hasMany(C::class);
}

Next one is a bit trickier, there is no formal FK relationship in the DB between C and D for reasons outside the scope of the question, so I added this:

Model C

public function system_source()
{
    return $this->hasOne(D::class, 'id', 'datasource_object_id');
}

Then, based on similar question at Laravel nested relationships, when I try to do:

$a = A::with('B.C.system_source')
    ->whereHas('B.C.d_source', function (Builder $query) {
        $query->whereIn('another_system_id', array(9575, 9576));
    });

When dumping the toSql output of this, it looks like so:

select * 
from `as` 
where exists (
    select * 
    from `bs` 
    where `as`.`id` = `bs`.`b_id` 
    and exists (select * 
        from `cs` 
        where `bs`.`id` = `cs`.`c_id` 
        and exists (select * 
            from `ds` 
            where `cs`.`datasource_object_id` = `ds`.`id` 
            and `another_system_id` in (?, ?))));

When I substitute the two question marks, I get a single result. However when I try to let Laravel run it using get()->count(), I get 0!

I am at a loss, here is the simple query in human written SQL syntax to get the correct results:

        SELECT d.form_id, d.question_id, a.id, a.`name`, a.parent_id, at.`name` as 'A Type'
        FROM a
        JOIN a_types at ON a.a_type_id = at.id
        JOIN b on b.a_id = a.id
        JOIN c on c.b_id = b.id
        JOIN d on d.id = c.datasource_object_id
        WHERE d.another_system_id in (9576, 9575);

You can see my problem, two issues:

1. How do I select the columns? I have tried so many different ways like suggested in the link using ::with with function for each nested level.
2. The generated query is MUCH worse in performance, about twice worse compared to hand written one! I would like Eloquent to generate query like the one I wrote, which is simple and efficient