Theo's Typesafe Cult•14mo ago

kysely join select query

hey guys im playing with kysely and im trying to get the count of the votes for and votes against, however it seems that only the votedForIds are counted. currently my code looks like this

const query = db
    .selectFrom("Anime")
    .leftJoin("VoteAnime", "Anime.id", "votedForId")
    .select([
      "Anime.id",
      "Anime.name",
      "Anime.imageUrl",
      count("VoteAnime.votedForId").as("VoteFor"),
      count("VoteAnime.votedAgainstId").as("VoteAgainst"),
    ])
    .groupBy("Anime.id")
    .execute();

const query = db
    .selectFrom("Anime")
    .leftJoin("VoteAnime", "Anime.id", "votedForId")
    .select([
      "Anime.id",
      "Anime.name",
      "Anime.imageUrl",
      count("VoteAnime.votedForId").as("VoteFor"),
      count("VoteAnime.votedAgainstId").as("VoteAgainst"),
    ])
    .groupBy("Anime.id")
    .execute();

anyone know how can i fix it? thanks!

4 Replies

Igal•14mo ago

Hey 👋

however it seems that only the votedForIds are counted.

What do you mean by that? what are you getting in the result? Is votedAgainstId nullable?

noctate•14mo ago

Nope, the voteAgainst Has the same count value as voteFor. But when I change the leftJoin to use 'votedAgainstId', the opposite is happening. Its interesting because if I would do query that don't use count then both votedAgainstId and voteForId are in the result

Igal•14mo ago

Example: anime: 1 (id) anime: 2 anime: 3 anime: 5 vote: 1, 3 (for, against) vote: 1, 2 vote: 1, 5 joined by for: 1, 1, 3 (anime.id, for, against) 1, 1, 2 1, 1, 5 2, null, null 3, null, null 5, null, null count by for: 1: 3 2: 0 3: 0 5: 0 count by against, the same. why? because count by column just counts number of records with non-null values in that column. To count number of votes for / against for each anime, you'll need a different query Something like: https://kyse.link/?p=s&i=VZ0I3NacuaSXkqgqoM54

noctate•14mo ago

I see! Thank you!

Gaming

Programming

kysely join select query