Alaskan donut Comments - Answer Overflow

Alaskan donut

Explore posts from servers

•Created by Alaskan donut on 10/14/2024 in #❓-help

Dynamically generate url for httpBatchLink

Currently, this seems to work, however if it's likely that the fallback value would ever be used here as opposed to the return of getUrl()

  const getUrl = () => {
    if (typeof window !== "undefined") {
      const { hostname, protocol, port } = window.location;
      const url = `${protocol}//${hostname}:${port}/api/trpc`;
      return url;
    }
    return undefined;
  };

  const [queryClient] = useState(() => new QueryClient());
  const [trpcClient] = useState(() =>
    trpc.createClient({
      links: [
        httpBatchLink({
          url: getUrl() ?? "http://localhost:3000/api/trpc",
          // url: "http://192.168.0.16:3000/api/trpc", // For mobile device testing
          // You can pass any HTTP headers you wish here
          // async headers() {
          //   return {};
          // },
          transformer: SuperJSON,
        }),
      ],
    }),
  );

  const getUrl = () => {
    if (typeof window !== "undefined") {
      const { hostname, protocol, port } = window.location;
      const url = `${protocol}//${hostname}:${port}/api/trpc`;
      return url;
    }
    return undefined;
  };

  const [queryClient] = useState(() => new QueryClient());
  const [trpcClient] = useState(() =>
    trpc.createClient({
      links: [
        httpBatchLink({
          url: getUrl() ?? "http://localhost:3000/api/trpc",
          // url: "http://192.168.0.16:3000/api/trpc", // For mobile device testing
          // You can pass any HTTP headers you wish here
          // async headers() {
          //   return {};
          // },
          transformer: SuperJSON,
        }),
      ],
    }),
  );

3 replies

PPrisma

•Created by Papa Smerf on 9/12/2024 in #help-and-questions

Selecting random records from table

I'm really hoping Prisma will support random queries at some point... Here is an extension that achieves this: https://github.com/nkeil/prisma-extension-random. I haven't used this yet, but it's worth looking into.

5 replies

PPrisma

•Created by Alaskan donut on 8/28/2024 in #help-and-questions

Complicated groupBy query

Hey Jon, your query worked well. I modified it return the structure I needed:

-- @param {String} $1:Post ID
-- @param {String} $2:User ID
SELECT
    p."id" AS "postId",
    reaction_types."id" AS "reactionTypeId",
    CAST(COUNT(reactions."id") AS INTEGER) AS "reactionCount",
    CASE
    WHEN COUNT(CASE WHEN reactions."user_id" = $2::uuid THEN 1 END) > 0 THEN TRUE
    ELSE FALSE
    END AS "reacted"
FROM posts p
LEFT JOIN reactions ON p."id" = reactions."post_id"
LEFT JOIN reaction_types ON reaction_types."id" = reactions."reaction_type_id"
WHERE p."id" = $1::uuid
GROUP BY p."id", p."user_id", reaction_types."id"
ORDER BY p."id", reaction_types."id";

-- @param {String} $1:Post ID
-- @param {String} $2:User ID
SELECT
    p."id" AS "postId",
    reaction_types."id" AS "reactionTypeId",
    CAST(COUNT(reactions."id") AS INTEGER) AS "reactionCount",
    CASE
    WHEN COUNT(CASE WHEN reactions."user_id" = $2::uuid THEN 1 END) > 0 THEN TRUE
    ELSE FALSE
    END AS "reacted"
FROM posts p
LEFT JOIN reactions ON p."id" = reactions."post_id"
LEFT JOIN reaction_types ON reaction_types."id" = reactions."reaction_type_id"
WHERE p."id" = $1::uuid
GROUP BY p."id", p."user_id", reaction_types."id"
ORDER BY p."id", reaction_types."id";

Here is the TypeScript that I used to merge the query into another posts query. This isn't procedure isn't necessary, since this could be handled with a single query, but I will deal with this later. Thanks again!

const result = await prisma.post.findMany({
  where: { user: { username: { equals: username, mode: "insensitive" } } },
  include: getPostInclude(user.id),
  orderBy: { createdAt: "desc" },
  take: pageSize + 1,
  cursor: cursor ? { id: cursor } : undefined,
});

const posts = result.slice(0, pageSize); // Posts are in desc order
const postIds = posts.map((post) => post.id);

const postTransactions = postIds.map((postId) => {
  return prisma.$queryRawTyped(getPostReactions(postId, user.id));
});

const postReactions = await prisma.$transaction(postTransactions); // Here is that query you provided! :)

const postsWithReactions = posts.map((post, index) => ({
  data: post,
  reactions: postReactions[index] as TPostReactions[],
}));

const nextCursor = posts.length > pageSize ? posts[pageSize].id : null;

const data: TProfilePostsPage = {
  posts: postsWithReactions,
  nextCursor,
};

const result = await prisma.post.findMany({
  where: { user: { username: { equals: username, mode: "insensitive" } } },
  include: getPostInclude(user.id),
  orderBy: { createdAt: "desc" },
  take: pageSize + 1,
  cursor: cursor ? { id: cursor } : undefined,
});

const posts = result.slice(0, pageSize); // Posts are in desc order
const postIds = posts.map((post) => post.id);

const postTransactions = postIds.map((postId) => {
  return prisma.$queryRawTyped(getPostReactions(postId, user.id));
});

const postReactions = await prisma.$transaction(postTransactions); // Here is that query you provided! :)

const postsWithReactions = posts.map((post, index) => ({
  data: post,
  reactions: postReactions[index] as TPostReactions[],
}));

const nextCursor = posts.length > pageSize ? posts[pageSize].id : null;

const data: TProfilePostsPage = {
  posts: postsWithReactions,
  nextCursor,
};

7 replies

PPrisma

•Created by Alaskan donut on 8/28/2024 in #help-and-questions

Complicated groupBy query

Thank you, @Jon Harrell! I greatly appreciate this.

7 replies

PPrisma

•Created by Alaskan donut on 8/28/2024 in #help-and-questions

Complicated groupBy query

model Post {
  id            String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  // ...

  userId  String  @map("user_id") @db.Uuid
  mediaId String? @map("media_id") @db.Uuid

  user      User       @relation(fields: [userId], references: [id], onDelete: Cascade, onUpdate: NoAction)
  media     Media[]
  reactions Reaction[]

  @@map("posts")
}

model Reaction {
  id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  // ...

  postId         String @map("post_id") @db.Uuid
  reactionTypeId String @map("reaction_type_id")
  userId         String @map("user_id") @db.Uuid

  reactionType ReactionType @relation(fields: [reactionTypeId], references: [id])
  post         Post         @relation(fields: [postId], references: [id])
  user         User         @relation(fields: [userId], references: [id])

  @@unique([postId, userId, reactionTypeId])
  @@map("reactions")
}

model ReactionType {
  id    String @unique
  name  String @unique
  emoji String

  reaction Reaction[]

  @@map("reaction_types")
}

model Post {
  id            String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  // ...

  userId  String  @map("user_id") @db.Uuid
  mediaId String? @map("media_id") @db.Uuid

  user      User       @relation(fields: [userId], references: [id], onDelete: Cascade, onUpdate: NoAction)
  media     Media[]
  reactions Reaction[]

  @@map("posts")
}

model Reaction {
  id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  // ...

  postId         String @map("post_id") @db.Uuid
  reactionTypeId String @map("reaction_type_id")
  userId         String @map("user_id") @db.Uuid

  reactionType ReactionType @relation(fields: [reactionTypeId], references: [id])
  post         Post         @relation(fields: [postId], references: [id])
  user         User         @relation(fields: [userId], references: [id])

  @@unique([postId, userId, reactionTypeId])
  @@map("reactions")
}

model ReactionType {
  id    String @unique
  name  String @unique
  emoji String

  reaction Reaction[]

  @@map("reaction_types")
}

7 replies

PPrisma

•Created by Alaskan donut on 6/6/2024 in #help-and-questions

Flat response from relational query?

If I have no choice but to manually flatten, I will just use a map function on the array.

4 replies

Gaming

Programming