Union with some static value in some fields (like 0 or nil)

Questions / Help
1

I’m trying to union two tables, with different rows, so I need to fill the missing rows with some default value.

My initial idea was something along this lines.

    q =
      Video
      |> select([v], [nil, v.id, v.name])

    ChatRoom
    |> select([cr], [cr.id, nil, cr.name])
    |> union_all(^q)
    |> Repo.all()

But for some reason, default values like that are ignored, and ecto will try to match everything with the same names.

** (Postgrex.Error) ERROR 42804 (datatype_mismatch) UNION types bigint and uuid cannot be matched
     
         query: SELECT c0."id", c0."name" FROM "chat_rooms" AS c0 UNION ALL (SELECT v0."id", v0."name" FROM "videos" AS v0)

For this to work, I need to replace nil with fragment("NULL")

q =
      Video
      |> select([v], [fragment("NULL"), v.id, v.name])

    ChatRoom
    |> select([cr], [cr.id, fragment("NULL"), cr.name])
    |> union_all(^q)
    |> Repo.all()

Output

  [103, nil, "Chat Room Name 2"],
  [104, nil, "Chat Room Name 3"],
  [
    nil,
    <<4, 114, 85, 84, 35, 101, 65, 48, 159, 241, 249, 34, 181, 240, 98, 189>>,
    "Video Name 0"
  ],
  [
    nil,
    <<161, 167, 229, 74, 185, 118, 71, 121, 147, 183, 174, 177, 49, 59, 133,
      129>>,
    "Video Name 1"
  ]
]

Should this make sense, and in the future be possible to use 0 or nil the same way as fragment("0") or fragment("NULL") ?

1 Like
2

Yeah it does seem odd that literal values are not treated as literals in the query… Maybe they should be pinned via ^nil instead? If that doesn’t work then maybe a bug report on github?

2 Likes
3

That worked, thanks. Never thought about using ^. Thanks!

1 Like
4

Ah awesome, good to know! ^.^

1 Like