Tips & Tricks - SQL queries management
If you have a lot of SQL queries to external systems (or even internal dynamic ones), you can encounter something like this:
Set SQL = "SELECT "_ "c.cid AS Id,"_ "c.nid AS Nid, "_ "FROM_UNIXTIME(c.created) AS Created,"_ "c.uid AS Uid,"_ "IFNULL(vv.average,0) AS AvgVote,"_ "IFNULL(vv.amount,0) AS VotesAmount,"_ "body.comment_body_value AS Text,"_ "'comment' AS Type "_ "FROM comment AS c "_ "LEFT OUTER JOIN node ON node.nid = c.nid "_ "INNER JOIN field_data_field_forum_ref AS ref ON ref.entity_id = node.nid "_ "LEFT OUTER JOIN field_data_comment_body AS body ON c.cid = body.entity_id "_ "LEFT OUTER JOIN (SELECT entity_id , SUM(value) AS average, COUNT(1) AS amount "_ "FROM votingapi_vote "_ "WHERE entity_type = 'comment' "_ "GROUP BY entity_id) AS vv ON vv.entity_id = c.cid "_ .... + 100 more lines of SQL code omitted, but you get the idea
Looks awful?
Yes.
But that's not the only problem. Queries in class code also:
- Make it harder to read and understand source code
- No SQL code highlighting
- If you want to execute the query in any other tool, you'll need to copy it to external editor and do a bunch of Find&Replaces and probably reformat the query after that
I was plagued by this problem, before developing the solution that I'd like to share.
Class definition can have a Query element declared and it can be a basic class query or a custom class query (more on that). Basic class query text gets checked during compilation, but you can write anything in custom class query body and it would compile.
Here I created a separate class to hold custom queries and a method to return query text by it's name:
Class Utils.SQL [ Abstract ]
{
/// Return query text by name. Removes linebreaks if removeNL is 1
/// write ##class(Utils.SQL).getSQL("Comments")
ClassMethod getSQL(name As %String, removeNL = {$$$NO}) As %String
{
#dim sc As %Status = $$$OK
set query = ##class(%Dictionary.QueryDefinition).IDKEYOpen($classname(), name,, .sc)
throw:$$$ISERR(sc) ##class(%Exception.StatusException).CreateFromStatus(sc)
set sql = query.SqlQuery
set:(removeNL = $$$YES) sql = $replace(sql, $$$NL, " ")
return sql
}
/// Get comments from mysql db
Query Comments() As %Query
{
SELECT
c.cid AS Id,
c.nid AS Nid,
FROM_UNIXTIME(c.created) AS Created,
c.uid AS Uid,
IFNULL(vv.average, 0) AS AvgVote,
IFNULL(vv.amount, 0) AS VotesAmount,
body.comment_body_value AS Text,
'comment' AS Type
FROM comment AS c
LEFT OUTER JOIN node ON node.nid = c.nid
INNER JOIN field_data_field_forum_ref AS ref ON ref.entity_id = node.nid
LEFT OUTER JOIN field_data_comment_body AS body ON c.cid = body.entity_id
LEFT OUTER JOIN (SELECT
entity_id,
SUM(value) AS average,
COUNT (1) AS amount
FROM votingapi_vote
WHERE entity_type = 'comment'
GROUP BY entity_id) AS vv ON vv.entity_id = c.cid
WHERE
node.status = 1
AND node.type IN ('code_package', 'documentation', 'learning_track', 'video', 'post')
GROUP BY c.cid
}
}
And in code, you can get the query text by executing:
Set SQL = ##class(Utils.SQL).getSQL("Comments")
Also, there is SQL code highlighting. And, of course, you can just copy/paste this sql to any other tool without any hassle.
How do you manage your SQL queries?