Ecto/SQL group results into array

Questions / Help
, ,
1

Hiya Everyone,

I’ve got a query like so:

Repo.all from m in Message,
      join: s in assoc(m, :shop),
      where: not is_nil(m.shop_id),
      group_by: [m.shop_id, s.shop_name, m.number, m.id],
      select: {m.shop_id, s.shop_name, m.number, m.id}

Which outputs:

[
  {"63adc0d5-bc3f-498a-816b-e8f43a01c62e", "Some Shop", "12345",
   "10369089-b333-4d06-818d-a52eca546d6e"},
  {"63adc0d5-bc3f-498a-816b-e8f43a01c62e", "Some Shop", "12345",
   "36820133-a947-4f87-b2a7-0b7253930c5a"},
  {"63adc0d5-bc3f-498a-816b-e8f43a01c62e", "Some Shop", "12345",
   "7c396315-3ba4-4388-bb5d-1b22c6013b10"}
]

I would appreciate some help to get it like so:

[
  {"63adc0d5-bc3f-498a-816b-e8f43a01c62e", "Some Shop", "12345",
   ["10369089-b333-4d06-818d-a52eca546d6e", "36820133-a947-4f87-b2a7-0b7253930c5a", "7c396315-3ba4-4388-bb5d-1b22c6013b10"]}
]

Is it even possible to get that result with only a query?

2

Hi
Try to remove m.id from group_by clause and replace m.id in select clause with fragment("ARRAY_AGG(?)", m.id)

1 Like
3

Mmm sadly still not, I’m still trying more things but so far the output for your suggestion is:

[
  {"63adc0d5-bc3f-498a-816b-e8f43a01c62e", "Some Store", "12345",
   [<<54, 130, 1, 51, 169, 71, 79, 135, 178, 167, 11, 114, 83, 147, 12, 90>>]},
  {"63adc0d5-bc3f-498a-816b-e8f43a01c62e", "Some Store", "12345",
   [<<124, 57, 99, 21, 59, 164, 67, 136, 187, 93, 27, 34, 198, 1, 59, 16>>]},
  {"63adc0d5-bc3f-498a-816b-e8f43a01c62e", "Some Store", "12345",
   [<<16, 54, 144, 137, 179, 51, 77, 6, 129, 141, 165, 46, 202, 84, 109, 110>>]}
]

That’s certainly closer to what I need though, so thanks :slight_smile: I’m hoping to find a way to merge the 3 rows into one and have the differing IDs in an array.

4

That is the same data, now you need just cast it to canonical string representation.

5

Can you show the exact query you ran? The result you are showing has identical values in the first 3 positions which is impossible if you’re grouping on those 3 values.

6

Yea technology hates me :stuck_out_tongue:

Here is the query:

Repo.all from m in Message,
      join: s in assoc(m, :shop),
      where: not is_nil(m.shop_id),
      group_by: [m.shop_id, s.shop_name, m.number],
      select: {m.shop_id, s.shop_name, m.number, fragment("ARRAY_AGG(?)", m.id)}

The IDs (message_id and shop_id) are :binary_id not sure if that complicates matters?

Here is the resulting query in my terminal:

SELECT m0."shop_id", s1."shop_name", m0."number", ARRAY_AGG(m0."id") FROM "messages" AS m0 INNER JOIN "shops" AS s1 ON s1."id" = m0."shop_id" WHERE (NOT (m0."shop_id" IS NULL)) GROUP BY m0."shop_id", s1."shop_name", m0."number" []
7

type(fragment(...), {:array, :binary_id}) should help

2 Likes
8

Ah, well, yes it’d help if I tell it that huh. Heh, thanks, that piece is sorted now.

[
  {"63adc0d5-bc3f-498a-816b-e8f43a01c62e", "Some Shop", "12345",
   ["36820133-a947-4f87-b2a7-0b7253930c5a"]},
  {"63adc0d5-bc3f-498a-816b-e8f43a01c62e", "Some Shop", "12345",
   ["7c396315-3ba4-4388-bb5d-1b22c6013b10"]},
  {"63adc0d5-bc3f-498a-816b-e8f43a01c62e", "Some Shop", "12345",
   ["10369089-b333-4d06-818d-a52eca546d6e"]}
]

I’m encrypting that number field (ecto_cloak). And even though it appears the same in the terminal, I assume that’s why it’s not grouping correctly?

9

Yea, I tweaked it a little to array_agg the numbers as well, it’s the ecto_cloak encryption I suppose:

[
  {"63adc0d5-bc3f-498a-816b-e8f43a01c62e", "Some Shop",
   [
     <<1, 10, 65, 69, 83, 46, 71, 67, 77, 46, 86, 49, 212, 207, 211, 232, 205,
       77, 80, 19, 80, 91, 92, 153, 56, 47, 23, 163, 131, 6, 31, 251, 105, 5,
       158, 86, 124, 198, 11, 103, 9, 170, 204, 70, 83, ...>>,
     <<1, 10, 65, 69, 83, 46, 71, 67, 77, 46, 86, 49, 72, 128, 248, 0, 127, 6,
       54, 37, 74, 154, 212, 46, 153, 244, 22, 32, 89, 222, 64, 134, 213, 67,
       248, 204, 50, 4, 44, 31, 103, 49, 166, 156, ...>>,
     <<1, 10, 65, 69, 83, 46, 71, 67, 77, 46, 86, 49, 70, 244, 226, 131, 92, 19,
       60, 99, 147, 248, 34, 185, 236, 34, 9, 58, 122, 162, 127, 228, 132, 168,
       121, 69, 69, 81, 115, 118, 10, 239, 57, ...>>
   ],
   ["10369089-b333-4d06-818d-a52eca546d6e",
    "7c396315-3ba4-4388-bb5d-1b22c6013b10",
    "36820133-a947-4f87-b2a7-0b7253930c5a"]}
]
10

Thanks for chiming in everyone – if I remove the encrypted field from the query, it does what it’s supposed to. I’ll work around that other field I guess :slight_smile:

Repo.all from m in Message,
      join: s in assoc(m, :shop),
      where: not is_nil(m.shop_id),
      group_by: [m.shop_id, s.shop_name],
      select: {m.shop_id, s.shop_name, type(fragment("ARRAY_AGG(?)", m.id), {:array, :binary_id})}

[
  {"63adc0d5-bc3f-498a-816b-e8f43a01c62e", "Some Shop",
   ["10369089-b333-4d06-818d-a52eca546d6e",
    "7c396315-3ba4-4388-bb5d-1b22c6013b10",
    "36820133-a947-4f87-b2a7-0b7253930c5a"]}
]
11

Ahhh yeah if the actual value in the database is not identical then the group by will not work as you expect.

1 Like