Flattening an ecto select query

Questions / Help
1

Hello, I’ve been banging my head against this problem for a couple of hours and would appreciate any help.
The problem I’m running into is in the system there are multiple lockers associated to a locker system and multiple cubbies associated to a locker.
What I’m trying to is tie the list of cubbies directly with the locker system.

I have the following schemas:

  @primary_key {:id, :string, autogenerate: false}
  @derive {Jason.Encoder, only: [:timer, :serialnumber]}

  schema "lockersystem" do
    field :timer, :integer
    field :serialnumber, :string
    field :location, :string
    has_many :lockers, Locker, foreign_key: :lockersystem
    has_one :mastercode, Sfecfg, foreign_key: :name, references: :location,
      where: [cfgtype: "LOC", type: "LOCKERLOCATIONMASTERCODE"]
  end

  @primary_key {:id, :string, autogenerate: false}
  @foreign_key_type :string

  @derive {Jason.Encoder, only: [:lockertype]}

  schema "locker" do
    field :lockertype, :string
    belongs_to :locker_system, LockerSystem, foreign_key: :lockersystem
    has_many :cubbies, Cubby, foreign_key: :locker
  end


  @primary_key {:id, :string, autogenerate: false}
  @foreign_key_type :string

  @derive {Jason.Encoder, only: [:id, :address]}

  schema "cubby" do
    field :address, :string
    belongs_to :lockers, Locker, foreign_key: :locker
  end

This is the current code I’m running to get the results.

LockerSystem
    |> Repo.all
    |> Repo.preload([:mastercode, lockers: :cubbies])
    |> Enum.map(fn %{timer: timer, serialnumber: serialnumber, mastercode: mastercode, lockers: lockers} ->
      %{timout: timer, serial_number: serialnumber, master_code: mastercode.value, lockers: lockers, last_updated: DateTime.utc_now()}
    end)

It seems to be returning everything I need. I’m just not sure how to change the query to get the format I need it in.

2

Hi @steven.yoo :wave: and welcome to the community!

I’m just not sure how to change the query to get the format I need it in.

I see 2 levels of 1 to Many relationships between schemas… What format do you want results to be in to be flat?

3

The final enum results I’m trying to get would look like this:

[{
    serial_number: pos_integer(),
    master_code: pos_integer(),
    timeout: pos_integer(),
    last_updated: DateTime.t(),
    cubbie_addresses: [String.t()],
  }]

So I’m basically just trying to flatten the lockers into a single list of cubby addresses which would be attached to the locker system.

Thanks for the welcome :slight_smile:

4

Well for this particular example I would use aggregator function…

For example

import Ecto.Query

query = 
  from ls in LockerSystem, 
    join: l in assoc(ls, :lockers), 
    join: c in assoc(l, :cubbies), 
    group_by: [ls.serial_number, master_code, ..<all fields except cubbie_addresses>..],
    select: %{
      serial_number: ls.serial_number,
      master_code: ...,
      ...
      cubbie_addresses: fragment("array_agg(?)", c.address)
    }

Repo.all(query)

Note that array_agg is specfic to Postgres… for example its analogous in MySQL would be json_arrayagg…