Join query - undefined function for table alias

Questions / Help
,
1

Hey guys,

I’m getting stuck writing a query with some joins (maybe it’s a newbie doubt, but i can’t figure it out)
I’m trying to do a query like that:

    query =
  Post
  |> where([p], p.private == false)
  |> join(:left_lateral, [p], u in ^upvotes_count(p.id))
  |> join(:left_lateral, [p, _u], d in ^downvotes_count(p.id))
  |> join(:left_lateral, [p, _u, _d], c in ^comments_count(p.id))
  |> join(:left_lateral, [p], v in ^total_votes_count(p.id))
  |> preload([:user])
  |> select([p, u, d, c, v], %{
    p
    | downvotes_count: d.count,
      upvotes_count: u.count,
      comments_count: c.count,
      total_votes: v.count
  })

posts = Repo.all(query)

But for some reason i get the following error:

** (CompileError) undefined function p/0
(stdlib) lists.erl:1338: :lists.foreach/2
(stdlib) erl_eval.erl:680: :erl_eval.do_apply/6

What i missing here?
I read some documents, but i can’t fix it :confused:

2

:wave:

Have you import Ecto.Query?

Also you might want to replace

|> join(:left_lateral, [p], v in ^total_votes_count(p.id))

with

|> join(:left_lateral, [p, _u, _d, _c], v in ^total_votes_count(p.id))
3

Yes, i do :slight_smile:

4

Can you post the full stack trace leading to the compilation error?

5 
Compiling 1 file (.ex)

warning: variable "p" does not exist and is being expanded to "p()", please use parentheses to remove the ambiguity or change the variable name
  posts.ex:24

warning: variable "p" does not exist and is being expanded to "p()", please use parentheses to remove the ambiguity or change the variable name
  posts.ex:25

warning: variable "p" does not exist and is being expanded to "p()", please use parentheses to remove the ambiguity or change the variable name
  posts.ex:26

warning: variable "p" does not exist and is being expanded to "p()", please use parentheses to remove the ambiguity or change the variable name
  posts.ex:27

warning: variable "posts" is unused
 posts.ex:37

warning: variable "vote" is unused
  posts.ex:314


== Compilation error in file posts.ex ==
** (CompileError) posts.ex:24: undefined function p/0
    (stdlib) lists.erl:1338: :lists.foreach/2
    (stdlib) erl_eval.erl:680: :erl_eval.do_apply/6
could not compile dependency :module_name, "mix compile" failed. You can recompile this dependency with "mix deps.compile module_name", update it with "mix deps.update module_name" or clean it with "mix deps.clean modula_name"

Just omit package and app names for security.

6

I think you forgot to import ecto query.

7

image

It’s imported here :frowning:

8

It doesn’t really tell me anything. But the error and warnings that you’ve posted above indicate that Ecto.Query is not available.

Note that if you import it in a function block, it doesn’t make it available in other function blocks.

9

It’s globally imported :frowning:

10

@EngErik Can you share a simplest repository which reproduces your error? Just create new app, add ecto (for 2.x.y) or ecto_sql (for 3.x.y) and copy only important parts for that query. For example instead of all fields in Post schema use only those used in query etc. It’s generally faster to simply clone it and try on your own rather than guess what’s going on especially in ecto problems case. Also really often when you are creating such minimal repository you often can find a typo or other typical stupid mistake which you made and it’s a bit hard to find for us, because we do not see your whole code.

11

Thank you Eiji.
I’ll try to do that and i know that is dificulty to guessing things too. :frowning:
It’s strange, because other queries using the same pattern works like a charm :confused:

12

Elixir does not have global imports. All imports are lexically scoped.

2 Likes
13 
u in ^upvotes_count(p.id)

You cannot reference p.id inside ^upvote_counts(...) like this. The expression after ^ (the pin operator) will be evaluated in the surrounding context, without the query bindings. If upvote_counts/1 is query macro you must call it without the pin. If upvote counts is not a query macro you may convert it to one.

See
https://hexdocs.pm/ecto/Ecto.Query.API.html#fragment/1-defining-custom-functions-using-macros-and-fragment.

The same goes for the rest of the interpolated calls.

I’m guessing upvotes_count/1 returns a query for a given post id. This will not help you when joining in a query where the post id is unknown unless you have written the lateral join query in SQL using fragment(...). You can abstract a non-fragment query to work for arbitrary posts:

def count_upvotes_query() do
  subquery(
    from v in Votes,
      where: v.direction == :up,
      group_by: v.post_id,
      select: %{post_id: v.post_id, count: count(v.id)}
  )
end

...
|> join(:left, [p], u in ^count_upvotes_query(), on: p.id == u.post_id)
...

You may abstract this further:

def count_assoc_query(source, fk, filters \\ []) do
  subquery(
    from a in source,
      where: ^filters,
      group_by: field(a, ^fk),
      select: merge(%{count: count(field(a, ^fk))}, map(a, [fk]))
  )
end

...
|> join(:left, [p], u in ^count_assoc_query(Vote, :post_id, [direction: :up]),
        on: p.id == u.post_id)
|> join(:left, [p], t in ^count_assoc_query(Vote, :post_id),
        on: t.id == u.post_id)
|> join(:left, [p], c in ^count_assoc_query(Comment, :post_id),
        on: p.id == c.post_id)
...
1 Like