From e7e975568d596810b74e207e73426624c215bb24 Mon Sep 17 00:00:00 2001
From: Paul Duncan <pabs@pablotron.org>
Date: Sun, 6 Mar 2016 18:24:25 -0500
Subject: populate get_posts

---
 src/guff/post-model.cr | 27 +++++++++++++++++++++++++++
 1 file changed, 27 insertions(+)

(limited to 'src/guff/post-model.cr')

diff --git a/src/guff/post-model.cr b/src/guff/post-model.cr
index e0e82a4..112589b 100644
--- a/src/guff/post-model.cr
+++ b/src/guff/post-model.cr
@@ -1,5 +1,32 @@
 module Guff
   class PostModel < Model
+    SQL = {
+      get_posts: "
+        SELECT a.post_id,
+               a.name,
+               a.body,
+               a.html
+
+          FROM posts a
+          JOIN (
+            SELECT DISTICT
+                   c.post_id
+
+              FROM post_tags c
+
+              JOIN tags d
+                ON (d.tag_id = c.tag_id)
+              JOIN (VALUES (%{tags}) e(name)
+                ON (e.name = d.name)
+          ) b
+
+         WHERE c.%{filter}
+
+         ORDER BY %{sort} %{dir}
+         OFFSET ? LIMIT ?
+      ",
+    }
+
     def get_posts(req)
       # TODO
       [{foo: "bar"}, {foo: "asdf"}]
-- 
cgit v1.2.3