Coffee11 Posts - Answer Overflow

Coffee11

Explore posts from servers

CDCloudflare Developers KKysely DTDrizzle Team

•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