Why does this query not work? Ecto bug?

I have two schemas like this:

schema "projects" do
  field :title, :string
  field :is_shared, :boolean

schema "cards" do
  field :title, :string
  field :type, :string
  belongs_to(:project, Project)

now I want to find the cards of the project that not shared.

I use this query:

    from(c0 in Card,
        p1 in subquery(
          from p0 in Project,
            where: p0.is_shared == false,
            select: p0.id
      on: c0.project_id != p1.id,
      where: c0.type == ^"golden"

but It gives me a Postgrex error:

Postgrex.Protocol (#PID<0.494.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.527.0> timed out because it queued and checked out the connection for longer than 15000ms

But when I use the raw sql that debug print, there’s no problem.

SELECT c0."id",
FROM "cards" AS c0
         INNER JOIN (SELECT p0."id" AS "id" FROM "projects" AS p0 WHERE (p0."is_shared" = FALSE)) AS s1
                    ON c0."project_id" != s1."id"
WHERE (c0."type" = 'golden')

the raw sql above is very fast. It only cost 100-200 ms.


If the not equal(on q.id != subq.id) syntax is not supported, how do I convert this sql to Ecto query:

with not_shared as (select id
                    from projects
                    where is_shared = false)
select *
from cards c
where c.project_id not in (select * from not_shared);

I think I can solve this problem by myself:

q1 = from q in Project, where: q.is_shared == false, select: q.id
from q in query, left_join: sq in subquery(q1), on: q.project_id == sq.id and is_nil(sq.id)

But I really want to know why ecto not support not equal syntax?

Should probably be

where: c0.type == "golden"

Nope, that’s not the reason.