Question 279873 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to check if a column value is contained within a string

When a payment is received in my company’s banking account, a webhook is triggered to our application, providing data related to the payment, including the payment label.

This label is a string written by the client and can vary significantly from one instance to another. We typically provide a reference to the client to include in the label, allowing us to identify it. The reference pattern conforms to this regular expression: /A[a-zA-Z]{2,5}-?d{4}-?d{2}-?d{4}z/. For example: "ABCDE-2023-01-0001" or "ab-2023-01-0001" etc.

Additionally, I have an Invoice model with two columns: number and moneyin_key. The number value generally follows this format: ABCDE-2023-01-0001 and the moneyin_key value is the same but without the hyphens, like "ABCDE2023010001".

I want to create a scope to retrieve all the invoices related to the label provided by the client. As mentioned, the label can vary widely, but here’s an example: "VIR. O/ JUMBO VISMA MOTIF: EDP2023100001" My scope should be structured as follows:

scope :related_to_payment_label, -> (label) { do_something_here }

I’ve tried several approaches, but none of them have been successful. Example:

scope :related_to_payment_label, -> (label) {
  where("number ILIKE :label OR moneyin_key ILIKE :label", label: "%#{label}%")
}

Any ideas?

Tags:

2

Answers


  1. 0

    You can use REGEXP function here like this:

    scope :related_to_payment_label, -> {
  where("number REGEXP :regexp OR moneyin_key REGEXP :regexp", regexp: '[a-zA-Z]{2,5}-?d{4}-?d{2}-?d{4}z')
}

    Or you can use ~* for case insensitive regexp and ~ for case sensitive if your database is Postgres

    For instance:

    scope :related_to_payment_label, -> {
  where("number ~* :regexp OR moneyin_key ~* :regexp", regexp: '[a-zA-Z]{2,5}-?d{4}-?d{2}-?d{4}z')
}
    Login or Signup to reply.
  2. 0

    You can use a custom SQL query in your scope. You need to extract the reference pattern from the payment label and then use it to search for related invoices. Here’s how you can do it:

    scope :related_to_payment_label, -> (label) {
  reference = label.match(/b[a-zA-Z]{2,5}-?d{4}-?d{2}-?d{4}b/).to_s

  where("number = :reference OR moneyin_key = :reference", reference: reference)
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search