How to use Distinct, Select, and Where in Ecto query?

Questions / Help
1

I’m trying to create a simple query to select distinct values from a column. If I only use select and distinct I get back a list of unique values.

Users
|> select([u], u.group_id)
|> distinct(true)
|> Repo.all()

Which gives me back a list of ids as expected:

[42, 43, nil]

However, when I add a where clause to exclude the nil value I get back a different result.

Users
|> where([u], not is_nil(u.group_id))
|> select([u], u.group_id)
|> distinct(true)
|> Repo.all()

This returns the value:

'*+'

I would have expected the query to return of a list of integers without a nil value. When I run the generated raw SQL in psql I get back the list as expected.

How can I get back a list of distinct group_ids that does not include the nil value?

2

You’ve hit the charlist problem. Print your result with:

IO.inspect(result, charlists: :as_lists)

What you’re getting is a list of 2 integers. But they both happen to be printable ascii characters, which is how erlang represents strings. The single quotes are one hint.

1 Like
3

Don’t worry, this happens to everybody at some point. Erlang has been around quite a while and charlists will keep rearing their bizarre head.

1 Like
4

Jeez, that’s twice now that I’ve been burned. Hopefully that’s the last time. Thank you!

5

You wait till you get burned with strange binaries. :grinning: They are both the case of not really having strings and using other datatypes to implement them.

2 Likes
6

Ha. I’ve seen simple binaries that look strange to me. Binaries are like, number two on my list of Elixir topics to learn next.