QueryBuilder - Compose Ecto queries without effort

QueryBuilder v1.0.0 has been released!

New features:

• add the Extension module allowing to easily extend QueryBuilder with user’s own functions
• add support for offset and limit operations
• differentiate field names vs an atom value with the syntax :<field_name>@self vs :<atom_value>

Thanks to @onomated that did all of the work, of high quality!

Because QueryBuilder is considered stable and there have been breaking changes, it has been bumped to 1.0.

Breaking changes:

.1.
QueryBuilder.order_by(User, lastname: :asc) becomes
QueryBuilder.order_by(User, asc: :lastname)

.2.
QueryBuilder.where(User, {:name, :eq, :nickname}) becomes
QueryBuilder.where(User, {:name, :eq, :nickname@self})
(concerns a comparison of two fields of the root schema)

Ah, don’t know why I didn’t grok the extension approach you suggested at first. I like it! That should work well. I’ll give it a shot after we work out the resolution to Atom values in where args treated as schema fields · Issue #2 · mathieuprog/query_builder · GitHub

I thought rather something like:

defmodule MyApp.QueryBuilder do
  use QueryBuilder.Extension # inject defdelegate and from_list

  def search(query, field, search_term) do
    # code
  end
end

and in QueryBuilder.Extension have __using__ injecting the defdelegates and from_list.

Except if I’m missing something

Note that it really is for the from_list function, otherwise I would have suggested to not extend QueryBuilder at all with macros, and just do something like:

User
|> QueryBuilder.where(firstname: "John")
|> QueryBuilderExt.search(...)

However the user might want to use from_list with the new functionality.

I prefer reflection because I think it’s not expensive at all, as I think the values from these functions for reflection are calculated at compile time.

Ah, so the thought is allow clients create an extension module that are passed in to QueryBuilder like so?

use QueryBuilder, extension: MyApp.QueryBuilder.SearchFunction

MyApp.QueryBuilder

defmacro __using__(opts) do
    extension = Keyword.get(opts, :extension, QueryBuilder.NullExtension)
    # confirm its a module with __using__
    quote do
      require QueryBuilder.Schema
      QueryBuilder.Schema.__using__(unquote(opts))
    
      require unquote(extension)
      unquote(extension).__using__(unquote(opts))
    end
  end

The clients can use just query builder as currently documented. No defdelegates required and no from_list overrides needed. Its worth a shot, though injecting macros can be complex if the extension macros have other macro operations such as setting module attributes which my extension module does. It’s worth a try to see how that plays out in my case.

That’s one approach, or introducing :self as a sentinel for the current schema to resolve to a field. So:

User
|> QueryBuilder.where([auth_method: :email@self, auth_val: "someone@example.com"])
|> Repo.one!()

Resolves to the current functionality as today, and:

User
|> QueryBuilder.where([auth_method: :email, auth_val: "someone@example.com"])
|> Repo.one!()

works like Ecto does today, while modeling the current association syntax you have.

Thoughts? Either way, it’s worth creating issues, and I can take a stab at working on the functionality suggested in a new branch or just fork the repo and PR back in after implementing. Definitely need to address the atom field referencing to use this lib

The idea is to simply inject all those defdelegates and the from_list function into the custom module. Except if I missed something? use allows you to inject code.

Nice, obviously I didn’t think about that case:) I think it’s easily solved with some reflection:

As a note to self, I think I’d also like at some point to implement select, because QueryBuilder is limited to fetching full entities right now.

Interesting. Can you highlight a bit more about what you’re thinking here? This is a pattern I haven’t seen. Not as much of a power user of Elixir just yet, so it’ll be an interesting learning experience.

Great point. Yes this does align with the Ecto Query API arg scheme.

One issue I have run into is that QueryBuilder assumes atom values are fields. So queries like this:

args_collected_somewhere = [auth_method: :email, auth_val: "someone@example.com"]
User
|> QueryBuilder.where(args_collected_somewhere)
|> Repo.one!()

is resolving to the following sql and error:

SELECT a0.* FROM "users" AS a0 WHERE ((a0."auth_val" = $1) AND (a0."auth_method" = a0."email"))

** (Postgrex.Error) ERROR 42703 (undefined_column) column a0.email does not exist

This is problematic as I use enums (with GitHub - gjaldon/ecto_enum: Ecto extension to support enums in models · GitHub) somewhat extensively in my data model, so attempting to “know enum fields” and programmatically convert them to strings first will be tedious. Is there a way to be explicit about values actually being db columns and only then should they be auto-binded as fields? I can file an issue if there is no workaround for this at the moment. This was caught in my regression tests as I’m trying to incorporate QueryBuilder.to_list where this worked fine prior:

args_collected_somewhere = [auth_method: :email, auth_val: "someone@example.com"]
User
|> Repo.get_by!(args_collected_somewhere)

It’s a good idea to offer a module to allow extension! I don’t see how a behaviour would help here as you want to extend the QueryBuilder module with your own custom functions with arbitrary function names.
What we could offer though is some code injection: use QueryBuilder.Extension and this will allow to inject the from_list as well to accept the new custom functions.
If you want to change your codebase already you could make a PR, or you can just wait until I’ll get back into it:)

The lib is in alpha so I’m allowed to break it? But what I will do I think is make a stable version 1.x and it won’t bother anyone. Having e.g. asc: :lastname also follows the Ecto Query API option.

@mathieuprog Tested out the custom sql functionality for order_bys and it works great. I’ve updated the gist above with my QueryBuilder extension module that utilizes dynamic bindings in where and order_by methods. Looking forward to the next release with these features.

Thanks once again for supporting the ask. Cheers!

@mathieuprog, here’s a gist where I started expanding the QueryBuilder functionality:

It’s currently work in progress as I haven’t added the necessary logic to utilize your changes to order_by in your master branch. Maybe making QueryBuilder a behavior with default implementations will make extending a lot more straightforward i.e. remove the need to defdelegate functions.

An option to avoid breaking your order_by interface would be to support a map of the order by spec, and just check if its a keyword list or map. So supporting something along the lines of:

QueryBuilder.order_by(User, %{direction: :asc, field: :last_name})

Just a thought.

I’ll expand my search functionality to also utilize your current changes for order_by as well and update the gist afterwards

Thanks @mathieuprog! That’s great. I’ll try out the master branch and share my implementation with you.