CODEWITHSUNDEEP
phppostgresqlormdoctrine-ormrelational-database
Chris West
Chris West

Reputation: 741

doctrine sum of values depending on account source

I'm currently standing in front of an issue that I don't really know how to solve in doctrine.

This is my current Query:

SELECT SUM(t.amount) FROM AccountingBundle:Entity:Transaction t WHERE t.SourceAccount = ?1 or t.TargetAccount = ?1

While TargetAccount should be positive accounted and SourceAccount should be negative accounted. I don't really want to do 2 Queries as with this doctrine and PostgreSQL are faster.

I found an possible solution use 

CASE WHEN t.SourceAccount = ?1 THEN ... ELSE ...

But I didn't yet find a way to tell doctrine the value is negative. Can any one tell me how to achieve something like this?

Upvotes: 0

Views: 59

Answers (1)

NuLo
NuLo

Reputation: 1528

Wouldn't doing:

SELECT
  SUM(CASE WHEN t.SourceAccount = ?1 THEN t.ammount * -1 ELSE t.ammount)
FROM
  AccountingBundle:Entity:Transaction t
WHERE
  t.SourceAccount = ?1 or t.TargetAccount = ?1

solve your problem?

EDIT: the trick here is to multiply the amount by -1 making it a negative number

Upvotes: 1

Related Questions