const mysql = require('mysql');

const connection = mysql.createConnection({
    host : 'localhost',
    database : 'fss',
    user : 'root',
    password : '1111'
});

const pool = mysql.createPool({
    host : 'localhost',
    database : 'fss',
    user : 'root',
    password : '1111'
});

module.exports = {connection, pool};

const {connection, pool} = require('./config');

const Games = {
    selectPart: function(filtr, callback){
        pool.getConnection(function(error, connection){
            if(error){
                console.log("Error", error);
                callback(error);
            }else{
                let {id1, id2, table1, table2, column1, column2, value1, value2, beginningWith, amount} = filtr;

                connection.query("SELECT id, (SELECT COUNT(??) FROM ?? WHERE ??.?? = ?" +
                    " AND ?? = ?) as dislike, " +
                    "(SELECT COUNT(??) FROM ?? WHERE ??.?? = ?" +
                    " AND ?? = ?) as lik FROM ?? LIMIT ?, ?",
                    [id2, table2, table1, id1, id2, column2, value2, id2, table2, table1, id1,
                    id2, column1, value1, table1, beginningWith, amount], function(err, result){
                    callback(null, result);
                });
            }
            connection.release();
        });
    }
};

module.exports = Games;

app.get('/facts', function(req, result, next){
    var filtr = {
        id1: 'id',
        id2: 'id_fact',
        table1: 'facts',
        table2: 'rating',
        column1: 'is_like',
        column2: 'is_like',
        value1: true,
        value2: false,
        beginningWith: 0,
        amount: 5
    };
    Games.selectPart(filtr, function(err, res){
        if(err){
            console.log(err);
        }else{
            console.log(res);
        }
    });
});

SELECT id, (SELECT COUNT(id_fact) FROM rating WHERE facts.id = id_fact AND
is_like = false) as d, (SELECT COUNT(id_fact) FROM rating WHERE facts.id =
id_fact AND is_like = true) as l FROM facts LIMIT 0, 5;

+----+------+------+
| id | d    | l    |
+----+------+------+
| 23 |    0 |    2 |
| 24 |    1 |    1 |
| 25 |    0 |    0 |
| 26 |    1 |    0 |
| 27 |    0 |    3 |
+----+------+------+
5 rows in set (0.00 sec)

[ RowDataPacket { id: 23, d: 0, l: 0 },
  RowDataPacket { id: 24, d: 0, l: 0 },
  RowDataPacket { id: 25, d: 0, l: 0 },
  RowDataPacket { id: 26, d: 0, l: 0 },
  RowDataPacket { id: 27, d: 0, l: 0 } ]