SQL Queries in Moonscript

queries.moon

toOneResult = (result) ->
  if result
    return result[1]
  else
    return result
blog = [[
  SELECT `api_blog_option_terms`.`blog_option_name`, `api_blog_option_index`.`blog_option_title` FROM `api_blog_option_index`
  JOIN api_blog_option_terms
  ON api_blog_option_index.blog_option_id = api_blog_option_terms.blog_option_id
  WHERE api_blog_option_index.domain_id = ?
]]
latestPosts = [[
  SELECT api_small_links.* FROM api_#_posts
  JOIN api_small_links
  ON api_#_posts.post_id = api_small_links.post_id
  ORDER BY api_#_posts.post_date DESC
  LIMIT 90;
]]
main = [[
  SELECT api_small_links.*, api_#_posts.post_date, DATE_FORMAT(api_#_posts.post_date, '%D %M %Y') as post_date_friendly
  FROM api_#_posts
  JOIN api_small_links
  ON api_#_posts.post_id = api_small_links.post_id
  ORDER BY api_#_posts.post_date DESC
  LIMIT ?, ?;
]]
singlePost = [[
  SELECT DATE_FORMAT(api_#_posts.post_date, '%D %M %Y') as post_date, api_#_posts.post_date as post_date_raw, api_small_links.*, api_post_big_content.post_content, "Hone Watson" as post_author, "hone-watson" as post_author_slug
  FROM api_#_posts
  JOIN api_small_links
  ON api_#_posts.post_id = api_small_links.post_id
  JOIN api_post_big_content
  ON api_small_links.post_id = api_post_big_content.post_id
  WHERE api_#_posts.post_id = ?
  LIMIT 1
]]
singleLatestPosts = [[
  SELECT api_small_links.post_id, api_small_links.post_name, api_small_links.post_title, api_small_links.image_link
  FROM api_#_posts
  JOIN api_small_links
  ON api_#_posts.post_id = api_small_links.post_id
  WHERE api_#_posts.post_date > ?
  AND api_#_posts.post_date < ?
  ORDER BY api_#_posts.post_date DESC
]]
relatedPosts = [[
  SELECT api_small_links.*
  FROM api_related_index
  JOIN api_small_links
  ON api_related_index.related_id = api_small_links.post_id
  WHERE api_related_index.post_id = ?
]]
checkData = [[ 
  SELECT post_modified
  FROM api_post_modified_post_name
  WHERE post_id = ?
  AND post_name = UNHEX(MD5(?))
]]
archive = [[
  SELECT api_#_posts.post_date, api_small_links.*, DATE_FORMAT(api_#_posts.post_date, '%D %M %Y') as post_date_friendly
  FROM api_#_posts
  JOIN api_small_links
  ON api_#_posts.post_id = api_small_links.post_id
  WHERE api_#_posts.post_date > ?
  AND api_#_posts.post_date < ?
  ORDER BY api_#_posts.post_date DESC
  LIMIT ?, ?
]]
postCount = [[
  SELECT COUNT(*) as count FROM api_#_posts;
]]
postCountByMonth = [[
  SELECT YEAR(post_date) AS year, DATE_FORMAT(post_date,'%m') AS month, MONTHNAME(post_date) AS monthname
  FROM api_#_posts
  GROUP BY YEAR(post_date), MONTH(post_date)
  ORDER BY post_date
  DESC LIMIT 12
]]
postCountByYear = [[
  SELECT YEAR( post_date ) AS year
  FROM api_#_posts
  GROUP BY YEAR( post_date )
  ORDER BY post_date DESC
  LIMIT 50
]]
tagPagesSql = [[
  FROM api_published_post_date
  JOIN api_tag_index
  ON api_published_post_date.post_id = api_tag_index.post_id
  JOIN api_small_links
  ON api_tag_index.post_id = api_small_links.post_id
  JOIN api_post_id_domain_id
  ON api_small_links.post_id = api_post_id_domain_id.post_id
  JOIN api_domain_id_name
  ON api_post_id_domain_id.domain_id = api_domain_id_name.domain_id
  WHERE tag_id = ?
  ORDER BY api_published_post_date.post_date DESC
]]
tagPages = [[
   SELECT api_published_post_date.post_date, DATE_FORMAT(api_published_post_date.post_date, '%D %M %Y') as post_date_friendly,  api_small_links.*, api_domain_id_name.domain_name " .. tagPagesSql .. " LIMIT ?, ?
]]
tagPostCount = [[
  SELECT COUNT(*) as count " .. tagPagesSql
]]
tagDetail = [[
  SELECT *
  FROM `api_tag_name_title`
  WHERE tag_name = ?
  LIMIT 1
]]
cloud = [[
  SELECT i.tag_id, COUNT( i.post_id ) AS count, t.tag_title, t.tag_name
  FROM api_tag_index i
  JOIN api_tag_name_title t
  ON i.tag_id = t.tag_id
  GROUP BY tag_id
  ORDER BY count DESC
  LIMIT 200
]]
latestSiteWidePosts = [[
  SELECT api_small_links . * , api_domain_id_name.domain_name
  FROM `api_published_post_date`
  JOIN api_small_links ON api_published_post_date.post_id = api_small_links.post_id
  JOIN api_post_id_domain_id ON api_small_links.post_id = api_post_id_domain_id.post_id
  JOIN api_domain_id_name ON api_post_id_domain_id.domain_id = api_domain_id_name.domain_id
  ORDER BY api_published_post_date.post_date DESC
  LIMIT 50
]]
{ 
  :blog,
  :main,
  :toOneResult,
  :latestPosts,
  :singlePost,
  :relatedPosts,
  :checkData,
  :archive,
  :postCount,
  :postCountByMonth,
  :postCountByYear,
  :tagPages,
  :tagDetail
  :tagPostCount,
  :cloud,
  :latestSiteWidePosts
}