kayrunm
Repos
14
Followers
26
Following
21

Events

issue comment
[9.x] Fix issue with aggregates (withSum, etc.) for pivot columns on self-referencing many-to-many relations

Apologies @driesvints, sorted now.

For more information on the issue, you can view the issue at #44285.

Created at 3 days ago

Use better name

Created at 6 days ago

Potential fix?

Created at 6 days ago
pull request opened
Fix issue #44285 with aggregates on self-referencing many-to-many relations

Attempt to fix issue described in #44285

Created at 6 days ago
create branch
kayrunm create branch same-table-aggregate-issue
Created at 6 days ago
Created at 6 days ago
issue comment
Unable to run aggregate queries (withSum, etc.) on self-referencing many-to-many relations

I think I may have a solution for this, so I will try and work one out and if so, reference this issue with a PR.

Created at 6 days ago
opened issue
Unable to run aggregate queries (withSum, etc.) on self-referencing many-to-many relations
  • Laravel Version: 9.31.0 (from what I can see, also on 8.x)
  • PHP Version: 8.1.10 (also on 7.4.x)
  • Database Driver & Version: MySQL 8.0.30, SQLite 3.39.3

Description:

An SQL error occurs when trying to add an aggregate (e.g. withSum) for a many-to-many relation that has both sides of the relationship from the same table.

For example, in an application which contains a transactions table, I may be able to have positive (+) and negative (-) transactions and I am able to allocate positive transactions to a negative transaction. Take the following list of transactions

| ID | Description | Amount | |----|-------------|---------:| | 1 | Invoice 1 | -500.00 | | 2 | Invoice 2 | -1000.00 | | 3 | Invoice 3 | -200.00 | | 4 | Payment 1 | 1700.00 |

In this example, I want to allocate:

  • 500 from Payment 1 to Invoice 1
  • 1000 from Payment 1 to Invoice 2
  • 200 from Payment 1 to Invoice 3

I can achieve this through a transaction_transaction pivot table with an amount column signifying how much was allocated from one transaction to another.

Steps To Reproduce:

In my use case, I want to be able to allocate different amounts of Payment 1 across the invoices. I would end up with an intermediate table like this:

| allocated_from | allocated_to | amount | |----------------|--------------|--------:| | 4 | 1 | 500.00 | | 4 | 2 | 1000.00 | | 4 | 3 | 200.00 |

And two relationships on my Transaction model like so:

    public function allocatedTo(): BelongsToMany
    {
        return $this->belongsToMany(
            Transaction::class,
            'transaction_transaction',
            'allocated_from_id',
            'allocated_to_id',
        )->withPivot('amount');
    }

    public function allocatedFrom(): BelongsToMany
    {
        return $this->belongsToMany(
            Transaction::class,
            'transaction_transaction',
            'allocated_to_id',
            'allocated_from_id',
        )->withPivot('amount');
    }

When running the following:

$result = Transaction::query()
    ->withSum('allocatedTo as total_allocated', 'transaction_transaction.amount')
    ->first();

I get the following error in SQLite (I get a similar one in MySQL as well)

Illuminate\Database\QueryException : SQLSTATE[HY000]: General error: 1 no such column: laravel_reserved_0.transaction_transaction.amount (SQL: select "transactions".*, (select sum("laravel_reserved_0"."transaction_transaction"."amount") from "transactions" as "laravel_reserved_0" inner join "transaction_transaction" on "laravel_reserved_0"."id" = "transaction_transaction"."allocated_to_id" where "transactions"."id" = "transaction_transaction"."allocated_from_id") as "total_allocated" from "transactions" limit 1)

The problem seems to come from line 625–627 in QueriesRelationships:

                $hashedColumn = $this->getQuery()->from === $relation->getQuery()->getQuery()->from
                                            ? "{$relation->getRelationCountHash(false)}.$column"
                                            : $column;

Because the current query ($this->getQuery()) is for the ledger_entries table, and the relation query's base ($relation->getQuery()->getQuery()) is also for ledger_entries, we end up with laravel_reserved_0 prefixed onto the column provided (transaction_transaction.amount).

Note: This works totally fine if the two sides of the many-to-many are different tables. You can see an example of this in my reproduction repository,

Repo

https://github.com/kayrunm/laravel-sum-pivot-issue/blob/main/tests/Feature/ExampleTest.php

Created at 6 days ago

Some explanation provided

Created at 6 days ago

Some explanation provided

Created at 6 days ago
kayrunm create branch main
Created at 6 days ago
kayrunm create repository
Created at 6 days ago

Updates

Add Github actions for code style, static analysis and tests.

Created at 1 month ago

Update documentation

Created at 1 month ago
create tag
kayrunm create tag 1.0
Created at 1 month ago
delete branch
kayrunm delete branch docs
Created at 1 month ago

Update documentation (#3)

  • Move contracts to own namespace

  • Add documentation

Created at 1 month ago
pull request closed
Update documentation
Created at 1 month ago
pull request opened
Update documentation
Created at 1 month ago
create branch
kayrunm create branch docs
Created at 1 month ago

Update git attributes

Created at 1 month ago
delete branch
kayrunm delete branch ci
Created at 1 month ago

Set up CI actions (#2)

  • Add job for running tests.

  • Add job for static analysis

  • Add job for PHP CS Fixer

  • Only run CI on pull request to main

Created at 1 month ago
pull request closed
Set up CI actions
Created at 1 month ago

Only run CI on pull request to main

Created at 1 month ago

Only run CI on pull request to main

Created at 1 month ago

Only run CI on pull request to master

Created at 1 month ago