Ecto Assoc with a Composite Foreign Key

Questions / Help
1

I have a schema with a composite foreign key which looks like this,

has_many :name_a, AllIAsk.Model, references: :a, foreign_key: :a
has_many :name_b, AllIAsk.Model, references: :b, foreign_key: :b:

with a normal foreign key, I could use Ecto.assoc, like this,

model |> ( Ecto.assoc :name_normal_fk ) |> AllIAsk.Repo.one

What’s the best way to do this with a composite foreign key though?

2

Hoping @michalmuskala can take a look, perhaps I am missing something simple, or not describing this problem well enough…

3

Right now ecto does not support compound foreign keys when working with associations. This is something that needs to be done manually.

4

Ah, OK, good to know!

5

This is my stab. If I can figure out how to get the fields / keys for each assoc it would be a bit better…

 defp composite_fk_assoc(db_struct, assoc_names, keys), do:
    db_struct.__struct__
    |> fn m ->
      assoc_names
      |> (Enum.reduce m,
                      fn(c, a) ->
                        a
                        |> (join :inner, [s], p in (assoc s, ^c))
                      end
        )
      end.()
    |> fn m ->
      keys
      |> (Enum.reduce m,
                     fn(c, a) ->
                       a
                       |> (where [s], ^(map_take_to_list db_struct, c) )
                     end
        )
      end.()
    |> (select [s, p], p)

  defp map_take_to_list(m, k), do:
    m
    |> (Map.take [k])
    |> Map.to_list
6

Right now ecto does not support compound foreign keys when working with associations.

Is this still the case as of Ecto 3.0.4 ?

1 Like
7

It seems so, I just tried to migrate some of the sql execute’s in a new migration to see if references could handle them yet. Tried a variety of things from add blah, references(:blah, column: :"(bloop,bleep)") to add blah, references(:blah, column: :"bloop,bleep") to add blah, references(:blah, column: [:bloop, :bleep]) to add blah, references(:blah, column: fragment("(bloop,bleep)")) and a few other variations, then looked in code and apparently it always wraps whatever is passed as a string, no way to fragment escape it or anything, so… it’s still not handled.

It seems if we could fragment inject it or if a helper call like add blah, references(:blah, column: composite([:bloop, :bleep])) (or just wrap it in a list with no helper) were added then it would help a lot. At that point migrations would be mostly fixed, then the schema belongs_to/has_one/has_many would need fixing with an equivalent composite/1 call support or so. Unsure what else but that would be the bulk right there.

The raw PostgreSQL itself and the manual association query building still works, just not as clean or safe.

2 Likes
8

Hmm, it looks like adding a case to:


Would fix the migration case, but unsure what other possible issues it could cause since quote_name is used in a few places, maybe having a new function that CONSTRAINTS and such call instead of quote_name would work, something like:

defp quote_names([]) do
  error!(nil, "empty field name given")
end
defp quote_names([name]) do
  quote_name(name)
end
defp quote_names(names) when is_list(names) do
  names
  |> Enum.map(&quote_name)
  |> Enum.join(","
end
defp quote_names(names) do
  quote_name(name)
end

Or something like that…

At the very least that would give get the otherwise-required-sql bit fixed, the ecto queries sans-association helpers work fine otherwise, so that could be a PR on it’s own. Another PR could fix up all the association helpers to support composite keys as well.

9

You may try to propose such change, but remember that Ecto has been declared as “done”, so I don’t know if this feature change will be accepted by Ecto’s maintainers. Good luck!

10

Ecto’s still missing quite a bit of SQL functionality, they’d probably take things that are still backwards compatible I’d think, either that or it will eventually get forked or rewritten. ^.^;

2 Likes
11

When it comes to CockroachDB Enterprise, composite primary/foreign key support is a must because CockroachDB leverages composite primary key to achieve record-level geo-partitioning.

2 Likes
12

Here here, I’m running into the exact same need. Added an issue to the project for tracking purposes: https://github.com/elixir-ecto/ecto/issues/3351

1 Like
13

Actually I had another problem: making a single database connection pool that contains connections to multiple database hosts.

Because the CockroachDB nodes in a single datacenter are functionally identical, I think it would be better to put the connections to those nodes in a single connection pool so that the load can be distributed evenly. Though it’s viable to introduce an HAProxy, the proxy itself can become a single point of failure.

1 Like