Ecto : where exists with assoc

Phoenix Forum Questions / Help
1

So I have this query which works:

    from(c in Certification,
      where: c.is_active and fragment("""
        EXISTS (
          SELECT * FROM delegates
          INNER JOIN certifications_delegates
          ON delegates.id = certifications_delegates.delegate_id
          INNER JOIN certifications
          ON certifications_delegates.certification_id = ?
          WHERE delegates.is_active
        )
      """, c.id)
    )

But I’d like to replace the EXISTS part with assoc(c, :active_delegates) which works in:

from c in Certification, join: d in assoc(c, :active_delegates)

but not in the EXISTS.

I tried:

from(c in Certification,
      where: c.is_active and fragment("EXISTS (?)", from d in assoc(c, :active_delegates)))

and

from(c in Certification,
      where: c.is_active and fragment("EXISTS (?)", subquery(from d in assoc(c, :active_delegates))))

without success. :frowning:

Can anyone help?

Thanks

2

Hey @augnustin ,

You could try this:

      from(
        c in Certification,
        join: cd in "certifications_delegates",
        on: c.id == cd.certification_id and cd.certification_id == ^certification_id,
        join: d in Delegate,
        on: d.id == cd.delegate_id,
        where: not is_nil(cd.certification_id) and not is_nil(cd.delegate_id) and d.is_active,
        distinct: true
      )

This is a way that I found to do an EXISTS in Ecto.