populate get_posts

1 files changed, 27 insertions, 0 deletions

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"}]