add post_url to get_posts

1 files changed, 31 insertions, 14 deletions

diff --git a/src/guff.cr b/src/guff.cr
index 04708ed..1caad59 100644
--- a/src/guff.cr
+++ b/src/guff.cr
@@ -287,6 +287,7 @@ module Guff
         get_posts: "
           SELECT a.post_id,
                  a.site_id,
+                 c.name AS site_name,
                  b.state,
                  a.posted_at,
                  a.expires_at,
@@ -294,25 +295,41 @@ module Guff
                  a.slug_lock,
                  a.name,
 
-                 (CASE WHEN c.post_id IS NOT NULL THEN
+                 (CASE WHEN x.post_id IS NOT NULL THEN
                    'blog'
-                 WHEN d.post_id IS NOT NULL THEN
+                 WHEN y.post_id IS NOT NULL THEN
                    'page'
-                 WHEN e.post_id IS NOT NULL THEN
+                 WHEN z.post_id IS NOT NULL THEN
                    'project'
-                 END) as post_type
+                 END) as post_type,
+
+                 (CASE WHEN b.state = 'posted' THEN
+                   CASE WHEN x.post_id IS NOT NULL THEN
+                     strftime('/%%Y/%%m/%%d/', a.posted_at) || a.slug || '.html'
+                   WHEN y.post_id IS NOT NULL THEN
+                     '/' || a.slug || '.html'
+                   WHEN x.post_id IS NOT NULL THEN
+                     '/' || a.slug || '/'
+                   END
+                 ELSE
+                   NULL
+                 END) AS post_url
 
             FROM posts a
             JOIN states b
               ON (b.state_id = a.state_id)
-            LEFT JOIN blogs c
-              ON (c.post_id = a.post_id)
-            LEFT JOIN pages d
-              ON (d.post_id = a.post_id)
-            LEFT JOIN projects e
-              ON (e.post_id = a.post_id)
+            JOIN sites c
+              ON (c.site_id = a.site_id)
 
-           WHERE %s
+            LEFT JOIN blogs x
+              ON (x.post_id = a.post_id)
+            LEFT JOIN pages y
+              ON (y.post_id = a.post_id)
+            LEFT JOIN projects z
+              ON (z.post_id = a.post_id)
+
+           WHERE c.is_active
+             AND %s
 
            ORDER BY COALESCE(a.posted_at, a.created_at) DESC
 
@@ -463,11 +480,11 @@ module Guff
           # add type filter
           filters << case post_type
           when "blog"
-            "c.post_id IS NOT NULL"
+            "x.post_id IS NOT NULL"
           when "page"
-            "d.post_id IS NOT NULL"
+            "y.post_id IS NOT NULL"
           when "project"
-            "e.post_id IS NOT NULL"
+            "z.post_id IS NOT NULL"
           else
             raise "unknown post type: #{post_type}"
           end