ruby-on-railsrubypostgresqlmodelscope

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?


Solution

  • 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)
    }