Is there a way to define a filter (where clause) on a schema?

Phoenix Forum Questions / Help
,
1

The context of my question on elegant constants is that I’m using a rudimentary form of polymorphism in my application based on a type or part identifier for which i needed constants which affords me some protection against duplicate keys or values.

Now, it works really well for associations because I can declare a filter at schema level, i.e. add where cluases (e.g. where: [type: :post] to the association declarations (has_many, belongs_to, etc.) That reliably injects the required SQL to limit the associated data correctly whether I used queries such as join: y in associ(x, :posts) or I use preload in its query or Repo.preload forms. All brilliant.

But I’m left with having to remember to apply the filter manually to the first schema in a query, or use a series of functions or macros to generate it or use functions to build partial queries correctly. I’ve been doing that since previous versions of my application, but haven’t found one approach that works everywhere equally well. (For reference, Ingeneral I tend to lean away from too much magic towards explicit code rather than a prolifiration of utility functions which blurs the line separating concerns.)

The elegance of using the SimpleEnum constants solution with Ecto.Enum has made me wonder if there’s a trik I’m missing (or that Ecto might be missing).

Is there a way to define a filter (where clause) on a schema?

For the general purpose case using a single schema per table it might be of no value, but Ecto very elegantly and effectively allows for multiple schemas to store their data in the same table. It would make a load of sense if there is a way to declare that a schema refers to table “data” but only to records which passes a specified filter.

2

There is not, but let me propose not using the schema (name) in the first place. Most places where you use MySchema actually takes a Ecto.Queryable protocol implementation. The module name just happens to be one such implementation.

The docs show the implementations ecto comes with: Ecto.Queryable — Ecto v3.12.4

You could e.g. have MyQueryable.from(:post) and have it return a struct, which resolves to the correct Ecto.Query through the protocol or return the correct Ecto.Query as a result to the function. The former is a bit more flexible because you could have additional apis to interact with the queryable, which you can’t do with a Ecto.Query.

Unfortunately Atom, BitString and Tuple already have native implementations for the protocol, so you cannot use those types for your custom implementation, leaving either lists (charlists) or structs(/Map) as possible formats for the protocol.

1 Like
3

That sounds rather elegant and intriguing, but more involved than I can immediately picure in my mind what the code would look like. I do think it’s worth exploring, so can you maybe add a bit more guidance as to what that would look like in practice?

I’ve quickly thrown together some code with schemas as I would currently define them as a tangible example where I’d love to see how you’d apply your proposed approach.

4

If you only have one such enum this could work:

defimpl Ecto.Queryable, for: Integer do
  use MyApp.Part
  import Ecto.Query
  
  for {key, num} <- Part.no(:__enumerators__) do
    def to_query(unquote(num)) do
      from x in key_to_schema(unquote(key)), where: x.part == unquote(key)
    end
  end

  defp key_to_schema(key), do: …
end

# to query
use MyApp.Part

from c in Part.no(:comment)
1 Like
5

While at the moment I do only have one I cannot as it stands derive the correct schema from the number alone because that information simply isn’t in the metadata I carry at the moment.

I looked at creating a custom ecto enum type but it seems a little over the top.

What if I define my own protocol or behaviour (they’re for different purposes I know, but this one purpose seems to sit on the nexus of the behaviour are about modules and protocols are about data summation) for the schema modules based on the two underlying tables which then provides a function for each derived schema that in effect returns the appropriate “queryable”. Not that I know enough about queryables yet, but in principle. Something that I would use like:

use MyApp.Part
alias MyApp.Core.Post

from c in Post.filtered # my guess if using a protocol of sorts
from c in Part.query(Post) # my guess if using a behaviour of sorts

Does that strike you as a direction where I’m likely to find some elegance in keeping with the Elixir/Phoenix/Ecto idiom and principle of explicit code without too much magic?

My objective is not to obscure that I’m using the same tables for many schemas at all. I only want to avoid accidental ommition of the filter on the part value.

6

I tried my hand (in the repo code) at using Base and Link as behaviours. It allows me to write

from p in Base.part_filtered(Post)

or

from p in Post.query

It can probably be done more elegantly and idiomatically sound, possibly by wrapping Ecto.Schema in another layer extending the schema DSL to allow for the part value (or enum atom) to be specified in the definition and injecting the query function automatically.

7

You can also create a view on your table with that filter and then create a schema on that view.

8

Well, that exposes my age. Since I qualified as an Oracle DBA things have surely changed changed with views since Postgres 9.3 being automatically updatable under certain conditions. I’d have to see if the required view stays within the database’s limitations for being updatable.

I presume the views would need to be created in migration files, right?

It doesn’t quite scream elegance to me, but it probably is an option.

9

Not sure what your definition of elegance is. But it’s simple and idiomatic. Good luck to you on your quest for elegance :slight_smile:

10

I’m not dismissive about your proposal, just saying that since view management falls outside the scope of Ecto it feels like a pain to have to write each view create script. Also since the discriminator is an integer at the database level, not the Ecto.Enum the schema makes it, you’d have to bring things into the migration files that’s not usually there or deal with the value translation manually which is another point of failure.

I might be working off a different interpretation of idiomatic and elegance than you. For me it means “in keeping with the general spirit of Elixir, Phoenix and Ecto”, it’s declarative and semantically consistent. It just seems that views fall too far outside Ecto’s purview which could leave you up the creek without a paddle if for example the view you need is no longer updatable once you’ve added partitioning and/or tenancy with prefixes. Not saying it would or would be a problem, just that it leans too much on goldilocks conditions.

Thanks for your contribution. Don’t take it personally.