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

Question

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 like 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.

score 1 · Accepted Answer · edited May 23 '17 at 10:34

1

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.

edited May 23 '17 at 10:34

Community

1
1

answered May 25 '11 at 16:42

Denis de Bernardy

75,850
13
131
154

+1 @Denis, right, assumed grouping should stay at DB level. As a rule of thumb then, formatting to middleware layer, and the rest (conditional groupings like sum(), count(), group_concat() & friends) stay with DB? – virtualeyes May 25 '11 at 16:50
Personally, that's what I do and enforce. – Denis de Bernardy May 25 '11 at 17:24
I will give this a try, certainly will make existing queries far more ORM-able. Thanks – virtualeyes May 25 '11 at 18:15

score 1 · Answer 2 · answered May 25 '11 at 17:10

1

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;

answered May 25 '11 at 17:10

David Chan

7,347
1
28
49

thanks, fighting the ORM is not worth it. esp if you're porting a working application. Just take all your select statements, and create views. joins, sql_functions, etc are ugly for ORMs to handle. – David Chan May 25 '11 at 19:41

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

2 Answers2