How to add external data in select

I want to add external data in select, but it only works with sql.

this.drizzle.get({
  from: users,
  select: {
    id: users.id,
    username: users.username,
    name: users.name,
    surname: users.surname,
    email: users.email,
    job: users.job,
    phone: users.phone,
    avatar: users.avatar,
    last_seen: users.lastSeen,
    organization_id: users.organizationId,
    otp_enabled: users.otpEnabled,
    is_webauth: users.isWebauth,
  },
  page: query.page,
  perPage: query.per_page,
})
.where(GetUsersWhere(query))
.orderBy(desc(users.lastSeen))
.execute()
// My solution 
.then(async (users) => {
  return await Promise.all(
      users.map(
        async (user: { organization_id: number }) => {
          const jobs = await this.orgService.nestedUserJobs(user.organization_id);
          return {
            ...user,
            jobs
          }
   }))
});

this.drizzle.get({
  from: users,
  select: {
    id: users.id,
    username: users.username,
    name: users.name,
    surname: users.surname,
    email: users.email,
    job: users.job,
    phone: users.phone,
    avatar: users.avatar,
    last_seen: users.lastSeen,
    organization_id: users.organizationId,
    otp_enabled: users.otpEnabled,
    is_webauth: users.isWebauth,
  },
  page: query.page,
  perPage: query.per_page,
})
.where(GetUsersWhere(query))
.orderBy(desc(users.lastSeen))
.execute()
// My solution 
.then(async (users) => {
  return await Promise.all(
      users.map(
        async (user: { organization_id: number }) => {
          const jobs = await this.orgService.nestedUserJobs(user.organization_id);
          return {
            ...user,
            jobs
          }
   }))
});

is that way has to do like

{
  select: {
    jobs: (user) => await this.orgService.nestedUserJobs(user.organization_id)
  }
}

{
  select: {
    jobs: (user) => await this.orgService.nestedUserJobs(user.organization_id)
  }
}

9 Replies

Mykhailo•9mo ago

Hello, @Suleyman! Could you provide you schemas code and what this.orgService.nestedUserJobs is for? As I understand from your code, you want to get jobs by user.organizationId, so probably you can use joins.

Suleyman•9mo ago

Hello @solo , My question is is that way has to define data without sql or schema

db.select({
  id: users.id,
  isOnline: true, // <- without sql``
})
.from(users);

db.select({
  id: users.id,
  isOnline: true, // <- without sql``
})
.from(users);

like this

Angelelz•9mo ago

You can't do it without sql because it's like you're trying to select a column called true or being true? 🤷‍♂️ For what seems to be your objective, this is a workaround:

db.select({
  id: users.id,
  isOnline: sql`true`.as("isOnline"),
})
.from(users);

db.select({
  id: users.id,
  isOnline: sql`true`.as("isOnline"),
})
.from(users);

And this will translate into this query:

SELECT id, true as isOnline from users;

SELECT id, true as isOnline from users;

Which I believe should be valid sql depending on your dialect.

Suleyman•9mo ago

ok, how to solve this problem

      const [action] = await this.drizzle.db
        .select(
          {
            id: encode(tasks.id) as string,
            body: tasks.body,
            created_at: tasks.createdAt,
            finishes_at: tasks.finishesAt,
            is_read: tasks.isRead,
            mail_id: tasks.mailId,
            read_at: tasks.readAt,
            recipient: {
              id: users.id,
              username: users.username,
              avatar: users.avatar,
              name: users.name,
              surname: users.surname,
            },
            recipient_id: tasks.recipientId,
            sender_id: tasks.senderId,
            updated_at: tasks.updatedAt,
            uid: users.id
          }
        )
        .from(tasks)
        .innerJoin(users, eq(tasks.recipientId, users.id))
        .where(eq(tasks.id, request.id));

      const [action] = await this.drizzle.db
        .select(
          {
            id: encode(tasks.id) as string,
            body: tasks.body,
            created_at: tasks.createdAt,
            finishes_at: tasks.finishesAt,
            is_read: tasks.isRead,
            mail_id: tasks.mailId,
            read_at: tasks.readAt,
            recipient: {
              id: users.id,
              username: users.username,
              avatar: users.avatar,
              name: users.name,
              surname: users.surname,
            },
            recipient_id: tasks.recipientId,
            sender_id: tasks.senderId,
            updated_at: tasks.updatedAt,
            uid: users.id
          }
        )
        .from(tasks)
        .innerJoin(users, eq(tasks.recipientId, users.id))
        .where(eq(tasks.id, request.id));

full code

Angelelz•9mo ago

Is encode an SQL function? In that case you need to do:

id: sql`encode(${tasks.id}) as string`,

id: sql`encode(${tasks.id}) as string`,

Suleyman•9mo ago

No, its and just javascript function

Angelelz•9mo ago

In that case, this is how you do it:

id: sql`${tasks.id}`.mapWith(encode)

id: sql`${tasks.id}`.mapWith(encode)

Suleyman•9mo ago

Thanks @Angelelz! I worked finally Thanks a lot! @Angelelz I'm sorry, but i have a question again.

{ 
prepared_by: sql`mails.organization_prepared_by`.mapWith(async function (value) {
    return {
      id: value,
    }
}) as unknown as SQL<{
   id: string,
}>,

{ 
prepared_by: sql`mails.organization_prepared_by`.mapWith(async function (value) {
    return {
      id: value,
    }
}) as unknown as SQL<{
   id: string,
}>,

sometimes i need to call async function for relations, but sql doesn't support async. is that have solution?

Angelelz•9mo ago

This could be a solution but prepared_by now holds a promise. You'll need to await just that key Also the type casting should be as unknown as Promise<string> I think I've never tried this, so I don't really know I'm just guessing

Gaming

Programming

How to add external data in select