SQL Query to Substitute value in the scanned results and update that field of Table

Questions / Help
, ,
1

I have a 1 to many Organization: Users relationship.
I want to fetch the usernames of all User model of an Organization, capture a part of that username and append/substitute it with new value.

Here is how I am doing:

  1. Form the raw SQL to Get the matching usernames and replace them with new value.

raw = "SELECT REGEXP_REPLACE($1::string[], '(^[a-z0-9]+)((\s[a-z0-9]+))*\@([a-z0-9]+)$', m.name[1] || '@' || $2) FROM (SELECT REGEXP_MATCHES($1::string[], '(^[a-z0-9]+)((\s[a-z0-9]+))*\@([a-z0-9]+)$') AS name) m"

  1. Get the matching usernames and replace them with new value.
    usernames: list of usernames retrieved from queryable

Repo.query(raw, [usernames, a_string])

  1. Error I am getting

    SELECT REGEXP_REPLACE($1::string[], ‘(^[a-z0-9]+)(( [a-z0-9]+))@([a-z0-9]+)$’, m.name[1] || ‘@’ || $2) FROM (SELECT REGEXP_MATCHES($1::string[], '(^[a-z0-9]+)(( [a-z0-9]+))@([a-z0-9]+)$’) AS name) m [[“tradeboox@trdbx18”], “trdbx17”]
    {:error,
    %Postgrex.Error{connection_id: 7222, message: nil,
    postgres: %{code: :undefined_object, file: “parse_type.c”, line: “257”,
    message: “type “string[]” does not exist”, pg_code: “42704”,
    position: “137”, routine: “typenameType”, severity: “ERROR”,
    unknown: “ERROR”}}}

FYI: The username field of User model is of type citext

  1. Once I get the replaced values, I want to update the User with something like

update([u], set: [username: new_values])

Any ideas on how to proceed with this.

`

1 Like
2

Here is the solution:

from(u in User,
    join: o in assoc(u, :organization),
    where: u.organization_id == ^organization_id and o.id == ^organization_id,
    update: [set: [updated_at: ^time, inserted_at: ^time,
    username: fragment("concat(split_part(?, '@', 1), '@', ?)", u.username, ^replaced_text)]])
    |> Repo.update_all([], returning: true)
2 Likes