SUM / IF with INNER JOIN in MySQL--how to turn it into a knex query?

user_11512387 0
Viewed 288

I have this MySQL statement below that works as intended. Feel great about that...but...

I am struggling with how to get this into a knex.js query builder. There is no ".selectRaw" in the knex.js docs that I have seen. I've only been able to sum one column with knex.js. The knex.js doesn't seem to address this. The idea is that there are upvotes & downvotes on what I am building. I want to know the votes for each category, and then a sum total (up + down = total). I am trying to avoid breaking this up into different queries. I haven't found any good samples yet.

Can anyone help? Thanks.

    use iconwebsite;
    SELECT 
        i.icon_description,
        SUM(IF(v.vote_score > 0, v.vote_score, 0)) AS up,
        SUM(IF(v.vote_score < 0, v.vote_score, 0)) AS down,
        SUM(IF(v.vote_score, v.vote_score, 0)) AS total
        
    FROM
        icon i
            INNER JOIN
        vote v ON i.icon_id = v.icon_id
    GROUP BY i.icon_description;
knex.js node.js mysql javascript
1 Answers

You can use raw SQL with knex like that with your query:

knex.select(knex.raw(`
    use iconwebsite;
    SELECT 
        i.icon_description,
        SUM(IF(v.vote_score > 0, v.vote_score, 0)) AS up,
        SUM(IF(v.vote_score < 0, v.vote_score, 0)) AS down,
        SUM(IF(v.vote_score, v.vote_score, 0)) AS total
        
    FROM
        icon i
            INNER JOIN
        vote v ON i.icon_id = v.icon_id
    GROUP BY i.icon_description;
`))
user_98058670
Related