Symfony 3 - Weird data return from Repository

Question

I've got Repository method which returns sum of missing invoices price to pay

public function getAmountToPay()
{
    $query = $this->createQueryBuilder('i')
        ->select('c.name, (i.price - COALESCE(sum(p.amount), 0)) as price')
        ->leftJoin('i.payments', 'p')
        ->join('i.company', 'c')
        ->where('i.dueDate > :now')
        ->groupBy('i.id')
        ->having('sum(i.price) > SUM(p.amount) OR SUM(p.amount) IS NULL ')
        ->setParameter('now', new \DateTime())
        ->getQuery();

    return $query->getResult();
}

Pure SQL returns normal rows but if I do it in doctrine, it returns some weird data in array with 0 values:

"afterDeadline" => array:3 [▼
0 => array:2 [▼
  "name" => "Example company #1"
  "price" => "0"
]
1 => array:2 [▼
  "name" => "Example company #2"
  "price" => "0"
]
2 => array:2 [▼
  "name" => "Example company #1"
  "price" => "117.99000000000001"
]

Why it has companies with 0 value? Only the last array index is ok.

you have the sql COALESCE , if the result of the sum is null you get 0 from COALESCE — madalinivascu
– madalinivascu, Commented Oct 16, 2017 at 9:38
Ohh, right. I didn't see that. Is there any way to keep that COALESCE and have only companies with values more than 0? — peripe
– peripe, Commented Oct 16, 2017 at 9:43
I cannot write WHERE clause 'cuz of SUM but if I do HAVING clause it says SQLSTATE[42S22]: Column not found: 1054 Unknown column 'i1_.price' in 'having clause' — peripe
– peripe, Commented Oct 16, 2017 at 9:57

Łukasz D. Tulikowski · Accepted Answer · 2017-10-16 11:14:20Z

1

Have you tried the solution which madalinivascu suggested. It will look like similar to this:

public function getAmountToPay()
{
    $query = $this->createQueryBuilder('i')
        ->select('c.name, (i.price - COALESCE(sum(p.amount), 0)) as price')
        ->leftJoin('i.payments', 'p')
        ->join('i.company', 'c')
        ->where('i.dueDate > :now')
        ->andWhere('i.price != 0')
        ->groupBy('i.id')
        ->having('sum(i.price) > SUM(p.amount) OR SUM(p.amount) IS NULL ')
        ->setParameter('now', new \DateTime())
        ->getQuery();

    return $query->getResult();
}

answered Oct 16, 2017 at 11:14

Łukasz D. Tulikowski

1

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Symfony 3 - Weird data return from Repository

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related