Vfrdtyky

In activeadmin, we need to apply a kind of complicated filter on the index page of our Score model. Namely, we want to get the results in the Score table such that their ExportOrders have the selected DistributionChain. We start from the following models:

class Score < ApplicationRecord

  has_and_belongs_to_many :export_orders, join_table: :scores_export_orders

end



class ExportOrder < ApplicationRecord

  belongs_to :distribution_chain

  has_and_belongs_to_many :scores, join_table: :scores_export_orders

end



class DistributionChain < ApplicationRecord

  has_many :export_orders

end

In the schema.rb (excerpt):

  create_table "scores_export_orders", id: false, force: :cascade do |t|

    t.integer "score_id"

    t.integer "export_order_id"

    t.index ["export_order_id"], name: "index_scores_export_orders_on_export_order_id", using: :btree

    t.index ["score_id"], name: "index_scores_export_orders_on_score_id", using: :btree

  end



  create_table "export_orders", force: :cascade do |t|

    t.integer  "distribution_chain_id"

  end

In Score Activeadmin:

ActiveAdmin.register Score

  filter :delivery_distr_chain, as: :select, collection: DistributionChain.all

end

The scope of the filter is defined in the score model:

class Score < ApplicationRecord

  ...

  scope :delivery_distr_chain, -> (input) {

    self.joins(:export_orders).where('export_orders.distribution_chain_id = ?', input)

  }



  def self.ransackable_scopes(auth_object = nil)

    [:delivery_distr_chain]

  end

end

Following is the error:

ActionView::Template::Error (PG::DuplicateAlias: ERROR:  table name "scores_export_orders" specified more than once

: SELECT COUNT(DISTINCT count_column) FROM (SELECT  DISTINCT "scores"."id" AS count_column FROM "scores" INNER JOIN "scores_export_orders" ON "scores_export_orders"."score_id" = "scores"."id" INNER JOIN "export_orders" ON "export_orders"."id" = "scores_export_orders"."export_order_id" LEFT JOIN scores_export_orders ON scores.id = scores_export_orders.score_id WHERE (NOT (scores_export_orders.score_id IS NULL)) AND (export_orders.distribution_chain_id = '2') LIMIT $1 OFFSET $2) subquery_for_count):

    1: insert_tag renderer_for(:index)



activerecord (5.0.7) lib/active_record/connection_adapters/postgresql_adapter.rb:600:in `async_exec'

I sense that the query is not properly written. Can you tell us where we are failing?

EDIT: interesting to notice is the difference between executing the query in the rails console and within the scope in the Score model.

If I execute the same query in the rails console, it works without errors. The output of Score.joins(:export_orders).where('export_orders.distribution_chain_id = ?', '2').to_sql is:

"SELECT "scores".* FROM "scores" INNER JOIN "scores_export_orders" ON "scores_export_orders"."score_id" = "scores"."id" INNER JOIN "export_orders" ON "export_orders"."id" = "scores_export_orders"."export_order_id" WHERE (export_orders.distribution_chain_id = '2') ORDER BY "scores"."created_at" DESC"

whereas the exact same query fails in the scope in the Score model, and the .to_sql output is:

"SELECT DISTINCT "scores".* FROM "scores" INNER JOIN "scores_export_orders" ON "scores_export_orders"."score_id" = "scores"."id" INNER JOIN "export_orders" ON "export_orders"."id" = "scores_export_orders"."export_order_id" LEFT JOIN scores_export_orders ON scores.id = scores_export_orders.score_id WHERE (NOT (scores_export_orders.score_id IS NULL)) AND (export_orders.distribution_chain_id = '2') ORDER BY "scores"."id" desc"

The main difference lies in the left-join introduced by the scope: LEFT JOIN scores_export_orders ON scores.id = scores_export_orders.score_id, which lets specify the scores_export_orders table once more. I have no idea why this is introduced in there and not on the console...

Solved. I was not aware of the fact that I already declare a scope in my ActiveAdmin for Scores, which was the following:

scope :exported, -> { joins("LEFT JOIN scores_export_orders ON scores.id = scores_export_orders.score_id").where.not("scores_export_orders.score_id IS NULL").distinct

called in ActiveAdmin as

  controller do

    def scoped_collection

      end_of_association_chain.exported

    end

    ...

  end

Now I've rewritten the code such that I have in my score model:

class Score < ApplicationRecord

  ...

  scope :exported, -> { joins("INNER JOIN scores_export_orders ON scores.id = scores_export_orders.score_id INNER JOIN export_orders ON export_orders.id = scores_export_orders.export_order_id").where.not("scores_export_orders.score_id IS NULL").distinct }

  ...

  scope :delivery_distr_chain, -> (input) {

    self.where('export_orders.distribution_chain_id = ?', input)

  }



  def self.ransackable_scopes(auth_object = nil)

    [:delivery_distr_chain]

  end

end

so that I have the double join on the exported scope and I don't need to join again on the filter scope.