Support fields from associations

[maxmarcon]

Hi!

This looks like a cool library. Thank you so much for writing it! I think this is something that is badly needed in Elixirland. I was planning to use it until I (alas!) found a show stopper in my use case scenario.

I have a schema with associations, and I would like to show the main schema fields along with some fields from the associations, and make them searchable and sortable.

For example:

schema "user"do 
   field(:name, :string)

   belongs_to(:organization, Organizaton)
end

schema "organization" do 
   field(:name, :string)
end

In my Exzeitable table, I'd like to show both the user's name as well as their organization's names. I was able to make it work with this:

use Exzeitable,
    repo: DataIngestion.Repo,
    routes: Routes,
    query:
      from(u in User
        preload: [:organization]
      ),
    fields: [
      name: [label: "User name"],
      org_name: [function: true, label: "Organizaiton Name"]
   ]

def org_name(_socket, entry) do
   entry.organization.name
end

This allows me to display the organization's name just fine 👍 However, the field can't neither be sorted or searched because Exzeitable generates the search and sort queries assuming that org_name is a field of the user schema 👎

It seems to me that some support for associations would be in order here. I haven't thought carefully about the details, but maybe it would be enough to implement an association option for fields. Something like:

query: (from u in User),
fields: [
      name: [label: "User name"],
      org_name: [association: {:organization, :name},  label: "Organizaiton Name"]
   ]

That way, Exzeitable would know that it would have to preload the organization association and use its name field as the value of the org_name field in the table. It might work, but as I said, I haven't thought it through.

Anyway, let me know what you think.

[alanvardy]

Hi @maxmarcon and thank you for the issue!

I am currently using full text search if you had a user table with first name and last name the generated query would look something like this for search (I searched for "siob")

SELECT count(u0."id") 
  FROM "users" AS u0 
  WHERE (
    to_tsvector('english', coalesce(first_name, ' ') || ' ' || coalesce(last_name, ' ') ) @@ to_tsquery('siob:*')
  ) 

The search part of the query that I use is constructed here in the do_search function/2:

https://github.com/alanvardy/exzeitable/blob/4bab00ea488ab9f1bbd94dc8fd90c676b88c537c/lib/exzeitable.ex#L174

What I am thinking is that we could add an optional module/template option for :seach_query and which gives the developer an escape hatch to define their own query for searching. I would prefer to keep the String.replace(text, ~r/[^\w\s]|_/u, "") in the loop regardless in order to prevent SQL injection attacks.

What do you think @maxmarcon ? Do you have the bandwidth to work on a PR?

[maxmarcon]

Hi @alanvardy and thanks for the response.

I like your idea, it's quite flexible and also in line with how other functionalities can currently be customized by the dev.

One comment: we will also need to provide an "escape hatch" for sorting. Right now the name of the column is used directly in the query:

https://github.com/alanvardy/exzeitable/blob/4bab00ea488ab9f1bbd94dc8fd90c676b88c537c/lib/exzeitable.ex#L112

I don't have the bandwidth for a PR right now, but I might find the time in a few weeks. Stay tuned.