articles.js

// pg DB connection from config file /helpers/db.js,, automatically converts to JSON on output with result.rows
const db = require('../helpers/db.js')

// query to list all LIKED articles for specific user
//    SELECT * FROM articles INNER JOIN likes l on articles.id = l.article_id
//                         INNER JOIN users u on u.id = l.user_id WHERE u.id=28;

exports.getAll = async function getAll() {
    let sql = 'SELECT *, views FROM articles, views WHERE views.id = articles.id \
    ORDER BY "modifiedDate" DESC;'
    let results = await db.query(sql)
    .then(results => {
        return results
    })
    .catch(e => console.error(e.stack))
    return results;
}

exports.getById = async function getById(id) {
    let sql = 'SELECT * FROM articles INNER JOIN views ON views.id = articles.id \
         WHERE articles.id='+id+';';
    let result = await db.query(sql)
    .then(async result => {
        let sql='UPDATE views SET views = views + 1 WHERE id = '+id+';';
        await db.query(sql)
        return result
    })
    .catch(e => console.error(e.stack))
    return result
}


exports.createArticle = async function createArticle(newArticle) {
    let keys = Object.keys(newArticle)
    let values = Object.values(newArticle)
  let keysQuoted = keys.map(q => `"${q}"`);
    let valuesQuoted = values.map(q => `'${q}'`);
    if ((keys != null) && (values != null)) {
        let sql = `WITH articles AS (INSERT INTO articles(`+keysQuoted+`) VALUES(`+valuesQuoted+`) RETURNING *), \
                        views AS (INSERT INTO views(id) SELECT id FROM articles RETURNING *) \
                   SELECT articles.*, views.views FROM articles, views WHERE views.id = articles.id;`;
        let obj = await db.query(sql)
        .then(obj => {
            return obj
        })
        .catch(e => console.error(e.stack))
    return obj
    }
}

exports.updateArticle = async function updateArticle(id, updatedArticle) {
    let keys = Object.keys(updatedArticle)
    let values = Object.values(updatedArticle)
    let keysQuoted = keys.map(q => `"${q}"`);
    let valuesQuoted = values.map(q => `'${q}'`);
    let i = 0;
        for (i; i < keys.length; i++) {
            let sql = `UPDATE articles SET `+keysQuoted[i]+`=`+valuesQuoted[i]+` WHERE id=`+ id +' RETURNING *;';
            var obj = await db.query(sql)
            .then(obj => {
                return obj
            })
            .catch(e => console.error(e.stack))
        }
    return obj;
}

exports.deleteArticle = async function deleteArticle(id) {
    let sql = 'DELETE FROM views WHERE id='+id+'; \
               DELETE FROM articles WHERE id='+id+' RETURNING title;';
    let obj = await db.query(sql)
    .then(obj => {
        return obj
    })
    .catch(e => console.error(e.stack))
    return obj;
}