Has_many through load selected associated columns

Questions / Help
,
1

Building on the example from another thread:
https://elixirforum.com/t/many-to-many-once-again-extra-data-on-the-join-table/
User has many Chatroom through UsersChatrooms.

Now, I would like to get names and ids of given user’s chatrooms. I surely can do for example:

user = Repo.preload(user, :chatrooms)

but this will load “everything” from both final and join tables, while I’d rather do something like Rails’ pluck or at least select so that only columns I am interested in are queried. What are the “correct” ways of doing this with Ecto associations?

Alternatively already while fetching the user record

2

You can use ecto query to select what you need. There’s not really a high level API for that in ecto.

query = 
  from cr in Chatroom, 
    join: ucr in assoc(:users_chatrooms), 
    on: ucr.user_id == ^user_id and ucr.chatroom_id == cr.id,
    select: map(cr, [:id, :name])

Repo.all(query)

or

user
|> Ecto.assoc(:chatrooms)
|> select([cr], map(cr, [:id, :name])
|> Repo.all()
3 Likes
3

Ah, so basically this means that one has to build an appropriate query himself. I presume the same would apply if I didn’t have the User struct already and wanted to fetch the user along with its selected associated data, right?

4

Yep, more or less. And it’s important to remember that Ecto is explicit: it tries very hard not to do things you didn’t ask it to do.

2 Likes
5

TNX.

I can understand and even relate to that on some occasions. On the majority of them though it feels similar to what I felt many years ago when I had to finish application written by somebody else, using “Zend framework”. My main thought and takeaway from that experience was something like “what on Earth do I need this whole Zend framework for, if all I do with it is translating SQL into framework’s syntax, which is going to be then translated back into SQL anyway”. In the majority of cases like the fairly trivial one here, I’d be happy to write a one-liner:

user.chatrooms.pluck(:id, :name)

and be done with it, trusting the library to do The Right Thing™, instead of forcing me to think in SQL terms first, and then how to express those in the library’s own syntax/DSL. So yes, there are pros and cons. And yes, there are cases where fine-grained, low-level tuning is important but in lots of typical ones it isn’t. At least IMHO :wink:

6

I sympathize with the need. You can take it one step further by making helpers for it in your context/domain modules, e.g. with the second coding snippet that @LostKobrakai showed you like so:

defmodule MyApp.Users do
  def with_chatroom_names(user) do
    user
    |> Ecto.assoc(:chatrooms)
    |> select([cr], map(cr, [:id, :name])
    |> Repo.all()
  end
end

And it becomes intuitive to read the consuming modules when they do stuff like Users.with_chatroom_names(user) |> do_other_stuff() …IMO anyway; naming is hard).

I was a Rails dev too, long ago (for 6.5 years). I still sometimes miss bits and pieces from it but as time went by I gravitated towards preferring to get my hands a little more dirty for the added peace of mind that when looking at the app’s code then what I see is exactly what I get.

But again, if you find that clunky, nothing stops you from emulating a few favourite Rails-like idioms in your projects, or even spin them off to internal company libraries (I’ve done so in the past).

1 Like
7

:smiley: That’s precisely what I eventually did!

I worked with numerous languages/frameworks but of all of them my relationship with Elixir/Phoenix is so far probably the most “intense” and of “love-hate” kind. I believe thanks to this I have a glimpse of what people with bipolar disorder feel :slight_smile:

8

Ecto is far more geared towards giving developers the ability to deal with the sql. It’s not an ORM and doesn’t try to be. One could probably built one on top of ecto. I can see this not being great when trying to get things of the ground, but it’s really great once you need to maintain that thing flying.

3 Likes
9

That’s what I keep telling myself :wink: and others :wink: And – BTW – I think this is an excellent analogy. That’s exactly how I feel: V1 reached[1] long time ago but Vr still not even close, despite the end of runway uncomfortably well in sight.

[1] - rewriting it in something else no longer an option, especially that it was me who insisted on biting the bullet and diving into this brave new world :wink:

10

Well if that ain’t some hyperbole yet I know exactly what you mean :sweat_smile:

My super hot take as a fellow long-time rails developer is that the sooner you forget all conveniences of rails and any other OO-based web framework you’ve used, the sooner you will find happiness. You will maybe even start to feel that you are in a better place! By your last post, it does seem like you are getting there :slight_smile:

Another thing to keep in mind is that Ecto is very lightweight compared to a memory hog like ActiveRecord. I would say that the true selling point of .pluck is that it bypasses ActiveRecord object creation and returns your results in language primitives. In the case of Ecto, this is all it knows how to do!!!

Where I’m going with this is that say you already have something general like this:

# Chatroom Schema, or wherever you personally store your queries
def for_user(query, user) do
  from q in query,
    where: q.user_id == ^user.id
end

# Chatrooms Context
def for_user(user)
  Chatroom
  |> Chatroom.with_user(user)
  |> Repo.all()
end

Then it’s not the end of the world to do this:

user
|> Chatrooms.for_user()
|> Enum.map(fn %{id: id, name: name} -> {id, name} end)

…especially if your chatrooms table doesn’t have a lot of columns.

You could even pull that last line into a helper:

def plunk(collection, fields) do
  Enum.map(collection, fn entity ->
    List.to_tuple(Enum.map(fields, &entity[&1]))
  end)
end

Now you can do:

user |> Chatrooms.for_user() |> plunk([:id, :name])

…and hey, that kinda almost sorta mostly loosely sorta kinda looks like Rails!

Do I recommend doing this? Not realllllly, but maybe? It would be nice for use in iex. I’m really just sharing where my mind went when I was in your position, especially in regards to unlearning muscle-memory from ORMs and that Ecto is a very different beast in more ways than it makes you deal with SQL more directly.

11

The first thing I don’t want to do is I don’t want to query the fields I am not interested in. For that I could use select and since on top of that, in the given case I am interested only in values then pluck gives me both in one go :wink:

:smiley: Indeed. But that’s never been my point (or goal). I have nothing against things looking different than Rails (or Laravel, or… ) as long as they add [similar] value. My main problem like the one I described above about Zend long time ago is that I need to think in SQL. That’s not an issue per se. I know SQL more than enough for this. But then I have to laboriously (especially when things become much more complex than the example here) translate SQL into whatever I am coding in so that it can then translate it back into more or less the same SQL I initially came up with. This doesn’t feel good. For the same reasons in Rails I’ve never been a fan of [directly] using Arel for example. If I am to write complex Arel stuff, remembering or looking up how to express this or that SQL in Arel then I rather write SQL and spare both me and the lib the trouble of translating everything back and forth. Yes, I know that I can write plain SQL in Rails (and Arel, and Ecto and others). And I sometimes do but I prefer doing this on relatively rare occasions, like for optimising complex queries for example. Or querying something that couldn’t be/wasn’t modelled as ER.

Anyway - thanks for the input. The major takeaway I think is

:smiley:

12

select can do it in “one go” as well.

user |> Ecto.assoc(:chatrooms) |> select([cr], map(cr, [:id, :name])) |> Repo.all()
13

Yes, as mentioned I used your initial response to create a helper:

	def names_and_ids_for_user(user, order_by \\ nil) do
		user
		|> Ecto.assoc(:chatrooms)
		|> select([o], map(o, [:id, :name0]))
		|> order_by(^order_by)
		|> Repo.all()
	end

Which is exactly based on your suggestion. Once more - thank you for your prompt and very helpful responses!

14

I went a little too far with my answer by extracting it to a function when obviously @LostKobrakai 's a way better version. My point was just that Ecto is much faster than ActiveRecord (when you are creating AR objects) so it’s not the end of the world to grab all the fields but that is really neither here nor there.

Moving on from pluck, from what you wrote I get the impression you haven’t grasped the full power of composability. You certainly shouldn’t be needing to write SQL first to turn it into Ecto with any regularity. I’ve found Ecto to be incredibly concise and I don’t miss AR even a little. A lot of reusable little one line query helpers can go a really long way. I would have to see examples of what you’re doing, though (not suggesting you want to share, of course :sweat_smile:)

1 Like
15

Sure, Ecto is expected to be much faster in given case as it does not need to create large objects in a still relatively slow language/environment. I presume (and hope - although haven’t done any Benchee stuff to prove it) it should be faster even against the pluck option of Rails.

I am not sure what is the meaning of the end of the sentence here (?).

Still I very, very rarely “grab all the fields/columns” when I don’t need them. That’s because I very rarely am fully convinced that the table I grab things from today is not going to expand beyond the realm of what I find “decent” for this approach if not tomorrow then a bit later. Sure, for small projects, low traffic and stuff that is not really meant to be extended that is an absolutely viable way. In more general case OTOH I am much more constrained in my approach choices. Some may call it “premature optimisation”, which it might actually be, but I call it “it doesn’t cost me to do it more performant already from the start”. At least unless it does… :wink:

I can’t be sure how far I am with grasping its full power. I like and use it whenever it makes sense to me. Just don’t really see the point and how it relates to:

The very first example from this very thread

query = 
  from cr in Chatroom, 
    join: ucr in assoc(:users_chatrooms), 
    on: ucr.user_id == ^user_id and ucr.chatroom_id == cr.id,
    select: map(cr, [:id, :name])

Repo.all(query)

If that’s not writing SQL in a convoluted (for SQL savvy person) syntax?

And for something that I know can be as simple as user.chatrooms.pluck(:id, :name)

The same for user.chatrooms.count, which is obvious what it does, and I both understand and remember it the moment I see it but had to google the other day how to express this to make Ecto happy. And guess what? The first thing I found was another writing another SQL query in Ecto syntax.

Yes, I know that Ecto is not an ORM, It is closer to Arel for example, on top of which an ORM could be built. Yes, I know there’s also “pipe syntax” which while still far away from the simplicity of the ORM syntax is much more palatable for me. If for one reason then for the fact that it does not resemble SQL so much as to start questioning its added value… even if that’s only for fooling myself :wink:

In any case, my problem was swiftly solved by Benjamin and the rest is mostly the result of my personal “love-hate” relationship with Elixir and its libs.

16

Ecto only fetches the fields present in the schema anyways. You could even have multiple schemas for a single table, but different use cases. If you’re hardly fetching all fields of a schema I’d wonder if the schema is well defined or if you’d not rather want to split it into multiple.

4 Likes
17

Which would make the problem of a) querying unneeded baggage, b) fetching that, and c) plucking required fields from a large record go away indeed. Shall keep that in mind as a good alternative in appropriate cases!

I didn’t say that. It is when I don’t need it, especially in a busy application spot, I don’t want to do all the three things I mentioned above.