I want convert the fetch all answers with their corresponding votes and then group the votes according to types and thier counts.
The answers table is related to the votes by a one-to-many relation which is also related to vote_types table by a one-to-one relation.
After quering the answers table, I tried using withGraphFetched to retrieve the vote relation and count them by types for each answer. I have a SQL query that does this, but a need help converting it to objection js code.
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
Expected results:
answers:[{
answer_details,
votes:[
{upvotes:10},
{downvotes:2}
]
}]
Current results:
"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"
}
},
SQL query that I tried:
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
The result is as follows:
vote_type answer_details count
"downvote" "Test answer for a question" 7
"upvote" "Test answer for a question" 4