1

I'm working on an Ruby-on-Rails app which contains a list type of report. Two columns within that table are an aggregation from a transactional table.

So let's say we have these two tables:

**items**
id
name
group
price

**transactions**
id
item_id
type
date
qty

These two tables are connected with item_id in the transactions table.

Now I want to show some set of lines within the items table in a table and have two calculated columns within that table:

Calculated column 1 (Sparkline data):

Sparkline for transactions for the item with type="actuals" for the last 12 months. The result from the database should be text with aggregated qty for each month seperated by comma. Example: 15,20,0,12,44,33,6,4,33,23,11,65

Calculated column 2 (6m total sale):

Total qty for the item multiplied by sale for the last 6 months.

So the results would how columns like these:

Item name - Sparkline data - 6m total sale

So the result could by many thousand of lines, but would probably be paged.

So the question is, how is the most straightforward way of doing this in Rails models which doesn't sacrifice to much performance? Although this is a ruby-on-rails question it might contain more of a sql type solution.

gugguson
  • 819
  • 2
  • 13
  • 32

1 Answers1

0

The core sql could be something similar:

select
        i.id,
        i.name,
        y.sparkline,
        i.price*s.sum totalsale6m
from
        items i left join
        (select
                x.item_id,
                GROUP_CONCAT(x.sumqtd order by datemonth asc SEPARATOR ',') sparkline
        from
                (select
                        t.item_id,
                        date_format(date, '%m') datemonth,
                        sum(qtd) sumqtd
                from
                        transactions t
                where
                        t.type='actuals' and
                        t.date>date_sub(now(), interval 1 year)
                group by
                        t.item_id, datemonth
                ) x
        group by
                x.item_id
        ) y on i.id=y.item_id
        left join 
        (select
                t.item_id,
                sum(qtd) sumqtd
        from
                transactions t
        where
                t.date>date_sub(now(), interval 6 month)
        group by
                t.item_id
        ) s on i.id=s.item_id
group by
        i.id, i.name

A few comments:

  • I wasn't able to test it without real data.
  • If there are gaps in the sales, I mean no sales in a given month, then the list will not contain 12 elements. In this case you need to adjust x,y tables
  • If you need the result only for a given few items, then probably you can put the item id filter deeper into the subqueries sparing time.
Lajos Veres
  • 13,595
  • 7
  • 43
  • 56
  • Thanks. Definitely there will be many non sales months. What do you mean by adjust x,y tables? Point nr. 3 regarding put the item id filter deeper I don't understand. – gugguson Oct 06 '13 at 23:49
  • You should outer join the inner table with a continuous month list. if you have a table with continuous dates you can use it for date generator, otherwise you have to use something similar in the answers in this link: http://stackoverflow.com/questions/14105018/generating-a-series-of-dates id filtering: If you need the results for only one item, you can add t.item_id='thisid' into the internal x table's definition. – Lajos Veres Oct 07 '13 at 07:41