Active Admin: how to add sortable on nested model's json column

Question

How do I pass in a jsonb order query into the sortable: option for an Active Admin column?

My model is structured like this:

# User Model
class User < ActiveRecord::Base
  has_one :level
end

# Level Model
class Level < ActiveRecord::Base
  belongs_to :user     
end

# Level Migration
create_table "levels", force: :cascade do |t|
  t.integer  "user_id"
  t.jsonb    "ranked_scores"
end

The :ranked_score json structure is:

# level.ranked_scores
{"stage_1"=>111, "stage_2"=>222, "stage_3"=>333}

I have tried to sort the User using a Level's :ranked_scores attribute as follows:

# app/admin/user.rb

ActiveAdmin.register User do
  controller do
    def scoped_collection
      end_of_association_chain.includes(:level)
    end
  end

  index do
    column "Stage 1 Score", sortable: "level.ranked_scores -> 'stage_1'" do |user|
      user.level.ranked_scores['stage_1']
    end
  end
end

ActiveAdmin.register Level do
  belongs_to :user
end

The url generated to sort the colums is

http://localhost:3000/admin?order=levels.ranked_scores%5B%27stage_1%27%5D_desc

but the columns are not sorted in descending order for stage_1.

Any ideas for what is going wrong here?

I basically have to access `user.level.ranked_scores['stage_1']` in an ordered way. — Puffo, Aug 16 '15 at 08:38
Your example shows that you are using `sortable: "level.ranked_scores -> 'stage_1'"` at the moment. How does the database query looks like, if you use the sorting? — Timo Schilling, Aug 16 '15 at 08:46
From my rails console, the following is outputted when trying to use the sorting: | Parameters: {"order"=>"levels.ranked_scores['stage_1']_desc"} SELECT COUNT(count_column) FROM (SELECT 1 AS count_column FROM "users" LIMIT 30 OFFSET 0) subquery_for_count SELECT COUNT(*) FROM "users" SELECT COUNT(count_column) FROM (SELECT 1 AS count_column FROM "users" LIMIT 30 OFFSET 0) subquery_for_count SELECT "users".* FROM "users" LIMIT 30 OFFSET 0 SELECT "levels".* FROM "levels" WHERE "levels"."user_id" IN (1, 2, 3, 4, 5, 6, 7) SELECT "users".* FROM "users" | Is that helpful @TimoSchilling? — Puffo, Aug 16 '15 at 13:32
@TimoSchilling - i'm having a similar problem, and similar output - i've tested the query i'm using in psql console and it behaves as expected, but from active admin it does not sort, and in the server logs i'm seeing the same less-than-useful `subquery_for_count` — dax, Sep 03 '15 at 10:54
for the record, my query is as follows: `column 'Gold earned', sortable: "cast(payload ->> 'gold_earned' as integer) " do |gold|` — dax, Sep 03 '15 at 11:00

score 4 · Accepted Answer · edited May 23 '17 at 11:52

4

You should make 2 minor changes in admin/user.rb configuration which will make it workable.

#1 You have spaces around ->, which should be removed to make ActiveAdmin happy.

The problem here is caused by ActiveAdmin's sort validation regexp, which doesn't match your sortable option. Removing white spaces around -> can be considered as a workaround for ActiveAdmin's bug.

#2 Level's table should be referenced as levels, not level.

So finally we have:

column "Stage 1 Score", sortable: "levels.ranked_scores->'stage_1'"

And you get what you intended to have.

Note about the `->>` operator

There is another Postgres operator, ->>, which is very similar to the ->. See here.

The difference between the two is, that ->> always returns text value (stringified json), while -> can return json object. In your example their use is absolutely identical, because ranked scores are numbers.

But in general case, you might need ->> operator as well. Unfortunately ActiveAdmin still haven't fixed issues #3173 and #3085, which @bigsolom mentions in his reply. So you can't use ->> operator with current version of ActiveAdmin.

Unfortunately I can't think about any workaround, like we did with -> operator.

Still there is a hack you can use to enable this operator as well. It requires adding 2 characters into the source code of ActiveAdmin.

You need to change this line of code to the following one:

clause =~ /^([\w\_\.]+)(->>?'\w+')?_(desc|asc)$/

we added >? in-between. 2 chars, as promised.

edited May 23 '17 at 11:52

Community

1
1

answered Sep 05 '15 at 20:05

dimakura

7,288
12
34

+1 this seems like a good solution. it's a shame that we need a hack to use the `->>` operator, but so be it.. – dax Sep 07 '15 at 09:30
1

@dax I'm going to post a pull request to them. So I hope it won't be required soon. – dimakura Sep 07 '15 at 09:31
@dimakura as per your suggestion, the query now affects the ordering of the column - however, it does not order it according to the value received from the query. I think it orders it according to the length of the string. I've tried to cast the query as an integer using @dax 's comment: `cast(levels.ranked_scores->'stage_1' as integer)` but then we're back to it not affecting the column sorting. – Puffo Sep 07 '15 at 23:13
1

@Puffo the way you formatted them `{"stage_1"=>111, "stage_2"=>222, "stage_3"=>333}`, I'd thought they were numbers. I'll check it again today if something can be done in case of strings. – dimakura Sep 08 '15 at 04:13
1

Ah, @dimakura that was the final piece to the problem - I changed the persistence of the scores to be integers and it works like a charm! Thanks! – Puffo Sep 08 '15 at 07:35
works for me, works for OP, i think the bounty is yours. Thanks, @dimakura! – dax Sep 08 '15 at 07:48

score 0 · Answer 2 · edited Sep 05 '15 at 22:18

For the jsonb column you need to use ->> instead of ->, but this will cause a problem with ActiveAdmin validations (see open issues: https://github.com/activeadmin/activeadmin/issues/3173 and https://github.com/activeadmin/activeadmin/issues/3085).

I'm not sure if they fixed it or not yet, you can try

index do
  column "Stage 1 Score", sortable: "levels.ranked_scores ->> 'stage_1'" do |user|
    user.level.ranked_scores['stage_1']
  end
end

Active Admin: how to add sortable on nested model's json column

2 Answers2

Note about the ->> operator

Note about the `->>` operator