Coffee11 Posts - Answer Overflow

Coffee11

Explore posts from servers

CDCloudflare Developers KKysely DTDrizzle Team

•Created by Coffee11 on 8/6/2024 in #help

Mysql Geo index

export const property = mysqlTable(
  "properties",
  {
    id: int("id").primaryKey().autoincrement(),
    longitude: float("longitude").notNull(),
    latitude: float("latitude").notNull(),
    primaryImageUrl: varchar("primary_image_url", { length: 255 }),
    jsonData: json("json").$type<{ foo: string }>(),
    createdAt: timestamp("created_at", { mode: "string" }).defaultNow(),
  },
  (table) => ({
    latIndex: index("lat").on(table.latitude),
    longIndex: index("long").on(table.longitude),
  })
);

export const property = mysqlTable(
  "properties",
  {
    id: int("id").primaryKey().autoincrement(),
    longitude: float("longitude").notNull(),
    latitude: float("latitude").notNull(),
    primaryImageUrl: varchar("primary_image_url", { length: 255 }),
    jsonData: json("json").$type<{ foo: string }>(),
    createdAt: timestamp("created_at", { mode: "string" }).defaultNow(),
  },
  (table) => ({
    latIndex: index("lat").on(table.latitude),
    longIndex: index("long").on(table.longitude),
  })
);

this is what I generated

CREATE INDEX `lat` ON `properties` (`latitude`);--> statement-breakpoint
CREATE INDEX `long` ON `properties` (`longitude`);

CREATE INDEX `lat` ON `properties` (`latitude`);--> statement-breakpoint
CREATE INDEX `long` ON `properties` (`longitude`);

any idea how can I make drizzle generate this kind of index?

 alter table properties add index lat(latitude);
 alter table properties add index long(longitude);

 alter table properties add index lat(latitude);
 alter table properties add index long(longitude);

thank you

1 replies

CDCloudflare Developers

•Created by Coffee11 on 7/16/2024 in #pages-help

@cloudflare/puppeteer access?

I'm in a paid subscription, but I can't use @Cloudflare/puppeteer TypeError: Cannot read properties of undefined (reading 'fetch') I'm trying to take screenshot of a specific website.

5 replies

KKysely

•Created by Coffee11 on 9/28/2023 in #help

ts_rank in select

still not possible?

 .select([
          'finance_reimbursement_requests.reimbursement_request_id',
          'finance_reimbursement_requests.reference_no',
          'finance_reimbursement_request_types.request_type',
          'finance_reimbursement_expense_types.expense_type',
          'main_request_status.request_status as requestor_request_status',
          'hrbp_status.request_status as hrbp_request_status',
          'finance_status.request_status as finance_request_status',
          'finance_reimbursement_requests.amount',
          'finance_reimbursement_requests.attachment',
          'finance_reimbursement_requests.attachment_mask_name',
          'finance_reimbursement_requests.remarks',
          'users.full_name',
          'users.email',
          'users.employee_id',
          'users.client_name',
          'users.hrbp_approver_email',
          'users.payroll_account',
          'finance_reimbursement_requests.created_at',
          sql<number>`ts_rank(to_tsvector('english', users.email, websearch_to_tsquery('R2023-185')) as rank`,
        ])

 .select([
          'finance_reimbursement_requests.reimbursement_request_id',
          'finance_reimbursement_requests.reference_no',
          'finance_reimbursement_request_types.request_type',
          'finance_reimbursement_expense_types.expense_type',
          'main_request_status.request_status as requestor_request_status',
          'hrbp_status.request_status as hrbp_request_status',
          'finance_status.request_status as finance_request_status',
          'finance_reimbursement_requests.amount',
          'finance_reimbursement_requests.attachment',
          'finance_reimbursement_requests.attachment_mask_name',
          'finance_reimbursement_requests.remarks',
          'users.full_name',
          'users.email',
          'users.employee_id',
          'users.client_name',
          'users.hrbp_approver_email',
          'users.payroll_account',
          'finance_reimbursement_requests.created_at',
          sql<number>`ts_rank(to_tsvector('english', users.email, websearch_to_tsquery('R2023-185')) as rank`,
        ])

8 replies

KKysely

•Created by Coffee11 on 9/28/2023 in #help

How to convert this raw query to kysely?

UPDATE finance_reimbursement_approval_matrix
SET has_approved = true
WHERE approval_matrix_id = 'c78f11f0-7014-47cf-82b7-838a43b34b20'
AND reimbursement_request_id IN (
    SELECT reimbursement_request_id
    FROM finance_reimbursement_requests
    WHERE reimbursement_request_id = 'e1dafc7e-a54f-4888-95f1-8cb57f22dfba'
    AND request_status_id = '33bfa3b3-1dfa-471f-a7bb-80a9a8842fc5'
);

UPDATE finance_reimbursement_approval_matrix
SET has_approved = true
WHERE approval_matrix_id = 'c78f11f0-7014-47cf-82b7-838a43b34b20'
AND reimbursement_request_id IN (
    SELECT reimbursement_request_id
    FROM finance_reimbursement_requests
    WHERE reimbursement_request_id = 'e1dafc7e-a54f-4888-95f1-8cb57f22dfba'
    AND request_status_id = '33bfa3b3-1dfa-471f-a7bb-80a9a8842fc5'
);

as of now, this is what I have done.

await trx
      .updateTable('finance_reimbursement_approval_matrix')
      .set({
        has_approved: true,
        performed_by_user_id: user.original_user_id,
        updated_at: new Date(),
      })
      .where((eb) =>
        eb
          .selectFrom('finance_reimbursement_requests')
          .innerJoin(
            'finance_reimbursement_approval_matrix',
            'finance_reimbursement_approval_matrix.reimbursement_request_id',
            'finance_reimbursement_requests.reimbursement_request_id',
          )
          .where(
            'finance_reimbursement_approval_matrix.approval_matrix_id',
            '=',
            approval_matrix_id,
          )
           .where(
                'finance_reimbursement_requests.request_status_id',
                '=',
              PENDING_REQUEST)

await trx
      .updateTable('finance_reimbursement_approval_matrix')
      .set({
        has_approved: true,
        performed_by_user_id: user.original_user_id,
        updated_at: new Date(),
      })
      .where((eb) =>
        eb
          .selectFrom('finance_reimbursement_requests')
          .innerJoin(
            'finance_reimbursement_approval_matrix',
            'finance_reimbursement_approval_matrix.reimbursement_request_id',
            'finance_reimbursement_requests.reimbursement_request_id',
          )
          .where(
            'finance_reimbursement_approval_matrix.approval_matrix_id',
            '=',
            approval_matrix_id,
          )
           .where(
                'finance_reimbursement_requests.request_status_id',
                '=',
              PENDING_REQUEST)

7 replies

KKysely

•Created by Coffee11 on 9/26/2023 in #query-showcase

Select count?

Can't find any docs regarding this can someone send me the docs for this? Thank you in advance.

6 replies

KKysely

•Created by Coffee11 on 9/13/2023 in #help

Can this query translate to kysely?

sql`SELECT 
      frr.reimbursement_request_id,
      frr.reference_no,
      frrt.request_type,
      fret.expense_type,
      frrs.request_status,
      frr.amount,
      frr.attachment,
      frr.attachment_mask_name,
      frr.remarks,
      u.full_name,
      u.email,
      u.employee_id,
      u.hrbp_approver_email,
      frr.payroll_date,
      frr.date_approve,
      frr.created_at,
      frr.cursor_id::TEXT
      ${
        data?.text_search
          ? sql`
      ,ts_rank(to_tsvector('english', coalesce(frr.text_search_properties, '')), websearch_to_tsquery(${data.text_search})) AS rank`
          : sql``
      }  
    FROM finance_reimbursement_requests as
    WHERE to_tsvector('english', coalesce(frr.text_search_properties, '')) @@ websearch_to_tsquery(${data.text_search})
    ORDER BY rank DESC

sql`SELECT 
      frr.reimbursement_request_id,
      frr.reference_no,
      frrt.request_type,
      fret.expense_type,
      frrs.request_status,
      frr.amount,
      frr.attachment,
      frr.attachment_mask_name,
      frr.remarks,
      u.full_name,
      u.email,
      u.employee_id,
      u.hrbp_approver_email,
      frr.payroll_date,
      frr.date_approve,
      frr.created_at,
      frr.cursor_id::TEXT
      ${
        data?.text_search
          ? sql`
      ,ts_rank(to_tsvector('english', coalesce(frr.text_search_properties, '')), websearch_to_tsquery(${data.text_search})) AS rank`
          : sql``
      }  
    FROM finance_reimbursement_requests as
    WHERE to_tsvector('english', coalesce(frr.text_search_properties, '')) @@ websearch_to_tsquery(${data.text_search})
    ORDER BY rank DESC

4 replies

KKysely

•Created by Coffee11 on 9/6/2023 in #help

how to convert bigint to text in select statement?

In raw sql we do this cursor_id::text. How to perform this in kysely?

2 replies

Gaming

Programming