Kysely•9mo ago

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`,
        ])

Solution:

For future readers, when using sql template tag, or ExpressionBuilder within a select, you gotta alias it using the as method.

sql<number>`ts_rank(to_tsvector('english', users.email, websearch_to_tsquery('R2023-185'))`.as('rank'),

sql<number>`ts_rank(to_tsvector('english', users.email, websearch_to_tsquery('R2023-185'))`.as('rank'),

...

Jump to solution

4 Replies

koskimas•9mo ago

??? It's possible. But I don't feel like doing more of your work when you ask so nicely.

Coffee11•9mo ago

sorry man

Solution

Igal•9mo ago

For future readers, when using sql template tag, or ExpressionBuilder within a select, you gotta alias it using the as method.

sql<number>`ts_rank(to_tsvector('english', users.email, websearch_to_tsquery('R2023-185'))`.as('rank'),

sql<number>`ts_rank(to_tsvector('english', users.email, websearch_to_tsquery('R2023-185'))`.as('rank'),

Coffee11•9mo ago

Appreciate you @Igal and @koskimas sorry if the question sounds rude

Gaming

Programming

ts_rank in select