Hebilicious Posts - Answer Overflow

Hebilicious

•Created by Hebilicious on 1/11/2024 in #help

Postgres ARRAY and sql template proper syntax

Hello there ! I have a where clause in a query that looks like this :

        .where(
          sql`
            EXISTS (
              SELECT 1
              FROM unnest(${S.user.interests}) AS interest
              JOIN unnest(ARRAY[${courseTagsString}]) AS target_interest ON LOWER(interest) = LOWER(target_interest)
            )
          `
        )

        .where(
          sql`
            EXISTS (
              SELECT 1
              FROM unnest(${S.user.interests}) AS interest
              JOIN unnest(ARRAY[${courseTagsString}]) AS target_interest ON LOWER(interest) = LOWER(target_interest)
            )
          `
        )

I can't figure out a nice way to pass something to that array. AFAIK the only think that works is to .join() the string and manually do :

JOIN unnest(ARRAY${sql.raw(`[${formattedString}]`)})

JOIN unnest(ARRAY${sql.raw(`[${formattedString}]`)})

Is there a nicer way ? If that's the way, I'm happy to add that to the docs, because it took a lot of fiddling to figure out.

1 replies

DTDrizzle Team

•Created by Hebilicious on 1/2/2024 in #help

.$inferSelect for relations

Hello there, is there a convenient way to get the list of relations of a specific table ? Either directly or at the type level. Basically getting what the with argument accept in the rqb. Cheers o/

5 replies

DTDrizzle Team

•Created by Hebilicious on 12/26/2023 in #help

Drizzle studio : Many To Many : There is not enough information to infer relation

I can't seen to get rid of the following error with Drizzle studio :

Error: There is not enough information to infer relation "__public__.course.recommendations"

Error: There is not enough information to infer relation "__public__.course.recommendations"

Here are the relevant parts of my schema.

export const course = pgTable('Course', {
  id: text('id').primaryKey().default(uuid()).notNull(),
  createdAt: timestamp('createdAt', { precision: 3, mode: 'string' }).defaultNow().notNull(),
  //   updatedAt: timestamp('updatedAt', { precision: 3, mode: 'string' }).notNull(),
  name: text('name').notNull(),
  description: text('description').notNull(),
  ordinal: integer('ordinal').notNull(),
  duration: integer('duration').default(0).notNull(),
  thumbnails: text('thumbnails').array(),
  tags: text('tags').array(),
  supportingMedias: text('supportingMedias').array(),
  active: integer('active').default(0).notNull(),
  previewVideo: text('previewVideo'),
  downloadMediaZip: text('downloadMediaZip'),
  rating: numericNumber('rating', { precision: 65, scale: 30 }).default(0).notNull(),
  userId: text('userId')
    .notNull()
    .references(() => user.id, cascade)
});

export const courseRelations = relations(course, ({ one, many }) => ({
  // 1 - N
  user: one(user, { fields: [course.userId], references: [user.id] }),
  // M - N
  bookmarks: many(bookmark),
  chapters: many(chapter, { relationName: 'chaptersCourse' }),
  comments: many(comment),
  courseSubscriptions: many(courseSubscription),
  likes: many(like),
  notes: many(note),
  ratingsReviews: many(ratingsReview),
  videoLogs: many(videoLog),
  // M - M
  recommendations: many(recommendation)
}));


export const recommendation = pgTable(
  'Recommendation',
  {
    id: text('id').primaryKey().default(uuid()).notNull(),
    createdAt: timestamp('createdAt', { precision: 3, mode: 'string' }).defaultNow().notNull(),
    // updatedAt: timestamp('updatedAt', { precision: 3, mode: 'string' }).notNull(),
    tags: text('tags').default('{}').array(),
    userId: text('userId')
      .notNull()
      .references(() => user.id, cascade)
  },
  (table) => {
    return {
      userIdKey: uniqueIndex('Recommendation_userId_key').on(table.userId)
    };
  }
);

export const recommendationRelations = relations(recommendation, ({ one, many }) => ({
  user: one(user, { fields: [recommendation.userId], references: [user.id] }),
  courses: many(course),
  interests: many(interest)
}));

/**
 * Many to Many
 */

export const recommendationsToCourses = pgTable(
  'Recommendations_to_Courses',
  {
    recommendationId: text('recommendationId')
      .notNull()
      .references(() => recommendation.id, cascade),
    courseId: text('courseId')
      .notNull()
      .references(() => course.id, cascade)
  },
  (t) => ({ pk: primaryKey({ columns: [t.recommendationId, t.courseId] }) })
);

export const recommendationsToCoursesRelations = relations(recommendationsToCourses, ({ one }) => ({
  course: one(course, {
    fields: [recommendationsToCourses.courseId],
    references: [course.id]
  }),
  recommendation: one(recommendation, {
    fields: [recommendationsToCourses.recommendationId],
    references: [recommendation.id]
  })
}));

export const course = pgTable('Course', {
  id: text('id').primaryKey().default(uuid()).notNull(),
  createdAt: timestamp('createdAt', { precision: 3, mode: 'string' }).defaultNow().notNull(),
  //   updatedAt: timestamp('updatedAt', { precision: 3, mode: 'string' }).notNull(),
  name: text('name').notNull(),
  description: text('description').notNull(),
  ordinal: integer('ordinal').notNull(),
  duration: integer('duration').default(0).notNull(),
  thumbnails: text('thumbnails').array(),
  tags: text('tags').array(),
  supportingMedias: text('supportingMedias').array(),
  active: integer('active').default(0).notNull(),
  previewVideo: text('previewVideo'),
  downloadMediaZip: text('downloadMediaZip'),
  rating: numericNumber('rating', { precision: 65, scale: 30 }).default(0).notNull(),
  userId: text('userId')
    .notNull()
    .references(() => user.id, cascade)
});

export const courseRelations = relations(course, ({ one, many }) => ({
  // 1 - N
  user: one(user, { fields: [course.userId], references: [user.id] }),
  // M - N
  bookmarks: many(bookmark),
  chapters: many(chapter, { relationName: 'chaptersCourse' }),
  comments: many(comment),
  courseSubscriptions: many(courseSubscription),
  likes: many(like),
  notes: many(note),
  ratingsReviews: many(ratingsReview),
  videoLogs: many(videoLog),
  // M - M
  recommendations: many(recommendation)
}));


export const recommendation = pgTable(
  'Recommendation',
  {
    id: text('id').primaryKey().default(uuid()).notNull(),
    createdAt: timestamp('createdAt', { precision: 3, mode: 'string' }).defaultNow().notNull(),
    // updatedAt: timestamp('updatedAt', { precision: 3, mode: 'string' }).notNull(),
    tags: text('tags').default('{}').array(),
    userId: text('userId')
      .notNull()
      .references(() => user.id, cascade)
  },
  (table) => {
    return {
      userIdKey: uniqueIndex('Recommendation_userId_key').on(table.userId)
    };
  }
);

export const recommendationRelations = relations(recommendation, ({ one, many }) => ({
  user: one(user, { fields: [recommendation.userId], references: [user.id] }),
  courses: many(course),
  interests: many(interest)
}));

/**
 * Many to Many
 */

export const recommendationsToCourses = pgTable(
  'Recommendations_to_Courses',
  {
    recommendationId: text('recommendationId')
      .notNull()
      .references(() => recommendation.id, cascade),
    courseId: text('courseId')
      .notNull()
      .references(() => course.id, cascade)
  },
  (t) => ({ pk: primaryKey({ columns: [t.recommendationId, t.courseId] }) })
);

export const recommendationsToCoursesRelations = relations(recommendationsToCourses, ({ one }) => ({
  course: one(course, {
    fields: [recommendationsToCourses.courseId],
    references: [course.id]
  }),
  recommendation: one(recommendation, {
    fields: [recommendationsToCourses.recommendationId],
    references: [recommendation.id]
  })
}));

I tried adding a relationName with the name of the pivot table like this

// RecommendationRelations
  courses: many(course, { relationName: 'Recommendations_to_Courses' }),

// CourseRelations
  recommendations: many(recommendation, { relationName: 'Recommendations_to_Courses' })

// RecommendationRelations
  courses: many(course, { relationName: 'Recommendations_to_Courses' }),

// CourseRelations
  recommendations: many(recommendation, { relationName: 'Recommendations_to_Courses' })

But that did not change anything. Am I missing something ? I've seen similar questions previously asked, but none of the suggestions seems to apply for my specific case.

13 replies

DTDrizzle Team

•Created by Hebilicious on 12/23/2023 in #help

Advanced aggregations help

Here's a Drizzle query that I'm trying to do :

      const videoLogs = db
        .select({
          id: S.videoLog.id,
          chapterId: S.videoLog.chapterId,
          courseId: S.videoLog.courseId,
          watchedUntil: S.videoLog.watchedUntil,
          videoId: S.videoLog.videoId,
          completedVideos: sql`(
            select count(*)
            from ${S.vidStat}
            where ${S.vidStat.userId} = ${userId} 
            and ${S.vidStat.completedWatching} = true
            and ${S.vidStat.chapterId} = ${S.videoLog.chapterId}
          )`.mapWith(Number)
        })
        .from(S.videoLog)
        .where(eq(S.videoLog.userId, userId))
        .as('videoLogs');

      const videoLogs = db
        .select({
          id: S.videoLog.id,
          chapterId: S.videoLog.chapterId,
          courseId: S.videoLog.courseId,
          watchedUntil: S.videoLog.watchedUntil,
          videoId: S.videoLog.videoId,
          completedVideos: sql`(
            select count(*)
            from ${S.vidStat}
            where ${S.vidStat.userId} = ${userId} 
            and ${S.vidStat.completedWatching} = true
            and ${S.vidStat.chapterId} = ${S.videoLog.chapterId}
          )`.mapWith(Number)
        })
        .from(S.videoLog)
        .where(eq(S.videoLog.userId, userId))
        .as('videoLogs');

Unfortunately I'm getting a drizzle type error for the count one. What am I doing wrong ?

36 replies

DTDrizzle Team

•Created by Hebilicious on 12/21/2023 in #help

Type Helper to select fields

5 replies

Gaming

Programming