Query "embeds_many" association by count

Questions / Help
1

I’m trying to find “projects” based on if embeds_many association has any records in it. Here is what I’m currently doing:

from(
        p in Project,
        as: :p,
        where: p.id in ^gallery_ids,
        where: count(p.gallery_categories) >= 1
      )

ERROR:

 ** (Postgrex.Error) ERROR 42803 (grouping_error) aggregate functions are not allowed in WHERE

I’m getting a grouping_error and I’m really not sure how to solve this problem

project.ex

 schema "projects" do
    embeds_many :gallery_categories, Dreamhouse.GalleryCategories, on_replace: :delete

    timestamps()
  end

Again, gallery_categories is an array of maps. Any help on this would be appreciated.

2

Since the underlying projects column is JSONB, use jsonb_array_length in an fragment to compute this value

2 Likes
3

Hey @al2o3cr, Thanks for the help, I tried this but it doesn’t seem to work:

from(
        p in Project,
        as: :p,
        where: p.id in ^gallery_ids,
        where: fragment("jsonb_array_length(?)", p.gallery_categories) >= 1
      )

error:

** (Postgrex.Error) ERROR 42883 (undefined_function) function jsonb_array_length(jsonb[]) does not exist

Maybe I’m not implementing it correctly?

4

Ah, I misunderstood your schema - jsonb_array_length is what you’d want if you have a column of type jsonb containing a value that’s an array at top-level; this type indicates that your column is a jsonb[] - a Postgresql array of jsonb values. In that case you’d want array_length instead. Note that the second argument counts from 1, based on the example with a 1-dimensional array.

You may want to consider caching the count in a column on projects; I don’t know if array_length is indexable.

5

I ended up going with this:

from(
        p in Project,
        as: :p,
        where: p.id in ^gallery_ids,
        where: fragment("? != '{}'", p.gallery_categories)
      )

That basically says: where the gallery_categories is not an empty list.