piscopancer Posts - Answer Overflow

piscopancer

Explore posts from servers

•Created by piscopancer on 6/4/2024 in #help

need postgres explanation

do I have to start my own server for my postgres database with user and password and run in alongside my next app to be able to connect to it with

import * as courses from '@/db/schema/courses'
import * as studentsCourses from '@/db/schema/courses-students'
import * as users from '@/db/schema/users'
import { drizzle } from 'drizzle-orm/node-postgres'
import { Client } from 'pg'

const client = new Client({
  connectionString: `postgres://user:password@host:port/db`,
})

export const db = drizzle(client, {
  schema: {
    ...users,
    ...courses,
    ...studentsCourses,
  },
})

import * as courses from '@/db/schema/courses'
import * as studentsCourses from '@/db/schema/courses-students'
import * as users from '@/db/schema/users'
import { drizzle } from 'drizzle-orm/node-postgres'
import { Client } from 'pg'

const client = new Client({
  connectionString: `postgres://user:password@host:port/db`,
})

export const db = drizzle(client, {
  schema: {
    ...users,
    ...courses,
    ...studentsCourses,
  },
})

3 replies

DTDrizzle Team

•Created by piscopancer on 3/26/2024 in #help

how to replicate **joins** in **db.query**?

this is a db.select query with a join to filter out only answers that are accepted

const queryAcceptedAnswers = (userId: number)=>
  db.select()
  .from(answersTable)
  .where(eq(answersTable.authorId, userId))
  .innerJoin(questionsTable, eq(answersTable.id, questionsTable.acceptedAnswerId))

const queryAcceptedAnswers = (userId: number)=>
  db.select()
  .from(answersTable)
  .where(eq(answersTable.authorId, userId))
  .innerJoin(questionsTable, eq(answersTable.id, questionsTable.acceptedAnswerId))

let's say, there may be 2 accepted answers, thus this will be the response

"acceptedAnswers": [
    {
      "answers": {
        "id": 8,
        "content": "🤡🤡🤡",
        "authorId": 109352196,
        "questionId": 7,
        "createdAt": "2024-03-23T13:14:22.690Z",
        "useless": false
      },
      "questions": {
        "id": 7,
        "title": "какыфмыфвфывс",
        "content": "фывфывыфвывс",
        "authorId": 109352196,
        "createdAt": "2024-03-22T11:59:37.434Z",
        "category": "отношения",
        "acceptedAnswerId": 8
      }
    },
    {
      "answers": {
        "id": 6,
        "content": "aaa",
        "authorId": 109352196,
        "questionId": 8,
        "createdAt": "2024-03-23T11:46:38.130Z",
        "useless": false
      },
      "questions": {
        "id": 8,
        "title": "bruh",
        "content": "bruh222",
        "authorId": 109352196,
        "createdAt": "2024-03-22T11:59:37.992Z",
        "category": "отношения",
        "acceptedAnswerId": 6
      }
    }
  ]

"acceptedAnswers": [
    {
      "answers": {
        "id": 8,
        "content": "🤡🤡🤡",
        "authorId": 109352196,
        "questionId": 7,
        "createdAt": "2024-03-23T13:14:22.690Z",
        "useless": false
      },
      "questions": {
        "id": 7,
        "title": "какыфмыфвфывс",
        "content": "фывфывыфвывс",
        "authorId": 109352196,
        "createdAt": "2024-03-22T11:59:37.434Z",
        "category": "отношения",
        "acceptedAnswerId": 8
      }
    },
    {
      "answers": {
        "id": 6,
        "content": "aaa",
        "authorId": 109352196,
        "questionId": 8,
        "createdAt": "2024-03-23T11:46:38.130Z",
        "useless": false
      },
      "questions": {
        "id": 8,
        "title": "bruh",
        "content": "bruh222",
        "authorId": 109352196,
        "createdAt": "2024-03-22T11:59:37.992Z",
        "category": "отношения",
        "acceptedAnswerId": 6
      }
    }
  ]

How do I use joins in db.query api that will result in the same response or will filter out users accepted answers in a similar way? is it even possible with db.query? do i have to manage my relations in the schema?

2 replies

DTDrizzle Team

•Created by piscopancer on 3/25/2024 in #help

count() for join tables in query API

Usage I have a user page where you can see how many of their answers were considered correct (a person who asked a question accepted them, like on github). 3 tables are used: * users * questions * answers each answer has a column authorId which refers user.id, each question has acceptedAnswerId which refers answer.id

export function queryUser({ userId }: { userId: number }) {
  return db.query.usersTable.findFirst({
    where: (t, { eq }) => eq(t.id, userId),
    with: {
      questions: {
        with: {
          answers: {
            columns: {
              id: true,
              authorId: true,
            },
          },
          usersLikes: {
            columns: {
              questionId: true,
              userId: true,
            },
          },
          usersViews: {
            columns: {
              questionId: true,
            },
          },
        },
      },
    },
    extras: (t) => ({
      // a number of "answers" (table, see above) whose "authorId" === t.id 
      // should return an integer 0/1/2/3/4/5/...
      acceptedAnswers: count(t.id).as('accepted_answers'),
    }),
  })
}

export function queryUser({ userId }: { userId: number }) {
  return db.query.usersTable.findFirst({
    where: (t, { eq }) => eq(t.id, userId),
    with: {
      questions: {
        with: {
          answers: {
            columns: {
              id: true,
              authorId: true,
            },
          },
          usersLikes: {
            columns: {
              questionId: true,
              userId: true,
            },
          },
          usersViews: {
            columns: {
              questionId: true,
            },
          },
        },
      },
    },
    extras: (t) => ({
      // a number of "answers" (table, see above) whose "authorId" === t.id 
      // should return an integer 0/1/2/3/4/5/...
      acceptedAnswers: count(t.id).as('accepted_answers'),
    }),
  })
}

Draw your attention to the extras, what should be written there? I am not quite following

21 replies

DTDrizzle Team

•Created by piscopancer on 3/22/2024 in #help

"You're about to add not-null useless column without default value, which contains 1 items"

6 replies

DTDrizzle Team

•Created by piscopancer on 3/18/2024 in #help

many-to-many foreign key constraint 787 issue

4 replies

DTDrizzle Team

•Created by piscopancer on 3/16/2024 in #help

use `count` in db.query?

export function queryQuestions() {
  return db.query.questionsTable.findMany({
    limit: 5,
    orderBy: ({ createdAt }, { desc }) => desc(createdAt),
    with: {
      author: true,
      answers: {
        extras: //
      } 
    },
  })
}

export function queryQuestions() {
  return db.query.questionsTable.findMany({
    limit: 5,
    orderBy: ({ createdAt }, { desc }) => desc(createdAt),
    with: {
      author: true,
      answers: {
        extras: //
      } 
    },
  })
}

I am not fully understanding the API, require support

4 replies

DTDrizzle Team

•Created by piscopancer on 3/16/2024 in #help

2 tables cannot reference each others columns?

I have the following tables declaration:

export const questionsTable = sqliteTable('questions', {
  // bla bla
  acceptedAnswerId: text('accepted_answer_id').references(() => answersTable.id, {onDelete: 'cascade'}), // ERROR HERE
})

export const answersTable = sqliteTable('answers', {
  // bla bla
  questionId: text('question_id')
    .notNull()
    .references(() => questionsTable.id, { onDelete: 'cascade' }), // THIS REFERENCE DOES NOT LET ME CREATE A REFERENCE TO answersTable.id IN questionsTable.acceptedAnswerId, LOOK ABOVE
})

export const questionsTable = sqliteTable('questions', {
  // bla bla
  acceptedAnswerId: text('accepted_answer_id').references(() => answersTable.id, {onDelete: 'cascade'}), // ERROR HERE
})

export const answersTable = sqliteTable('answers', {
  // bla bla
  questionId: text('question_id')
    .notNull()
    .references(() => questionsTable.id, { onDelete: 'cascade' }), // THIS REFERENCE DOES NOT LET ME CREATE A REFERENCE TO answersTable.id IN questionsTable.acceptedAnswerId, LOOK ABOVE
})

As you can see, for some reason I CANNOT reference one table's column from the other table, while this table's column is already referenced by the other table. Can you explain why so and what should I do?

12 replies

DTDrizzle Team

•Created by piscopancer on 3/11/2024 in #help

I do not understand relations 😟 (many to many, users can follow and have followers)

export const usersTable = sqliteTable('users', {
  id: text('id').primaryKey(),
  username: text('username'),
})

export const usersRelations = relations(usersTable, ({ many }) => ({
  questions: many(questionsTable),
  followers: many(usersTable, { relationName: 'followers' }),
  following: many(usersTable, { relationName: 'following' }),
}))

export const usersToUsersTable = sqliteTable('users_to_users', {
  followedId: text('followed_id')
    .notNull()
    .references(() => usersTable.id),
  followerId: text('follower_id')
    .notNull()
    .references(() => usersTable.id),
})

export const usersToUsersRelations = relations(usersToUsersTable, ({ one }) => ({
  followed: one(usersTable, {
    fields: [usersToUsersTable.followedId],
    references: [usersTable.id],
    relationName: 'followers',
  }),
  follower: one(usersTable, {
    fields: [usersToUsersTable.followerId],
    references: [usersTable.id],
    relationName: 'following',
  }),
}))

export const usersTable = sqliteTable('users', {
  id: text('id').primaryKey(),
  username: text('username'),
})

export const usersRelations = relations(usersTable, ({ many }) => ({
  questions: many(questionsTable),
  followers: many(usersTable, { relationName: 'followers' }),
  following: many(usersTable, { relationName: 'following' }),
}))

export const usersToUsersTable = sqliteTable('users_to_users', {
  followedId: text('followed_id')
    .notNull()
    .references(() => usersTable.id),
  followerId: text('follower_id')
    .notNull()
    .references(() => usersTable.id),
})

export const usersToUsersRelations = relations(usersToUsersTable, ({ one }) => ({
  followed: one(usersTable, {
    fields: [usersToUsersTable.followedId],
    references: [usersTable.id],
    relationName: 'followers',
  }),
  follower: one(usersTable, {
    fields: [usersToUsersTable.followerId],
    references: [usersTable.id],
    relationName: 'following',
  }),
}))

this is the code I tried but I messed up the relations and the error says the following:

  throw new Error(
        ^

Error: There is not enough information to infer relation "__public__.usersTable.followers"
    at normalizeRelation (C:\dev\web\next\selowafelnoe\node_modules\drizzle-orm\relations.cjs:261:9)
    at C:\dev\web\next\selowafelnoe\node_modules\@drizzle-team\studio\index.js:73:67
    at Array.map (<anonymous>)
    at C:\dev\web\next\selowafelnoe\node_modules\@drizzle-team\studio\index.js:72:42
    at Array.map (<anonymous>)
    at extractRelations (C:\dev\web\next\selowafelnoe\node_modules\@drizzle-team\studio\index.js:71:51)
    at sqliteQueryEngine (C:\dev\web\next\selowafelnoe\node_modules\@drizzle-team\studio\index.js:293:16)
    at queryEngineForSetup (C:\dev\web\next\selowafelnoe\node_modules\@drizzle-team\studio\index.js:716:12)
    at Command.<anonymous> (C:\dev\web\next\selowafelnoe\node_modules\drizzle-kit\bin.cjs:63755:62)

  throw new Error(
        ^

Error: There is not enough information to infer relation "__public__.usersTable.followers"
    at normalizeRelation (C:\dev\web\next\selowafelnoe\node_modules\drizzle-orm\relations.cjs:261:9)
    at C:\dev\web\next\selowafelnoe\node_modules\@drizzle-team\studio\index.js:73:67
    at Array.map (<anonymous>)
    at C:\dev\web\next\selowafelnoe\node_modules\@drizzle-team\studio\index.js:72:42
    at Array.map (<anonymous>)
    at extractRelations (C:\dev\web\next\selowafelnoe\node_modules\@drizzle-team\studio\index.js:71:51)
    at sqliteQueryEngine (C:\dev\web\next\selowafelnoe\node_modules\@drizzle-team\studio\index.js:293:16)
    at queryEngineForSetup (C:\dev\web\next\selowafelnoe\node_modules\@drizzle-team\studio\index.js:716:12)
    at Command.<anonymous> (C:\dev\web\next\selowafelnoe\node_modules\drizzle-kit\bin.cjs:63755:62)

2 replies

Gaming

Programming