# Ugly as sin, but I could not figure out a better way to do this in ecto
# This tries to find any objects with this attachment
# This DOES NOT handle chat messages and SKIPS them, because they have attachments as an object instead of an array
Repo.query!(
- """
- SELECT * FROM
- (SELECT objects.data->>'id' AS id, jsonb_array_elements(objects.data->'attachment') as json_attachments FROM objects WHERE jsonb_typeof(objects.data->'attachment') = 'array')
- WHERE STARTS_WITH(json_attachments->'url'->0->>'href', $1)
- """,
- [clean_href]
- ).rows |> Enum.map(&List.first(&1))
+ """
+ SELECT * FROM
+ (SELECT objects.data->>'id' AS id, jsonb_array_elements(objects.data->'attachment') as json_attachments FROM objects WHERE jsonb_typeof(objects.data->'attachment') = 'array')
+ WHERE STARTS_WITH(json_attachments->'url'->0->>'href', $1)