I have an Entity which has a self reference. The idea is: I create a new Trade when I buy a stock. Then when I sell that tread I create a new Trade which has the reference to the buy Trade. It is possible to create multiple sell Trades for one buy trade for example when I sell only a part of the stock I bought before.

#[ORM\Entity(repositoryClass: TradeRepository::class)]
#[ORM\HasLifecycleCallbacks()]
class Trade
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private $id;
    
    #[ORM\Column(type: 'string', length: 10)]
    private $symbol;
    
    #[ORM\Column(type: 'float')]
    private $price;
    
    ...
    
    #[ORM\ManyToOne(targetEntity: self::class, inversedBy: 'children')]
    private $parent;
    
    #[ORM\OneToMany(targetEntity: self::class, mappedBy: 'parent', fetch: 'EAGER')]
    private $children;
}

This works without problems. I can get all trades which have no sold trades (children) with:

/**
 * 
 */
public function findAllBuyTrades(): array
{
    $qb = $this->createQueryBuilder('trade')
        ->where('trade.parent IS NULL');

    $query = $qb->getQuery();

    return $query->execute();
}

Now I want to build a repository method which returns all my trades which are partially or fully sold. A trade is fully sold when the sum of all child trades (property "price") equals the price (property "price" again) of the parent trade. A trade is partially sold when there are some child trades but the sum of all child trades is lower than the parent trade price. I know that this can be done with some sort of a JOIN query but I cannot figure out how and I also don't know if there is a better solution or way to go instead of a JOIN query.

Can anybody point me to the right direction?

Thanks

EDIT: I have now figured out two things:

1. The datatype for price needs to be DECIMAL(X,X) instead of DOUBLE (float) to get correct calculations.

2. I can get the information I want with the following SQL Query:

    SELECT
        *
    FROM
        trade
    WHERE
        executed = (SELECT
            SUM(subtrade.executed) AS total
        FROM
            trade trade
        JOIN
            trade subtrade
        ON
            subtrade.parent_id = trade.id
        WHERE
            trade.id = XX)

I think this can be solved more beautiful instead of using a subquery, but I have no clue how yet. Also I don't know, how such a query can be done with Symfony/Doctrine.

Any help?

CodePudding user response：

Well ok I found out how to do. Maybee someone else can use this.

public function findSoldTrades(): array
{
    $qb = $this->createQueryBuilder('t')
        ->innerJoin(Trade::class, 's')
        ->where('s.parent = t.id')
        ->groupBy('t.id')
        ->having('SUM(s.executed) = t.executed')
        ->orderBy('t.created', 'ASC');

    $query = $qb->getQuery();

    return $query->execute();
}