jsingleton37
jsingleton37
PostsComments
DTDrizzle Team
Created by jsingleton37 on 9/2/2024 in #help
What is the best way to get distinct values from a table?
Oh nice! So I can just do 
export async function getYearsThatHaveVotes({ division }: { division: string }) {
  const distinctYearsWithVotes = await db
    .selectDistinct({
      year: weeklyFinalRankings.year,
    })
    .from(weeklyFinalRankings)

  return distinctYearsWithVotes
}
export async function getYearsThatHaveVotes({ division }: { division: string }) {
  const distinctYearsWithVotes = await db
    .selectDistinct({
      year: weeklyFinalRankings.year,
    })
    .from(weeklyFinalRankings)

  return distinctYearsWithVotes
}
3 replies
DTDrizzle Team
Created by jsingleton37 on 6/10/2024 in #help
onConflictDoNothing still incrementing primaryKey
AFAIK this is the default behavior
9 replies
DTDrizzle Team
Created by jsingleton37 on 7/27/2024 in #help
Confused about onConflictDoUpdate when having a constraint
Ah sweet! That fixes it! Also makes me realize I need to update the unique to be on division, week, and year 😅
4 replies
DTDrizzle Team
Created by jsingleton37 on 7/20/2024 in #help
Is there a way to just return a specific column?
I was able to just adjust the frontend code to accommodate the new structure. It was just being passed to a select component from ShadCN UI
7 replies
DTDrizzle Team
Created by jsingleton37 on 7/20/2024 in #help
Is there a way to just return a specific column?
Oh awesome! So I can just do something like 
export async function getWeeksThatHaveVotes({
  year,
  division,
}: {
  year: number
  division: string
}) {
  const weeksWithVotes = await db.query.weeklyFinalRankings.findMany({
    columns: {
      week: true,
    },
    where: (model, { eq, and }) => and(eq(model.year, year), eq(model.division, division)),
  })

  return weeksWithVotes
}
export async function getWeeksThatHaveVotes({
  year,
  division,
}: {
  year: number
  division: string
}) {
  const weeksWithVotes = await db.query.weeklyFinalRankings.findMany({
    columns: {
      week: true,
    },
    where: (model, { eq, and }) => and(eq(model.year, year), eq(model.division, division)),
  })

  return weeksWithVotes
}
And the response just looks like [ { week: 0 } ]
7 replies
DTDrizzle Team
Created by jsingleton37 on 7/20/2024 in #help
Is there a way to just return a specific column?
@Raphaël M (@rphlmr) ⚡ I ended up doing this: 
export async function getWeeksThatHaveVotes({
  year,
  division,
}: {
  year: number
  division: string
}) {
  const finalRankings = await db.query.weeklyFinalRankings.findMany({
    where: (model, { eq, and }) => and(eq(model.year, year), eq(model.division, division)),
  })

  const weeksArray = finalRankings.map((finalRanking) => finalRanking.week)

  return weeksArray
}
export async function getWeeksThatHaveVotes({
  year,
  division,
}: {
  year: number
  division: string
}) {
  const finalRankings = await db.query.weeklyFinalRankings.findMany({
    where: (model, { eq, and }) => and(eq(model.year, year), eq(model.division, division)),
  })

  const weeksArray = finalRankings.map((finalRanking) => finalRanking.week)

  return weeksArray
}
Not sure if this is correct or if one of your suggestions would be cleaner/more efficient. 🤔
7 replies
DTDrizzle Team
Created by jsingleton37 on 6/15/2024 in #help
Querying DB returning same row for two different userIds
Hmm I think I need to do 
export async function hasVoterVoted({ year, week }: GetUsersVote) {
  const user = auth()

  if (!user.userId) throw new Error('Unauthorized')

  const vote = await db.query.ballots.findFirst({
    where: (model, { eq, and }) =>
      and(eq(model.userId, user.userId), eq(model.year, year), eq(model.week, week)),
  })

  return !!vote
}
export async function hasVoterVoted({ year, week }: GetUsersVote) {
  const user = auth()

  if (!user.userId) throw new Error('Unauthorized')

  const vote = await db.query.ballots.findFirst({
    where: (model, { eq, and }) =>
      and(eq(model.userId, user.userId), eq(model.year, year), eq(model.week, week)),
  })

  return !!vote
}
2 replies
DTDrizzle Team
Created by jsingleton37 on 6/4/2024 in #help
What's the best way to prevent duplicate entries?
I started on this and ran into an issue where the do nothing on conflict causes the primary key to still increment even though no inserts are happening
11 replies
DTDrizzle Team
Created by jsingleton37 on 6/4/2024 in #help
What's the best way to prevent duplicate entries?
Also, were you suggesting to change the submitted values from 
{
  "rank_1": "",
  "rank_2": "",
  "rank_3": "",
  "rank_4": "",
  "rank_5": ""
}
{
  "rank_1": "",
  "rank_2": "",
  "rank_3": "",
  "rank_4": "",
  "rank_5": ""
}
to something like 
[
  {
    "teamId": ",
    "rank": 1
  },
  {
    "teamId": "",
    "rank": 2
  },
  {
    "teamId": "",
    "rank": 3
  },
  {
    "teamId": "",
    "rank": 4
  },
  {
    "teamId": "",
    "rank": 5
  }
]
[
  {
    "teamId": ",
    "rank": 1
  },
  {
    "teamId": "",
    "rank": 2
  },
  {
    "teamId": "",
    "rank": 3
  },
  {
    "teamId": "",
    "rank": 4
  },
  {
    "teamId": "",
    "rank": 5
  }
]
11 replies
DTDrizzle Team
Created by jsingleton37 on 6/4/2024 in #help
What's the best way to prevent duplicate entries?
The only thing I will be querying for is to make a page similar to this https://www.espn.com/college-football/rankings. And then maybe to say how each voter voted
11 replies
DTDrizzle Team
Created by jsingleton37 on 6/4/2024 in #help
What's the best way to prevent duplicate entries?
This has been super helpful! I’m using Supabase for my DB and dealing with it and Drizzle is all new to me. My IDs for the teamId are basically coming from Sanity CMS, so I guess I could make a query to Ssnity to validate the teamIds 🤔 Your suggestion on the array I’ll look into. Right now I have a zod schema for a form that uses react-hook-form and everything comes through as an object with the 25 rankings like sorank_1: teamId. Another question I have for you since I’ve gotten mixed responses on it… you touched on the indexing part. Someone also suggested making it unique on the userId, week, and year but it would require a 3rd column because I and inserting 25 rows into the database for every ballot submitted. Would it be “better” to just have a single row per ballot but have a column for every rank?
11 replies
DTDrizzle Team
Created by jsingleton37 on 6/2/2024 in #help
Drizzle Studio Not Showing Tables that are in Supabase
Nope, I followed https://orm.drizzle.team/learn/tutorials/drizzle-with-supabase and it doesn’t suggest to have that installed… but why would having pg installed potentially fix drizzle studio not loading when using one URL vs the other?
3 replies