How to make a query in Ecto that checks if a Schema is related to another schema in a One to Many relationship

Questions / Help
, , ,
1

Hello there :slight_smile:

I have a schema A that has a one-to-many relationship with a schema B.

I want to make a query that lists all rows in schema A + have an extra boolean like “has_any_b_associated?”

Right now I’m doing it using a virtual field and a subquery but I wonder if there is any other simpler way to do it?

Here is the code I have right now:

Module “A”:

defmodule MyApp.Schema.A do
  @moduledoc false

  use Ecto.Schema

  import Ecto.Changeset
  import Ecto.Query

  alias MyApp.Schema.B

  @type t :: %__MODULE__{}

  @fields [
    :id
  ]

  @required_fields [
    :id
  ]

  @primary_key {:id, :binary_id, autogenerate: false}
  schema "a" do
    has_many :b, B

    field :has_any_b_associated?, :boolean, virtual: true

    timestamps()
  end

  @spec changeset(t(), map()) :: Ecto.Changeset.t()
  def changeset(%__MODULE__{} = data \\ %__MODULE__{}, attrs) do
    data
    |> cast(attrs, @fields)
    |> validate_required(@required_fields)
  end

  @spec with_has_any_b_associated?(__MODULE__ | Ecto.Query.t()) :: Ecto.Query.t()
  def with_has_any_b_associated?(query \\ __MODULE__) do
    from r in query,
      as: :schema_a,
      select: %{
        r
        | has_any_b_associated?:
            exists(
              from(
                b in B,
                where: parent_as(:schema_a).id == b.a_id,
                select: 1
              )
            )
      }
  end
end

How I build the query:

Schema.A |> Schema.A.with_has_any_b_associated?() |> Repo.all()
1 Like
2

that’s what I’d do. the virtual field + exists subsquery combo is a good choice (actually, exists is my top1 tool when I want to check conditions under relations without replicating rows with joins).

1 Like
3

Another way:

from r in query,
  left_join: b in assoc(r, :b),
  select_merge: %{
    has_any_b_associated?: count(b.id) > 0
  },
  group_by: r.id

I can’t speak to which one is more performant.

3 Likes