Is there a better way to pass an eager loaded query as a subquery to another query?

I'm trying to do a quite complicated query (vastly simplified for the examples) while still keeping things inside eloquent rather than raw SQL. The query grabs the max value by created_at that is unique to a specific relationship bin_id. Fortunately, T-SQL (SQL Server) provides a function row_number() that makes this dead simple. Unfortunately, it requires being inside a CTE or subquery to then query on the row_number column.

Therefore, I need to pass the eager loaded query as a subquery in the from clause so that I can query on that row_number.

Here is the problem. What I'm discovering is that anything I do on the query in the from clause is also added to the main query. This is effectively duplicating all the where clauses and the selectRaw onto the main query where I only want them on the subquery. I hope I'm explaining this properly. Also the reason I want the eager loaded query in the subquery is for performance reasons to reduce the result set of the subquery.

To get around this issue, I decided to clone the main query to its own subquery object but I'm not sure if this is correct syntax or if I'll run into edge cases I didn't expect, or if there is a better "Laravel" way of doing this?

Examples

This is the query I began with. Note that adding the selectRaw is added to both the both the query inside the from and the query outside the from.

$commodities = $this->location->commodities()
->with(['inventories' => function (HasMany $query) {
    $query->from(
        $query->selectRaw('
            *,
            row_number() over(
                partition by [bin_id] order by [created_at] desc, [id] desc
            ) as [row_number]
        '), 'inventories'
    )
    ->where('row_number', '=', '1');
}])
->orderBy('slug')
->paginate(15);

To solve the duplicated clauses, I decided to just clone the query object. This works and produces the correct SQL but I'm not sure if it's correct?

$commodities = $this->location->commodities()
->with(['inventories' => function (HasMany $query) {
    $subquery = clone $query;
    $query->from(
        $subquery->selectRaw('
            *,
            row_number() over(
                partition by [bin_id] order by [created_at] desc, [id] desc
            ) as [row_number]
        '), 'inventories'
    )
    ->where('row_number', '=', '1');
}])
->orderBy('slug')
->paginate(15);

(edit) Here is an example of the generated SQL that is being duplicated.

select
    *,
    row_number() over(
        partition by [bin_id]
        order by
            [created_at] desc,
            [id] desc
    ) as [row_number]
from
    (
        select
            *,
            row_number() over(
                partition by [bin_id]
                order by
                    [created_at] desc,
                    [id] desc
            ) as [row_number]
        from
            [inventories]
        where
            [inventories].[commodity_id] in (
                1,
                3,
                4,
                5,
                6,
                7,
                9,
                10,
                11,
                12,
                13,
                17,
                18,
                19,
                20
            )
            and cast([created_at] as date) <= '2022-09-10'
            and (
                [archived_at] is null
                or cast([archived_at] as date) > '2022-09-10'
            )
            and exists (
                select
                    *
                from
                    [locations]
                    inner join [bins] on [bins].[location_id] = [locations].[id]
                where
                    [bins].[id] = [inventories].[bin_id]
                    and [locations].[id] = 1
                    and [bins].[deleted_at] is null
            )
    ) as [inventories]
where
    [inventories].[commodity_id] in (
        1,
        3,
        4,
        5,
        6,
        7,
        9,
        10,
        11,
        12,
        13,
        17,
        18,
        19,
        20
    )
    and cast([created_at] as date) <= '2022-09-10'
    and (
        [archived_at] is null
        or cast([archived_at] as date) > '2022-09-10'
    )
    and exists (
        select
            *
        from
            [locations]
            inner join [bins] on [bins].[location_id] = [locations].[id]
        where
            [bins].[id] = [inventories].[bin_id]
            and [locations].[id] = 1
            and [bins].[deleted_at] is null
    )
    and [row_number] = '1'

and the following is what I'm really after:

select
    *
from
    (
        select
            *,
            row_number() over(
                partition by [bin_id]
                order by
                    [created_at] desc,
                    [id] desc
            ) as [row_number]
        from
            [inventories]
        where
            [inventories].[commodity_id] in (
                1,
                3,
                4,
                5,
                6,
                7,
                9,
                10,
                11,
                12,
                13,
                17,
                18,
                19,
                20
            )
            and cast([created_at] as date) <= '2022-09-10'
            and (
                [archived_at] is null
                or cast([archived_at] as date) > '2022-09-10'
            )
            and exists (
                select
                    *
                from
                    [locations]
                    inner join [bins] on [bins].[location_id] = [locations].[id]
                where
                    [bins].[id] = [inventories].[bin_id]
                    and [locations].[id] = 1
                    and [bins].[deleted_at] is null
            )
    ) as [inventories]
where [row_number] = '1'