Is it possible to merge two separate ecto queries?

Question

I am wondering if it is possible to combine two queries together in ecto.

I would like to create a function which takes an ecto query and modifies it depending on the case.

For example, let's imagine we have a users table that allows duplicate usernames to be entered and a user creates the following query...

query = from(u in "users", select: u)
select_all_unique_users(query)

I would like the select_all_unique_users function to take query and add to it, so that it only selects the distinct usernames.

this is only an example, I know that I could create a unique_index on the table to avoid this in reality =D

As query is a struct under the hood, I figured I could update the distinct key do this. This seems to work, however the downside is that it doesn't seem very flexible and looks like it will be hard to implement if I ever want to add a more complicated logic.

Simply put, what I would like to do is create a function that takes a query and adds some more logic to it...

iex()> query = from(u in "users", select: u)
#Ecto.Query<from u0 in "users", select: u0>

iex()> select_all_unique_users(query)
#Ecto.Query<from u0 in "users", distinct: [asc: u0.username], select: u0>

Mooney

Answer 1

The select_all_unique_users function would be implemented like this:

defmodule TestModule do
  def select_all_unique_users(q) do
    from u in q, distinct: u.username
  end
end

And you can use it like below:

iex(2)> query = from(u in "users", select: u)
#Ecto.Query<from u in "users", select: u>

iex(3)> distinct = TestModule.select_all_unique_users(query)
#Ecto.Query<from u in "users", distinct: [asc: u.username], select: u>

I wrapped select_all_unique_users in a module I created for the purpose of this answer, but you can place it in any module you see fit.

Typo

Answer 2

Sure you can!

You could go about it like this, supposing you have a select_all_unique_users/1 function like so:

def select_all_unique_users(query) do
  from(u in query, distinct: u.username)
end

should do the trick. So, you could then do:

# using a defined User schema is preferred over "users". Using
# "users" might cause an error for not being able to sellect all fields
# from table without a schema

iex> q = from(u in User, select: u)
#Ecto.Query<from u in User, select: u>
iex> select_all_unique_users(q)
#Ecto.Query<from u in User, distinct: [asc: u.username], select: u>

Notice that the query from select_all_unique_users/1 doesn't have another select statement. It's because only one select expression is allowed. So, if you wanted to select different attributes on your different queries, you might want to add that in select_all_unique_users/1 or any other functions you may have that base on the from(u in User)

Riboflavin