Last time I was talking about the DbConfig model and the fetchPosts function, so let's dive in. First, a few examples of using the config:
const Db = require('../lib/db')
const DbConfig = require('../models/dbconfig')
// Fetching one post
const config = new DbConfig()
config.fields = ['link']
config.fieldValues = [req.baseUrl.substring(1)] // The path without the initial `/`
config.limit = 1
// Search, meaning to fetch based on a query
const config = new DBConfig()
config.searching = true // Special logic for creating the query command
config.limit = 0 // All
config.fields = ['body', 'title']
// Create an array of strings with matches between quotes as single items,
// and matches outside quotes as multiple items
// "one item" several items -> ['one item', 'several', 'items']
config.fieldValues = req.query.query
.match(/\"(.*?)\"|(\w+)/g)
.map(function(match) {
return match.replace(/"/g, '')
})
// Archive, meaning to fetch all posts, but only the title, link and datetime to group them
const config = new DbConfig()
config.limit = 0
config.columns = 'title, link, datetime'
// Not an array, like fields and fieldValues,
// because they go together, anyway: 'SELECT title, link, datetime FROM posts'As for the fetchPosts, it's a bit tricky, but not that bad. First, let's introduce a new object, Result, to use as the result for fetching posts:
DbResult = function() {
this.posts = []
this.totalPosts = 0
}And then, the main course:
Db.fetchPosts = function(config) {
// As I said, columns is used in one go
let query = 'SELECT ' + config.columns + ' FROM posts'
// These two default to null, so if they contain anything, we are going to do add WHERE clauses to the query.
// For the archive we fetch them all, for example
const queried = config.fields && config.fieldValues
if (queried) {
// If we are searching, we are going to have more than one WHERE clause
if (config.searching) {
query += ' WHERE '
// Create a string that looks like
// WHERE field1 LIKE '%value1%' OR field1LIKE '%value2%' OR field2 LIKE '%value1%' OR field2 LIKE '%value2%'
config.fields.forEach(function(field) {
config.fieldValues.forEach(function(value) {
query += field + ' LIKE '
query += '\'%' + value + '%\''
query += ' OR '
})
})
// Remove the last ' OR '
query = query.slice(0, -4)
}
// This is basically for fetching one post by its link
else {
query += ' WHERE ' + config.fields[0] + ' = \'' + config.fieldValues[0] + '\''
}
}
// Then we order the query, default by the datetime field, and ascending
query += ' ORDER BY ' + config.orderBy + ' ' + config.orderDirection
return new Promise(function(resolve, reject) {
pg.connect(dbURL, function(err, client, done) {
client.query(query, function(err, result) {
done()
if (err) {
reject(new Error(err.message + ' ' + err.id))
return
}
const DbResult = require('../models/dbresult')
const res = new DbResult()
res.totalPosts = result.rows.length
let posts = result.rows.reverse()
// When updating all posts must be fetched, otherwise just the ones that are not in the future.
// Sometimes I write posts now, but date them in the future,
// but I also want to be able to access them by URL, to be able to see how they look before getting live.
if (!config.updating && config.limit != 1) {
posts = posts.filter(function(rawPost) {
// Just a helper function that transforms YYYY-MM-dd into a Date object
const date = Post.dateFromDateTime(rawPost.datetime)
return date && date < new Date()
})
}
// If the limit is 0, then we asked for all posts
if (config.limit) {
posts = posts.slice(config.offset, config.offset + config.limit)
}
res.posts = posts.map(function(rawPost) {
let body = rawPost.body
// If the user is searching, I'd like to mark his searched terms throughout the results
if (queried && config.searching) {
// This will create a regex like /value1|value2/, matching any string that equals any value
let pattern = ''
config.fieldValues.forEach(function(value) {
pattern += value + '|'
})
// Remove the last '|'
pattern = pattern.slice(0, -1)
// And make the regex global and insensitive
const regex = new RegExp(pattern, 'gi')
// Wrap the value between a mark of class 'search', to style it
body = body.replace(
regex,
'<mark class=\'search\'>\$&</mark>'
)
let lines = []
// Split the body into lines
body.split('\n').forEach(function(line) {
let _line = line
// Search for hrefs, asset links and images
if (line.indexOf('<a href=<') ||
line.indexOf('/assets/') ||
line.indexOf('<img src=')) {
// And remove the occurrences of the search marks on that line,
// otherwise links and images will break
_line = _line.replace(/<\/mark>/g, '')
_line = _line.replace(/<mark class='search'>/g, '')
}
lines.push(_line)
})
// Then join the lines again
body = lines.join('\n')
}
return new Post(
rawPost.title,
body,
rawPost.readingtime,
rawPost.datetime,
rawPost.modified || '',
rawPost.link
)
})
resolve(res)
})
})
})
}As for using it, here's my post route:
const router = require('express').Router()
const NotFound = require('./not-found')
const Db = require('../lib/db')
// This resolves to http://rolandleth.com/we-are-here
router.get('/', function(req, res) {
const config = new DB.Config()
// Query by the link field
config.fields = ['link']
// Use the path without the starting '/' as the value
config.fieldValues = [req.baseUrl.substring(1)]
// Fetch only 1
config.limit = 1
Db.fetchPosts(config).then(function(data) {
if (data.posts.length == 0) {
// Since I need this in several places, I just created a helper to render the 404 page
NotFound.show(res)
return
}
// Get the first post and render it
const post = data.posts[0]
res.render('index', {
posts: data.posts, // Since it's the same partial for one post, or several, it expects an array
title: post.title,
page: 1,
totalPosts: 1,
metadata: post.title
})
}).catch(function() {
NotFound.show(res)
})
})I'm sure I could improve a lot of code, so if you have any suggestions (or questions), I'd be more than happy to hear from you @rolandleth.