Why does this query not work? Ecto bug?

Questions / Help
1

I have two schemas like this:

schema "projects" do
  field :title, :string
  field :is_shared, :boolean
  timestamps()
end

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

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

I use this query:

    from(c0 in Card,
      join:
        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",
       c0."title",
       c0."inserted_at",
       c0."updated_at"
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.

image

2

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);
3

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?

4

Should probably be

where: c0.type == "golden"
5

Nope, that’s not the reason.