伯爵在主动管理仪表板（Rails中，主动管理1.0，PostgreSQL数据库，postgres_ext GEM）序列化的属性（数组）值的出现 [英] Count occurrence of values in a serialized attribute(array) in Active Admin dashboard (Rails, Active admin 1.0, Postgresql database, postgres_ext gem)

问题描述

我想有一个基本的表格总结了数组内的值occurence的数量。

I'd like to have a basic table summing up the number of occurence of values inside arrays.

我的应用程序是专为学习on Rails的Ruby的更多。一个每日交易应用

My app is a Daily Deal app built to learn more Ruby on Rails.

我有一个模型优惠，其中有一个名为Deal_goal一个属性。这是一个多重选择它是一个数组序列化。

I have a model Deals, which has one attribute called Deal_goal. It's a multiple select which is serialized in an array.

下面是从schema.db采取deal_goal：

Here is the deal_goal taken from schema.db:

t.string   "deal_goal",:array => true

所以交易A可以有交易=目标= [交通，资格]和另一笔交易可以有deal_goal = [品牌，交通，收购]

So a deal A can have deal= goal =[traffic, qualification] and another deal can have as deal_goal=[branding, traffic, acquisition]

我想打造的是在我的仪表盘上的表，该表将采取每种类型的目标（数组中的每个值），并计算其deal_goal的数组将包含此类型的目标，并指望他们交易的数量。

What I'd like to build is a table in my dashboard which would take each type of goal (each value in the array) and count the number of deals whose deal_goal's array would contain this type of goal and count them.

我的目标是让这个表：

我怎样才能做到这一点？我想我需要组每种类型的值的每个deal_goal数组，然后计算该在哪里出现的目标在阵列中的次数。我是很新的回报率和无法管理做到这一点。

How can I achieve this? I think I would need to group each deal_goal array for each type of value and then count the number of times where this goals appears in the arrays. I'm quite new to RoR and can't manage to do it.

下面是我的code迄今：

Here is my code so far:

column do
    panel "top of Goals" do
          table_for Deal.limit(10) do
            column ("Goal"),  :deal_goal ????
            # add 2 columns:
            'nb of deals with this goal'
            'Share of deals with this goal'
          end
        end

任何帮助将是非常美联社preciated！

Any help would be much appreciated!

推荐答案

我想不出任何干净的方式来让你通过ActiveRecord的是之后的结果却是pretty容易SQL。

I can't think of any clean way to get the results you're after through ActiveRecord but it is pretty easy in SQL.

所有你真正要做的是打开 deal_goal 阵列，并建立基于打开阵列的直方图。你可以前preSS直接在SQL这样的：

All you're really trying to do is open up the deal_goal arrays and build a histogram based on the opened arrays. You can express that directly in SQL this way:

with expanded_deals(id, goal) as (
    select id, unnest(deal_goal)
    from deals
)
select goal, count(*) n
from expanded_deals
group by goal

如果你要包括即使它们不会出现在任何的全部四个进球 deal_goal •然后只是折腾了LEFT JOIN说出这样的话：

And if you want to include all four goals even if they don't appear in any of the deal_goals then just toss in a LEFT JOIN to say so:

with
    all_goals(goal) as (
        values ('traffic'),
               ('acquisition'),
               ('branding'),
               ('qualification')
    ),
    expanded_deals(id, goal) as (
        select id, unnest(deal_goal)
        from deals
    )
select all_goals.goal goal,
       count(expanded_deals.id) n
from all_goals
left join expanded_deals using (goal)
group by all_goals.goal

SQL演示： http://sqlfiddle.com/#！ 15 / 3f0af / 20

投掷那些成<一之一href=\"http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html#method-i-select_rows\"相对=nofollow> select_rows 打电话，你会得到你的数据：

Throw one of those into a select_rows call and you'll get your data:

Deal.connection.select_rows(%q{ SQL goes here }).each do |row|
  goal = row.first
  n    = row.last.to_i
  #....
end

---

有可能是一个很多事情在这里，你是不熟悉的，所以我要解释一下。

---

There's probably a lot going on here that you're not familiar with so I'll explain a little.

首先，我使用的是与普通表前pressions（CTE），以简化进行选择。 的是一个标准的SQL功能，让你产生SQL宏或内联排序的临时表。在大多数情况下，你可以把CTE拖放就在那里查询它的名字是：

First of all, I'm using WITH and Common Table Expressions (CTE) to simplify the SELECTs. WITH is a standard SQL feature that allows you to produce SQL macros or inlined temporary tables of a sort. For the most part, you can take the CTE and drop it right in the query where its name is:

with some_cte(colname1, colname2, ...) as ( some_pile_of_complexity )
select * from some_cte

是这样的：

select * from ( some_pile_of_complexity ) as some_cte(colname1, colname2, ...)

热膨胀系数是重构一个过于复杂的查询/法成更小，更容易理解作品的SQL的方式。

CTEs are the SQL way of refactoring an overly complex query/method into smaller and easier to understand pieces.

UNNEST 是一种解压到数组各行的阵列功能。所以，如果你说 UNNEST（ARRAY [1,2]），你会得到两行回： 1 和 2 。

unnest is an array function which unpacks an array into individual rows. So if you say unnest(ARRAY[1,2]), you get two rows back: 1 and 2.

值 PostgreSQL中来，或多或少，产生内联常数表。您可以使用VALUES的任何地方，你可以使用一个普通的表，它不只是一些语法，您在INSERT扔告诉什么值插入数据库。这意味着，你可以说这样的事情：

VALUES in PostgreSQL is used to, more or less, generate inlined constant tables. You can use VALUES anywhere you could use a normal table, it isn't just some syntax that you throw in an INSERT to tell the database what values to insert. That means that you can say things like this:

select * from (values (1), (2)) as dt

和获得的行 1 和 2 出来。投掷的值转换为CTE使事情变得漂亮和可读性，使它看起来像任何旧表中的最后查询。

and get the rows 1 and 2 out. Throwing that VALUES into a CTE makes things nice and readable and makes it look like any old table in the final query.

这篇关于伯爵在主动管理仪表板（Rails中，主动管理1.0，PostgreSQL数据库，postgres_ext GEM）序列化的属性（数组）值的出现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！