Bulk insert results of query

Questions / Help
1

I am new to Elixir and I am trying to figure out how to bulk insert records from the result of a query.
I have the following tables:

Users      UserList     List
------     ---------    ----
user_id      user_id    list_id
            list_id

I want to be able to query users, insert a List record, and insert a UserList record for each user.

users = from(
    u in User,               
    where: u.language in ^List.wrap(criteria.lang),
    select: struct(u, [:user_id])) # just return the id since I don't need the full user
|> all

I’ve tried the following code, but only 1 userlist record is inserted and it doesn’t map to the user from the query result.

insert! %List{
        user_list: %UserList{
            user: users
        }
    }

I also realize this is a naive way to take a resultset and insert it to another table. If my result is 1 million rows, this is potentially quite expensive. Should I look at streaming the result into UserList inserts? How would you solve this type of problem in the Elixir world.

2

In my opinion, that’s a problem better solved with SQL. Looks like you’d be better off just writing an insert query that takes the results of another query and totally bypassed the network transfer.

1 Like