Ecto Join/Select Database Efficiency Questions

Questions / Help
, ,
1

So I am using the Ecto/PSQL join/select query at the end of this post to get select child and parent data to show through a feed.

I am then matching the selected variables in my html.eex template with elem(child_parent_data, 1).

Two questions:

  1. Is this the most efficient way to get this joined/selected data, by selecting it and feeding object through elem(object, index)?

  2. Do you think pattern matching elem(object, index) to a map ( %{"parent_logo" => elem(object, 0), "parent_name" => ...} etc. ) then feeding the map to a template would be more expensive or less expensive than just passing elem(object, index)s directly in the template?

The query:

def get_children_with_parents do
    query = from c in Child,
      where: c.is_active == true,
      join: p in Parents, where: p.id == c.parent_id,
      select: { 
         p.logo, 
         p.name,
         p.desc,
         c.name,
         c.image,
         c.type,
         c.slug,
         c.id
     }
2

Ecto supports selecting into a map directly. Not sure if it has any measurable performance impact vs tuples, but it is certainly more convenient than remembering the order of elements in a tuple :slightly_smiling_face:

1 Like
3

For the pattern matching, since you have a fixed tuple coming back why not just pattern match the tuple?

{
  parent_logo,
  parent_name,
  parent_desc,
  child_name,
  child_image,
  child_type,
  child_slug,
  child_id
} = object

Alternately, if you can modify the database, you could create a view for that query and create an Ecto schema over the view.

1 Like
4

I don’t think there is a performance difference between map and tuple. But tuple, especially long tuple, is hard to remember the position of each element. So I rarely use tuples for database results.

For better maintainability, why not create relationship and use preload like this:

query = from(Child, where: [is_active: true], preload: [:parent])
children = Repo.all(query)

Enum.each(children, fn child ->
  child.name
  child.parent.logo
end)

If you have to use inner join (i.e. some child has no parent), then:

from(
  c in Child,
  join: p in assoc(c, :parent),
  where: [is_active: true]
  preload: [parent: p]
)

You can also use select to reduce the fields needed. The key point is that using struct/map is much clear than tuple.

2 Likes