Selecting from many_to_many associations

Questions / Help
1

Hello!

I’m currently working on porting a website to Elixir/Phoenix/Ecto, and I’m wrestling with some legacy database questions.

I have a schema Project which has:

  • many_to_many :illustrators
  • many_to_many :clients
  • many_to_many :agencies

and tons of more.

When I query, I get all these extra fields I have no use for, so I started looking at select. I only want a field or two from the associations, so I tried writing my query like this:

      MyApp.Repo.one(
        from project in Project,
          preload: [
            :illustrators, :clients, :agencies, :image_series
          ],
          where: project.id == 148,
          select: map(project, [
            :title,
            :slug,
            illustrators: [
              :name,
              :slug
            ],
            clients: [
              :name
            ],
            agencies: [
              :name
            ]
          ])
      )

which returns

%{agencies: [], 
  clients: [], 
  illustrators: [], 
  slug: "project",
  title: "Project"}

When I query without the select, I get all the information I need, it’s just that I get much more than I need!

Is there a way to do what I want here, or is the select(map, ...) mainly for belongs_to/incompatible with many_to_many?

OR am I just “doing it wrong”?

Thanks!

2

You can use preload queries to customize how the association is preloaded: https://hexdocs.pm/ecto/Ecto.Query.html#preload/3

comments_query = from c in Comment, order_by: c.published_at
Repo.all from p in Post, preload: [comments: ^comments_query]
1 Like
3

Right, but then I can’t select from the “main” query, it seems.

I did a bunch of preload queries like so:

    illustrators_query =
      from i in Illustrator,
        select: struct(i, [:name, :slug])

    clients_query =
      from c in Client,
        select: struct(c, [:name])

    agencies_query =
      from c in Client,
        select: struct(c, [:name])

    images_query =
      from i in ProjectImage,
        order_by: i.sequence,
        select: struct(i, [:image])

    image_series_query =
      from is in ProjectImageSeries,
        preload: [images: ^images_query]

which does what I want with the associations, but I can’t seem to then select fields from project.

I’m trying to do both :slight_smile:

Thanks for taking a look!