How to count and group votes for an answer(SO style(upvote and downvote)) in objection js after using withGraphFetched to retrieve the relation

Question

I used modifiers to extract the desired output from the query but that did not generate the desired output. I managed to write the raw SQL to retrieve the desired output, but I'm stuck on doing the same with objection js.

The tables are structured as follows:

votes(id, post_id or answer_id, vote_type_id, user_id)
vote_type(id, vote_type)
answer(id,answer_details,...)

The relationship graph is also structured as follows:

Answer -> Votes --> Vote Type

The desired result is votes count by type The SQL query is as follows:

SELECT vote_type,answer_details, COUNT(*) FROM answers a
JOIN votes v ON v.post_id = a.id
JOIN vote_types vt ON vt.id = v.vote_type_id
GROUP BY vote_type, answer_details

expected result:

answers:[{
 answer_details,
 votes:[
  {upvotes:10},
  {downvotes:2}
 ]
}]

"answers": [
            {
                "id": "adff944c-d09e-45d8-86ae-1c25a9c6f82f",
                "answer_details": "Test answer for a question",
                "created_by_user_id": "0cd501eb-d543-4409-a5cb-701dc36a4d35",
                "parent_question_id": "e1df6b6f-c9be-4c64-aa5c-0b1e1a3969ae",
                "post_type_id": "e39dd8b3-09f9-4c7b-ad6c-6bf9800ebf01",
                "created_at": "2024-02-01T04:15:04.399Z",
                "updated_at": "2024-02-01T04:15:04.399Z",
                "votes": [
                    {
                        "id": "f63b2424-bd0d-4e17-94c9-8bdb6affa621",
                        "vote_type_id": "aaf72805-bb50-47e4-9425-a3e547270fac",
                        "user_id": "0cd501eb-d543-4409-a5cb-701dc36a4d35",
                        "created_at": "2024-02-01T04:25:49.621Z",
                        "updated_at": "2024-02-01T04:25:49.621Z",
                        "post_id": "adff944c-d09e-45d8-86ae-1c25a9c6f82f",
                        "vote_type": {
                            "id": "aaf72805-bb50-47e4-9425-a3e547270fac",
                            "vote_type": "upvote",
                            "count": 1,
                            "created_at": "2024-01-31T19:43:52.419Z",
                            "updated_at": "2024-01-31T19:43:52.419Z"
                        }
                    },
                    {
                        "id": "e1ce6d74-0d29-4d3d-8f8a-7254f774f0c3",
                        "vote_type_id": "aaf72805-bb50-47e4-9425-a3e547270fac",
                        "user_id": "0cd501eb-d543-4409-a5cb-701dc36a4d35",
                        "created_at": "2024-02-05T12:16:53.883Z",
                        "updated_at": "2024-02-05T12:16:53.883Z",
                        "post_id": "adff944c-d09e-45d8-86ae-1c25a9c6f82f",
                        "vote_type": {
                            "id": "aaf72805-bb50-47e4-9425-a3e547270fac",
                            "vote_type": "upvote",
                            "count": 1,
                            "created_at": "2024-01-31T19:43:52.419Z",
                            "updated_at": "2024-01-31T19:43:52.419Z"
                        }
                    },
                    {
                        "id": "f3913136-cea7-46e7-bf18-d306c4d89368",
                        "vote_type_id": "aaf72805-bb50-47e4-9425-a3e547270fac",
                        "user_id": "0cd501eb-d543-4409-a5cb-701dc36a4d35",
                        "created_at": "2024-02-05T12:16:58.325Z",
                        "updated_at": "2024-02-05T12:16:58.325Z",
                        "post_id": "adff944c-d09e-45d8-86ae-1c25a9c6f82f",
                        "vote_type": {
                            "id": "aaf72805-bb50-47e4-9425-a3e547270fac",
                            "vote_type": "upvote",
                            "count": 1,
                            "created_at": "2024-01-31T19:43:52.419Z",
                            "updated_at": "2024-01-31T19:43:52.419Z"
                        }
                   },

Answer 1

The SQL you need seems to be to just conditionally sum up and down votes.

SELECT
  a.answer_details,
  COUNT(*) AS count_votes,
  SUM(CASE WHEN vt.vote_type = 'upvote' THEN 1 ELSE -1 END) AS vote_total
FROM answers a
JOIN votes v ON v.post_id = a.id
JOIN vote_types vt ON vt.id = v.vote_type_id
GROUP BY
  a.id,
  a.answer_details;

As to objection.js, I suggest you make a new question for that.