MySQL :: When Should SQL Functions (conditionals, formatting) be Moved to Middleware Layer?

Over the years I've gotten into the habit (not entirely sure if good or bad, part of the reason for the question) of using if conditionals for formatting, or group functions, and date functions for date formatting.

Examples:

// Grouping: get total goals & assists for each player (1 = goal, 2,3 = assist)
SUM(IF(scoreType=1,1,0)) AS goals, SUM(IF(scoreType=2,1,IF(scoreType=3,1,0))) AS assists

// Date formatting:
DATE_FORMAT(gameDate, '%b %e') AS displayDate

// Text formatting:
IF(gameType='S','(S)','') AS gameTypeDisplay

I would be fine continuing on as is, but I'm moving to an ORM based system where "select *" is the default, and field substitution (to achieve the above), while possible, just makes an utter mess of things when you have multiple conditions to handle in your query (basically better to have pure, readable SQL, or ORM DSL, but not a mish mash of the two, imo).

So, what are the costs involved in moving say, the text formatting conditional above to middleware layer? e.g. 1,000 row query result; loop, and apply conditional for each row?

Basically I'd l开发者_开发百科ike to clean up middleware/ORM layer code and offload convenience SQL functions, but only if I'm not going to drag the server to a slow grind as middleware layer performs tons of extra processing.

Server setup is 32-bit CentOS 5, JVM (Groovy middleware) with MySQL 5 latest.

---

Dunno your data structure, but my guess is:

// Grouping: get total goals & assists for each player (1 = goal, 2,3 = assist)
SUM(IF(scoreType=1,1,0)) AS goals, SUM(IF(scoreType=2,1,IF(scoreType=3,1,0))) AS assists

This stuff can't be done efficiently at the app level anyway, so you'll want to keep it in the SQL.

// Date formatting:
DATE_FORMAT(gameDate, '%b %e') AS displayDate

// Text formatting:
IF(gameType='S','(S)','') AS gameTypeDisplay

Both of these can be done efficiently in the app. In fact, should, since doing so will mean less work for your database.

---

you could use a view.

create or replace view fancy_table AS 
   SELECT DATE_FORMAT(gameDate, '%b %e') AS gameDate,
        IF(gameType='S','(S)','') AS gameType, other, cols, here FROM table;

then your orm can use * on the view.

select * from fancy_table;