a sleepy programmer's blog

notes and stuff.

Postgres Window Functions in Rails

I was working on a simple(I thought it was simple) feature wherein I wanted to list parent models. In the same table, particular child models for each parent were to be listed too. Say, a list of accounts, each with its latest 3 transactions. This feature turned out to be lot more tougher than I thought and was an excellent learning curve. Below are the details.

Lets say that we have a simple application consisting for 2 models, Account and Transaction.

1
2
3
4
5
6
7
8
9
10
11
12
class Account
  has_many :transactions

  validates :account_number, presence: true
end

class Transaction
  belongs_to :account

  validates :account, presence: true
  validates :amount, presence: true
end

Now, as stated earlier, we need to list the accounts and last 3 transactions for each account.

1
2
3
4
def index
  # for sake of simplicity, lets load first 5 accounts.
  @accounts = Account.limit(5)
end
1
2
3
4
5
# displaying the accounts
- @accounts.each do |account|
  = display the account's details
  - account.transactions.order(created_at :desc).limit(3).each do |t|
    = each transaction's details

This is not the right solution because it fires a query to load last 3 transactions for each account causing the N+1 problem. ‘including’ the transactions in the index was of no use too since that would load all the transactions for the 5 accounts and we just wanted the latest 3.

I had read somewhere that we can define relations as scopes. Since we can order/filter the data in the scope, I created a scope to load the required transactions.

1
2
3
4
5
6
7
8
class Transaction
  belongs_to :account

  validates :account, presence: true
  validates :amount, presence: true

  scope :latest, -> { order(created_at: :desc).limit(3) }
end

Then, going ahead, decided to create a new association that would use to scope and would retrive only the required transactions for the account.

1
2
3
4
5
6
class Account
  has_many :transactions
  has_many :latest_transactions, -> { latest }, class_name: Transaction

  validates :account_number, presence: true
end

Now, since we can include an association for eager loading, I can definitely(I though so) use the newest association to load the accounts(first 5) and their required(latest 3) transactions.

1
2
3
4
def index
  # for sake of simplicity, lets load first 5 accounts.
  @accounts = Account.limit(5).includes(:latest_transactions)
end

Turns out, this doesn’t work as expected. The queries for the above code were ignoring the limit clause and retrieving all the transactions for the accounts. The only thing that worked was the ordering.

Anyways, the limit clause in the above query wasn’t going to solve the problem because we needed the limit clause on each account’s transactions and not all the transactions as a whole. My bad.

So, upon futher searching, I was directed to checkout the windows function provided in Postgres. Here is the documentation and examples. Please check them out for better clarity.

Using the windows function, we can perform aggregate(sort, order, group, limit etc) on partitioned data. The 3 function that we can use in the current situation are row_number, rank and dense_rank.

  • row_number: number of the current row within its partition, counting from 1
  • rank: rank of the current row with gaps; same as row_number of its first peer. ie, if N records have the same rank X, then the next record in the partition has rank N+X rather than X+1. eg, if you have 5 items at rank 2, the next rank listed would be ranked 7 and not 3.
  • dense_rank: rank of the current row without gaps; this function counts peer groups. ie, if N records have the same rank X, then the next record in the partition has rank X+1. eg, if you have 5 items at rank 2, the next rank listed would be ranked 3.

We can use any of the 3 functions since the ranking is going to be based on created_at(DateTime) which is always going to be unique across all records in the table.

So basically, we would be dividing the transactions collection based on the account_id, order each partition in descending order of created_at and ranking each record of the set based on created_at. So, the newest record would have the lowest rank.

So, our basic query is going to be as

1
2
3
4
5
select transactions.*, dense_rank() over(
  partition by transactions.account_id
  order by transactions.created_at desc
) as t_rank
from transactions;

This lists all the transactions partitioned/grouped by transactions.account_id and order in desc order of created_at. Since the created_at is going to be unique across all the transactions in the table, the combination of (account_id and t_rank) is unique.

Since we need transactions with t_rank <= 3, we use the above query in a nested select statement.

1
2
3
4
5
6
7
8
select * from (
  select transactions.*, dense_rank() over(
    partition by transactions.account_id
    order by transactions.created_at desc
  ) as t_rank
  from transactions
) as latest_transactions
where t_rank <= 3;

Lastly, we don’t need the transactions for all accounts but only for specific accounts. In out case, the first 5 accounts.

1
2
3
4
5
6
7
8
9
select * from (
  select transactions.*, dense_rank() over(
    partition by transactions.account_id
    order by transactions.created_at desc
  ) as t_rank
  from transactions
  where transactions.account_id in (1,2,3,4,5)
) as latest_transactions
where t_rank <= 3;

Now, we have a query that retrieves only the last 3 transactions for specific accounts. Since, we tried using the association with includes but couldnt work it out, the only option is to skip includes and load the data explicitly.

So, we declare a class to perform the task. Our class should accept an collection of accounts, retrieve the associated transactions using the query defined above and should assign the related transactions to each of the passed accounts.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
class LatestTransactions
  def initialize(accounts)
    @accounts = accounts
  end

  attr_accessor :accounts

  #generate the inner sql qwery
  def inner_query
    ids = @accounts.collect &:id
    Transaction.select(
      '*, dense_rank() over( 
        partition by transactions.account_id 
        order by transactions.created_at
      ) as t_rank'
    ).where(account_id: ids).to_sql
  end

  #create complete query and execute.
  def transactions
    Transaction.select('*').from("(#{inner}) as latest_transactions").where('t_rank <= 3')
  end
end

Finally, we have a service which accepts a collection of Account and fetches their latest transactions. Now, the only problem remaining is grouping the transactions as per their account and assigning the group to the particular account. I fixed this by adding an attr_accessor to the account model.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
class Account
  has_many :transactions
  # has_many :latest_transactions, -> { latest }, class_name: Transaction

  validates :account_number, presence: true

  attr_accessor :latest_transactions
end

class LatestTransactions
  ...

  def associate_accounts_and_transactions
    grouped_transactions = transactions.group_by(&:account_id)
    @accounts.each do |account|
      # account.latest_transactions = transactions.select{|t| t.account_id == account.id}
      account.latest_transactions = grouped_transactions[account.id]
    end
  end
end

We can use the above service as

1
2
3
4
5
def index
  service = LatestTransactions.new Account.limit(5)
  service.associate_accounts_and_transactions
  @accounts = service.accounts
end
1
2
3
4
5
# displaying the accounts and their latest 3 transactions
- @accounts.each do |account|
  = display the account's details
  - account.latest_transactions.each do |transaction|
    = each transaction's details

The above code fires only 2 queries, first to load the 5 accounts and the second to load the required transactions

1
2
3
4
5
SELECT  "accounts".* FROM "accounts" LIMIT 5
SELECT * FROM (SELECT transactions.*, dense_rank() OVER (
  PARTITION BY transactions.account_id
  ORDER BY transactions.created_at DESC
) AS t_rank FROM "transactions" WHERE "transactions"."account_id" IN (1, 2, 3, 4, 5)) AS latest_transactions WHERE (t_rank <= 3)

The above solution is crude and can be definitely improved. Hope you enjoyed it. Critics are welcome.

Creating a Simple Application Using React

In the previous post, we looked at the basic concepts in React. In this post, I would like to explain and demonstrate identifying and creating components with a simple example app.

We would be creating a simple React app as below: images

Here, we have 3 main sections: the summary of all the transactions, the form to enter the transaction details and the table displaying the transactions. So, we can say that we have 3 components. If the component becomes complex, we can futher break it down to multiple components. For now, this is enough for us to get started. Since all the 3 components are on the same level, we will hold them collectively held under a component(say TransactionsDetails)

So, the hierarchy of components is going to be as

  • TransactionsDetails
    • TransactionsSummary
    • TransactionForm
    • TransactionsTable
      • TransactionRow

For sake of simplicity, we will integrate Bootstrap after the entire app is up and running.

The basic HTML page is as below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<!DOCTYPE html>
<html>
  <head>
    <meta charset='UTF-8' />
    <title>React Rocks!!!</title>
    <script src='build/react.js'></script>
    <script src='build/react-dom.js'></script>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/babel-core/5.8.23/browser.min.js'></script>
    <script src='https://ajax.googleapis.com/ajax/libs/jquery/2.2.2/jquery.min.js'></script>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
  </head>
  <body>
    <div id='container'></div>
    <script type='text/babel'>
    </script>
  </body>
</html>

First, we instruct React to render the base component(TransactionsDetails) at ‘#container’.

1
2
3
4
5
6
7
8
9
var TransactionsDetails = React.createClass({
  render: function(){
    return(
      null
    );
  }
});

ReactDOM.render( <TransactionsDetails />, document.getElementById('container'));

Then, we add the child components: TransactionsSummary, TransactionForm & TransactionsTable. We are displaying TransactionsSummary & TransactionForm adjacent to each other. Below them, TransactionsTable is displayed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
var TransactionsSummary = React.createClass({
  render: function(){
    return (null);
  }
});

var TransactionForm = React.createClass({
  render: function(){
    return (null);
  }
});

var TransactionsTable = React.createClass({
  render: function(){
    return (null);
  }
});

var TransactionsDetails = React.createClass({
  render: function(){
    return (
      <div className="container">
        <div className='row'>
          <div className="col-md-3">
            <TransactionsSummary />
          </div>
          <div className="col-md-3">
            <TransactionForm />
          </div>
        </div>
        <div className='row'>
          <div className="col-md-6">
            <TransactionsTable />
          </div>
        </div>
      </div>
    );
  }
});

Now, lets start adding details for each component. I prefer displaying static data. Later, when all the components are in place and the static data is displayed properly, I start making changes for dynamic data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
var TransactionsSummary = React.createClass({
  render: function(){
    return(
      <ul>
        <li>Transactions: 2</li>
        <li>Debits: 500</li>
        <li>Credits: 600</li>
        <li>Balance: 100</li>
        <li>Total: 1100</li>
      </ul>
    );
  }
});

var TransactionForm = React.createClass({
  render: function(){
    return (
      <form>
        Date: <input type='text' name='date' /><br />
        Reason: <input type='text' name='reason' /><br />
        Amount: <input type='integer' name='amount' /><br />
        <button type='submit'>Submit</button>
        <button type='button'>Cancel</button>
      </form>
    );
  }
});

var TransactionsTable = React.createClass({
  render: function(){
    return (
      <table>
        <thead>
          <tr>
            <th> Date </th>
            <th> Reason </th>
            <th> Amount </th>
          </tr>
        </thead>
        <tbody>
          <tr>
            <td> 31-05-2016 </td>
            <td> Salary </td>
            <td> 600 </td>
          </tr>
          <tr>
            <td> 01-06-2016 </td>
            <td> Party </td>
            <td> -500 </td>
          </tr>
        </tbody>
      </table>
    );
  }
});

Now that the components skeleton is in place, lets start making changes for dynamic data. Lets fix the TransactionForm first. Since, the TransactionForm is the only place in the application from where data is going to be inserted, lets add states date, reason and amount to the TransactionForm component.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
var TransactionForm = React.createClass({
  getInitialState: function(){
    return { date: '', reason: '', amount: '' };
  },

  handleChange: function(e){
    switch(e.target.name){
      case 'date':
        this.setState({date: e.target.value});
        break;
      case 'reason':
        this.setState({reason: e.target.value});
        break;
      case 'amount':
        this.setState({amount: e.target.value});
        break;
    }
  },

  handleSubmit: function(e){
    e.preventDefault();
  },

  handleCancel: function(e){
    e.preventDefault();
    this.setState(this.getInitialState());
  },

  render: function(){
    return (
      <form onSubmit={this.handleSubmit}>
        Date: <input type='text' name='date' value={this.state.date} onChange={this.handleChange}/><br />
        Reason: <input type='text' name='reason' value={this.state.reason} onChange={this.handleChange}/><br />
        Amount: <input type='integer' name='amount' value={this.state.amount} onChange={this.handleChange}/><br />
        <button type='submit'>Submit</button>
        <button type='button' onClick={this.handleCancel}>Cancel</button>
      </form>
    );
  }
});

Now that we have added the basic states to the TransactionForm and have functions to update form states when user enters data, we need to add logic to accept the form submit functionality. Here, after accepting the form data, we need to save the record in the TransactionsDetails state since TransactionsSummary and TransactionsTable are the components that are going to refer to this state to update themselves.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
var TransactionForm = React.createClass({
  handleSubmit: function(e){
    e.preventDefault();
    this.props.handleNewRecord(this.state);
    this.setState(this.getInitialState());
  }
});

var TransactionsDetails = React.createClass({
  getInitialState: function(){
    return { records: [] };
  },

  addRecord: function(record){
    var records = this.state.records;
    records.push(record);
    this.setState({ records: records });
  },

  render: function(){
    return (
      ...
      <TransactionForm handleNewRecord={this.addRecord}/>
      ...
    );
  }
});

Since the form is working and the entered transactions are being saved in TransactionsDetails’s state.records, we move forward by displaying the dynamically added records in the table. For this, we introduce a new component named TransactionRow.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
var TransactionRow = React.createClass({
  render: function(){
    return (
      <tr>
        <td>{this.props.record.date}</td>
        <td>{this.props.record.reason}</td>
        <td>{this.props.record.amount}</td>
      </tr>
    );
  }
});

var TransactionsTable = React.createClass({
  render: function(){
    var rows = [];
    this.props.records.map(function(record, index){
      rows.push(<TransactionRow key={index} record={record} />);
    }.bind(this));
    return (
      <table>
        <thead>
          <tr>
            <th> Date </th>
            <th> Reason </th>
            <th> Amount </th>
          </tr>
        </thead>
        <tbody>
          { rows }
        </tbody>
      </table>
    );
  }
});

var TransactionsDetails = React.createClass({
  render: function(){
    return (
      ...
      <TransactionsTable records={this.state.records}/>
      ...
    );
  }
});

Now that we have the form and the table working, the only section remaining is the TransactionsSummary. Here, we are neither going to pass the state of TransactionsDetails but are passing the return values of functions.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
var TransactionsSummary = React.createClass({
  render: function(){
    return(
      <ul>
        <li>Transactions: {this.props.tCount}</li>
        <li>Debits: 500</li>
        <li>Credits: 600</li>
        <li>Balance: 100</li>
        <li>Total: 1100</li>
      </ul>
    );
  }
});


var TransactionsDetails = React.createClass({
  transactionsCount: function(){
    return this.state.records.length;
  },

  render: function(){
    return (
      ...
      <TransactionsSummary tCount={this.transactionsCount()}/>
      ...
    );
  }
});

Similarly, we implement the credits, debits, balance and total transactions sumation as

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
var TransactionsSummary = React.createClass({
  render: function(){
    return(
      <ul>
        <li>Transactions: {this.props.tCount}</li>
        <li>Debits: {this.props.debits}</li>
        <li>Credits: {this.props.credits}</li>
        <li>Balance: {this.props.balance}</li>
        <li>Total: {this.props.total}</li>
      </ul>
    );
  }
});

var TransactionsDetails = React.createClass({
  debits: function(){
    var sum = 0;
    var records = this.state.records.filter(function(record){ return record.amount < 0});
    if(records.length > 0){
      sum = records.reduce((function(a, b) {
        return a + parseFloat(b.amount);
      }), 0);
    }
    return -sum;
  },

  credits: function(){
    var sum = 0;
    var records = this.state.records.filter(function(record){ return record.amount >= 0});
    if(records.length > 0){
      sum = records.reduce((function(a, b) {
        return a + parseFloat(b.amount);
      }), 0);
    }
    return sum;
  },

  balance: function(){
    return this.credits() - this.debits();
  },

  totalTransactions: function(){
    return this.credits() + this.debits();
  },

  render: function(){
    return (
      ...
      <TransactionsSummary tCount={this.transactionCount()} debits={this.debits()} credits={this.credits()}
      balance={this.balance()} total={this.totalTransactions()}/>
      ...
    );
  }
});

Since we have a working example, lets integrate bootstaps for better look and feel. So, our updated code is as

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
var TransactionsSummary = React.createClass({
  render: function(){
    return(
      <ul className="list-group">
        <li className="list-group-item">
          <span className="badge">{this.props.tCount}</span>
          Count
        </li>
        <li className="list-group-item">
          <span className="badge">{this.props.debits}</span>
          Debits
        </li>
        <li className="list-group-item">
          <span className="badge">{this.props.credits}</span>
          Credits
        </li>
        <li className="list-group-item">
          <span className="badge">{this.props.balance}</span>
          Balance
        </li>
        <li className="list-group-item">
          <span className="badge">{this.props.total}</span>
          Total
        </li>
      </ul>
    );
  }
});

var TransactionForm = React.createClass({
  getInitialState: function(){
    return { date: '', reason: '', amount: '' };
  },

  handleChange: function(e){
    switch(e.target.name){
      case 'date':
        this.setState({date: e.target.value});
        break;
      case 'reason':
        this.setState({reason: e.target.value});
        break;
      case 'amount':
        this.setState({amount: e.target.value});
        break;
    }
  },

  handleSubmit: function(e){
    e.preventDefault();
    this.props.handleNewRecord(this.state);
    this.setState(this.getInitialState());
  },

  handleCancel: function(e){
    e.preventDefault();
    this.setState(this.getInitialState());
  },

  render: function(){
    return (
      <form onSubmit={this.handleSubmit} className="form-horizontal">
        <div className="form-group">
          <label for="date" className="col-sm-2 control-label">Date: </label>
          <div className="col-sm-10">
            <input type='text' name='date' className="form-control" value={this.state.date} onChange={this.handleChange}/>
          </div>
        </div>
        <div className="form-group">
          <label for="reason" className="col-sm-2 control-label">Reason: </label>
          <div className="col-sm-10">
            <input type='text' name='reason' className="form-control" value={this.state.reason} onChange={this.handleChange}/>
          </div>
        </div>
        <div className="form-group">
          <label for="amount" className="col-sm-2 control-label">Amount: </label>
          <div className="col-sm-10">
            <input type='integer' name='amount' className="form-control" value={this.state.amount} onChange={this.handleChange}/>
          </div>
        </div>
        <div className="form-group">
          <div className="col-sm-offset-2 col-sm-10">
            <button className="btn btn-primary" type='submit'>Submit</button>
            <button className="btn" type='button' onClick={this.handleCancel}>Cancel</button>
          </div>
        </div>
      </form>
    );
  }
});

var TransactionRow = React.createClass({
  render: function(){
    return (
      <tr>
        <td>{this.props.record.date}</td>
        <td>{this.props.record.reason}</td>
        <td>{this.props.record.amount}</td>
      </tr>
    );
  }
});

var TransactionsTable = React.createClass({
  render: function(){
    var rows = [];
    this.props.records.map(function(record, index){
      rows.push(<TransactionRow key={index} record={record} />);
    }.bind(this));
    return (
      <table className="table table-striped table-hover table-bordered">
        <thead>
          <tr>
            <th> Date </th>
            <th> Reason </th>
            <th> Amount </th>
          </tr>
        </thead>
        <tbody>
          { rows }
        </tbody>
      </table>
    );
  }
});

var TransactionsDetails = React.createClass({
  getInitialState: function(){
    return { records: [] };
  },

  addRecord: function(record){
    var records = this.state.records;
    records.push(record);
    this.setState({ records: records });
  },

  transactionCount: function(){
    return this.state.records.length;
  },

  debits: function(){
    var sum = 0;
    var records = this.state.records.filter(function(record){ return record.amount < 0});
    if(records.length > 0){
      sum = records.reduce((function(a, b) {
        return a + parseFloat(b.amount);
      }), 0);
    }
    return -sum;
  },

  credits: function(){
    var sum = 0;
    var records = this.state.records.filter(function(record){ return record.amount >= 0});
    if(records.length > 0){
      sum = records.reduce((function(a, b) {
        return a + parseFloat(b.amount);
      }), 0);
    }
    return sum;
  },

  balance: function(){
    return this.credits() - this.debits();
  },

  totalTransactions: function(){
    return this.credits() + this.debits();
  },

  render: function(){
    return (
      <div className="container">
        <div className='row'>
          <div className="col-md-offset-2 col-md-4">
            <div className="well">
              <TransactionsSummary tCount={this.transactionCount()} debits={this.debits()} credits={this.credits()}
              balance={this.balance()} total={this.totalTransactions()}/>
            </div>
          </div>
          <div className="col-md-4">
            <div className="well">
              <TransactionForm handleNewRecord={this.addRecord}/>
            </div>
          </div>
        </div>
        <div className='row'>
          <div className="col-md-offset-2 col-md-8">
            <div className="well">
              <TransactionsTable records={this.state.records}/>
            </div>
          </div>
        </div>
      </div>
    );
  }
});

Now that we have a working example that looks good, lets try to add the edit and delete options to alter the transactions. For this, we would be displaying the ‘Edit’ & ‘Delete’ buttons for each TransactionRow. When clicked on ‘Delete’ it should remove the record from table. When clicked ‘Edit’ it should load the transaction in TransactionForm for editing.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
var TransactionRow = React.createClass({
  handleDelete: function(e){
    e.preventDefault();
    this.props.handleDeleteRecord(this.props.record);
  },

  render: function(){
    return (
      <tr>
        <td>{this.props.record.date}</td>
        <td>{this.props.record.reason}</td>
        <td>{this.props.record.amount}</td>
        <td>
          <button className="btn btn-primary" >Edit</button>
          <button className="btn btn-danger" onClick={this.handleDelete}>Delete</button>
        </td>
      </tr>
    );
  }
});

var TransactionsTable = React.createClass({
  render: function(){
    var rows = [];
    this.props.records.map(function(record, index){
      rows.push(<TransactionRow key={index} record={record} handleDeleteRecord={this.props.handleDeleteRecord}/>);
    }.bind(this));
    return (
      ...
    );
  }
});

var TransactionsDetails = React.createClass({
  getInitialState: function(){
    return { records: [
      { date: '1-6-2016', reason: 'Salary', amount: 1000},
      { date: '2-6-2015', reason: 'EMI', amount: -400}
    ] };
  },

  deleteRecord: function(record){
    var records = this.state.records;
    var index  = records.indexOf(record);
    records.splice(index, 1);
    this.setState({ records: records });
  },

  render: function(){
    return (
      <TransactionsTable records={this.state.records} handleDeleteRecord={this.deleteRecord}/>
    );
  }
});

Adding the functionality to edit the transaction record is going to be tricky because we need to inform another sibling component (TransactionForm) that it needs to load data for a selected record. We need to identify whether a record being editted in the form is new or existing. Hence, we add a new state editing to TransactionForm which will be true if a record is being edited. Also, we add a state recordToEdit to TransactionsDetails. It points to record to be editted when ‘Edit’ is clicked.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
var TransactionForm = React.createClass({
  getInitialState: function(){
    return { record: { date: '', reason: '', amount: '' }, editing: false };
  },

  componentWillReceiveProps: function(nextProps) {
    this.setState({ record: nextProps.recordToEdit, editing: true });
  },
});

var TransactionRow = React.createClass({
  handleEdit: function(e){
    e.preventDefault();
    this.props.handleEditRecord(this.props.record);
  },

  render: function(){
    return (
      ...
      <button className="btn btn-primary" onClick={this.handleEdit} >Edit</button>
      ...
    )
  };
});

var TransactionsTable = React.createClass({
  render: function(){
    var rows = [];
    this.props.records.map(function(record, index){
      rows.push(<TransactionRow key={index} record={record} handleDeleteRecord={this.props.handleDeleteRecord}
      handleEditRecord={this.props.handleEditRecord}/>);
    }.bind(this));
    return (
    ...
    );
  }
});

var TransactionsDetails = React.createClass({
  getInitialState: function(){
    return {
      records: [ { date: '1-6-2016', reason: 'Salary', amount: 1000}, { date: '2-6-2015', reason: 'EMI', amount: -400} ],
      recordToEdit: { date: '', reason: '', amount: '' }
    };
  },

  editRecord: function(record){
    this.setState({ recordToEdit: record });
  },

  render: function(){
    return (
      ...
      <TransactionForm handleNewRecord={this.addRecord} recordToEdit={this.state.recordToEdit}/>
      <TransactionsTable records={this.state.records} handleDeleteRecord={this.deleteRecord}
        handleEditRecord={this.editRecord}/>
      ...
    );
  }
});

The function componentWillReceiveProps is a lifecycle function provided by React. It is called when the component is being rerendered. It has the updated props that are passed as arguments to the function and can be accessed before the component is rerendered.

Now, the last functionality that we need to add is updating a TransactionsTable. We have already implemented to load a transaction in the form so that it can be updated.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
var TransactionForm = React.createClass({
  handleChange: function(e){
    var record = this.state.record;
    switch(e.target.name){
      case 'date':
        record.date = e.target.value;
        break;
      case 'reason':
        record.reason = e.target.value;
        break;
      case 'amount':
        record.amount = e.target.value;
        break;
    }
    this.setState({ record: record });
  },

  handleSubmit: function(e){
    e.preventDefault();
    if(this.state.editing){
      this.props.handleUpdateRecord(this.state.record);
    } else {
      this.props.handleNewRecord(this.state.record);
    }
    this.setState(this.getInitialState());
  },

  render: function(){
    return (
      <form onSubmit={this.handleSubmit} className="form-horizontal">
        <h4>{this.state.editing ? 'Edit Record' : 'New Record'}</h4>
        ...
    );
  }
});

var TransactionsDetails = React.createClass({
  updateRecord: function(record){
    var records = this.state.records;
    var index = records.indexOf(record);
    records[index] = record;
    this.setState({ records: records, recordToEdit: {} });
  },

  render: function(){
    return (
      <TransactionForm handleNewRecord={this.addRecord} recordToEdit={this.state.recordToEdit}
        handleUpdateRecord={this.updateRecord}/>
    );
  }
});

Finally, we have a simple working application created using React. I know its not perfect and am definitely sure that there are some issues with it. But, our aim was to identitying, creating and linking the components. I hope the post achieved the aim. The source code can be found here.

Hope it was interesting. Critics are welcome.

Basic Concepts in React

Since last couple of days, I have been experimenting with ReactJS. I followed the official Tutorial, Thinking in React and some other blogs. This post is a summary of what I learned. I will try to keep is as simple as possible.

What is React? React is a JavaScript library to manage and render Views(V in MVC). The views are rendered by React Components.

Lets read some simple code. A simple ‘Hello React’ program.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8" />
    <title>Hello React!</title>
    <script src="build/react.js"></script>
    <script src="build/react-dom.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/babel-core/5.8.23/browser.min.js"></script>
  </head>
  <body>
    <div id="example"></div>
    <script type="text/babel">
      var HelloWorld = React.createClass({
        getInitialState: function(){
          return {message: 'Hello'}
        },

        render: function(){
          return (
            <h3>{this.state.message} {this.props.name}</h3>
          );
        }
      });

      ReactDOM.render(
        <HelloWorld name="React" />,
        document.getElementById('container')
      );
    </script>
  </body>
</html>

In the above code sample, we have a HTML page with some JS code defined inside the script tag. The JS code has some HTML too. This HTML inside JS is called JSX. Babel is a library to parse JSX. React uses JSX to render the HTML.

Some basic terminologies:

Component: A component is an entity that is supposed to do only one thing. Ideally, it should follow Single Responsibility Principle. The main task of a component is to render data(props/state). Component doesn’t inherit other Component but data(state/props) is passed form one Component(parent) to another Component(child).

state: A Component’s data that it has not inherited from the parent Component is called state.

props: Data that a Component inherits from the parent/Ancestor Component is called props(properties).

React follows unidirectional data flow. What it means is that a Component(parent) passes data(state/props) to another Component(child). When the data of the parent is altered, the child component automatically update itself.

In the javascript of the above code sample, we have 2 entities named ReactDOM and HelloWorld. In React, these are called Component. In the above code, the ReactDOM is calling the HelloWorld component passing some data(ie. name=“React”). Hence, the parent component is ReactDOM and the child component is HelloWorld. message is the state of HelloWorld whereas name is its props.

Some important points:

  • Component must implement the render function.
  • When state is passed down to child components, it becomes props for child components. ie. state of parent Component will always be props of child Component.
  • A component can alter(update/delete) it’s state since it belongs to the component. state is mutable.
  • A component should not alter(update/delete) it’s props since they don’t belong to the component. props is immutable.
  • If props are to be altered, then they should be altered in the component where they are declared. When mounting the child Component, the callbacks that need to be called to update/delete the props should be passed too.
  • React will update all the child components using the props once the parent component’s data(state) has been updated.
  • When child component receives data(props) from the parent, do not assign it to child component’s state and then use this state to render html. Use the props directly in the render function. The state is set when the component is mounted for the first time. you will have to explicitly unmount and remount the child component to rerender the update state data.
  • child Component can call parent Component’s only those methods that have been passed on to it.
  • If a state is refered in multiple components of different types, the state should be added to the most recent ancestor of the components.

We will create a simple example wherein a User can enter message(eg. ‘Hello’) and a name(eg. ‘Prasad’). The page should display the message and name.

For sake of keeping this post short and simple, I won’t be adding the entire HTML but only the necessary(updated) code. Also, 1) The code sample would contain only the changes for the components. 2) We have the code sample followed by the explanation.

1
2
3
var Greet = React.createClass({});

ReactDOM.render(<Greet />, document.getElementById('container'));

The above code sample will fail because every Component created using React.createClass must implement the render function. The detailed error can be viewed in the browser’s console.

1
2
3
4
5
6
7
8
9
10
11
var Greet = React.createClass({
  getInitialState: function(){
    return { message: 'Hello' };
  },

  render: function(){
    return (
      <h3>{this.state.message}</h3>
    );
  }
});

We updated the Greet component to set some initial value for message and defined the render function to display the message. Since, message has been defined in the same component, hence, message is Greet’s state.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
var Greet = React.createClass({
  render: function(){
    return (
      <User message={this.state.message} />
    );
  }
});

var User = React.createClass({
  render: function(){
    return (
      <h3>{this.props.message}</h3>
    );
  }
});

Here, we created a new component named User and passed the Greet’s message to the User. Since, message is passed to User, it’s User’s props.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
var User = React.createClass({
  getInitialState: function(){
    return { name: 'Prasad' };
  },

  render: function(){
    return (
      <div>
        <input type='text' name='message' placeholder='Enter message' value={this.props.message}/>
        <input type='text' name='name' placeholder='Enter Name' value={this.state.name}/>
        <h3>{this.props.message} {this.state.name}</h3>
      </div>
    );
  }
});

Here, we added a state named name to User. We are rendering the props message and state name in the User’s render function along with 2 input fields wherein User can enter the custom message and name that he/she wants to be displayed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
var User = React.createClass({
  handleNameChange: function(event){
    this.setState({ name: event.target.value });
  },

  render: function(){
    return (
      <div>
        <input type='text' placeholder='Enter message' value={this.props.message} />
        <input type='text' placeholder='Enter Name' value={this.state.name} onChange={this.handleNameChange}/>
        <h3>{this.props.message} {this.state.name}</h3>
      </div>
    );
  }
});

Here, The handleNameChange function receives the event and retrieves the updated value from the target. React provides us a function named setState using which we have updated the state name. If we update the state name without using the setState function, the updated name is not displayed. This is because the setState informs React that an state has been updated and that the DOM needs to be updated.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
var Greet = React.createClass({
  updateMessage: function(value){
    this.setState({ message: value });
  },

  render: function(){
    return (
      <User message={this.state.message} handleMessageChange={this.updateMessage}/>
    );
  }
});

var User = React.createClass({
  handleGreetChange: function(e){
    this.props.handleMessageChange(e.target.value);
  },

  render: function(){
    return (
      <div>
        <input type='text' placeholder='Enter message' value={this.props.message} onChange={this.handleGreetChange}/>
        <input type='text' placeholder='Enter Name' value={this.state.name} onChange={this.handleNameChange}/>
        <h3>{this.props.message} {this.state.name}</h3>
      </div>
    );
  }

Here, the function updateMessage will receive the updated value and will update the state message. Also, we need to pass the function to User component. When the user types the message in the textbox, the handleGreetChange function is called. This function retrieves the entered value and passes it handleGreetChange props as a parameter. Since, React implements unidirectional data flow, any components using the props message are updated with the updated value.

So, the entire code is as

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
var Greet = React.createClass({
  getInitialState: function(){
    return { message: 'Hola' };
  },

  updateMessage: function(value){
    this.setState({message: value});
  },

  render: function(){
    return (
    <User message={this.state.message} handleMessageChange={this.updateMessage}/>
    );
  }
});

var User = React.createClass({
  getInitialState: function(){
    return { name: 'Prasad' }
  },

  handleNameChange: function(e){
    this.setState({name: e.target.value});
  },

  handleGreetChange: function(e){
    this.props.handleMessageChange(e.target.value);
  },

  render: function(){
    return (
      <div>
        <input type='text' placeholder='Enter Message' value={this.props.message} onChange={this.handleGreetChange}/>
        <input type='text' placeholder='Enter Name' value={this.state.name} onChange={this.handleNameChange}/>
        <h3>
          {this.props.message} {this.state.name}
        </h3>
      </div>
    );
  }
});

ReactDOM.render(
  <Greet/>,
  document.getElementById('container')
);

The User component defined above can be optimized a bit as below

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
var User = React.createClass({
  getInitialState: function(){
    return { name: 'Prasad' }
  },

  handleNameChange: function(e){
    if(e.target.name == "message"){
      this.props.handleMessageChange(e.target.value);
    } else {
      this.setState({name: e.target.value});
    }
  },

  render: function(){
    return (
      <div>
        <input type='text' name="message" placeholder='Enter Message' value={this.props.message} onChange={this.handleGreetChange}/>
        <input type='text' name="name" placeholder='Enter Name' value={this.state.name} onChange={this.handleNameChange}/>
        <h3>
          {this.props.message} {this.state.name}
        </h3>
      </div>
    );
  }
});

Some experiments

Ok. Since we have a working example, lets try to make come changes and check if they work or not. If not, why?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
var Greet = React.createClass({
  getInitialState: function(){
    return { message: 'Hola' };
  },

  updateMessage: function(value){
    this.setState({message: value});
  },

  render: function(){
    return (
    <User message={this.state.message} handleMessageChange={this.updateMessage}/>
    );
  }
});

We didn’t pass the handleMessageChange props to the User component. Now, when we enter the message, the same is not being displayed on the page. ie. Every parent component props(state/props/function) that you want to access in the child has to be explicitly passed on to the child Component.

1
2
3
4
5
6
7
8
9
10
11
12
13
var User = React.createClass({
  render: function(){
    return (
      <div>
        <input type='text' name="message" placeholder='Enter Message' value={this.props.message} onChange={this.handleGreetChange}/>
        <input type='text' name="name" placeholder='Enter Name' value={this.state.name} onChange={this.handleNameChange}/>
        <h3>
          {this.state.some} {this.props.message} {this.state.name} {this.props.some}
        </h3>
      </div>
    );
  }
});

Here, we are accessing data(state/props) that has not been defined in any of the components. The above code will still work as expected.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
var Greet = React.createClass({
  updateMessage: function(value){
    this.setState({message: value});
    console.log('updated message: '+ this.state.message);
  }
});

var User = React.createClass({
  getInitialState: function(){
    return { name: 'Prasad', message: this.props.message }
  },

  render: function(){
    return (
      <div>
        <input type='text' placeholder='Enter Message' value={this.props.message} onChange={this.handleGreetChange}/>
        <input type='text' placeholder='Enter Name' value={this.state.name} onChange={this.handleNameChange}/>
        <h3>
          {this.state.message} {this.state.name}
        </h3>
      </div>
    );
  }
});

Here, we assigned the message props to a message state and displayed the message state. When we enter the new message in the input box, the Greet’s message state is being updated(can be viewed in browser console) but the same in not reflected on the page. This is because the getInitialState is called only when the component is mounted. Its like an initializer/constructor.

Summerizing, the post is about the the key concepts and their relations to get started with React. Hope it was interesting.

Critics are welcome.

Filtering for Json Keys as Columns in Active Admin

After implementing sorting on keys of a json column data, I need to add filtering too. We are using ActiveAdmin(1.0.0-pre1) with postgres 9.4. You can check my previous post regarding sorting.

For sake of continuity, lets take the same example as in the previous post. We have a model named Box which stores (length, width & height) as json in a column named dimensions.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
\d boxes;

Table "public.boxes"
Column   |            Type             |                     Modifiers
------------+-----------------------------+----------------------------------------------------
id         | integer                     | not null default nextval('boxes_id_seq'::regclass)
user_id    | integer                     |
dimensions | json                        |
volume     | integer                     |
created_at | timestamp without time zone | not null
updated_at | timestamp without time zone | not null
Indexes:
"boxes_pkey" PRIMARY KEY, btree (id)


# select * from boxes limit 5;
  id | user_id |             dimensions              | volume |         created_at         |         updated_at
 ----+---------+-------------------------------------+--------+----------------------------+----------------------------
   1 |       3 | {"length":4,"breadth":7,"height":3} |     84 | 2016-04-19 18:24:54.281761 | 2016-04-19 18:30:21.885756
   2 |       3 | {"length":3,"breadth":3,"height":8} |     72 | 2016-04-19 18:24:54.28487  | 2016-04-19 18:30:21.904715
   3 |       2 | {"length":1,"breadth":4,"height":3} |     12 | 2016-04-19 18:24:54.287235 | 2016-04-19 18:30:21.911726
   4 |       4 | {"length":0,"breadth":2,"height":4} |      0 | 2016-04-19 18:24:54.289777 | 2016-04-19 18:30:21.917753
   5 |       4 | {"length":7,"breadth":4,"height":0} |      0 | 2016-04-19 18:24:54.292116 | 2016-04-19 18:30:21.924538
(5 rows)

In ActiveAdmin, the filters can be displayed as below

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
ActiveAdmin.register Box do
  filter :length, as: :numeric, label: 'Length'
  filter :breadth, as: :numeric, label: 'Breadth'
  filter :height, as: :numeric, label: 'Height'
  filter :volume, as: :numeric, label: 'Volume'

  controller do
    def scoped_collection
      Box.select("*, dimensions ->> 'length' as length, dimensions ->> 'breadth' as breadth, dimensions ->> 'height' as height")
    end
  end

  index do
    column :length, label: 'Length', sortable: "dimensions->>'length'"
    column :breadth, label: 'Breadth', sortable: "dimensions->>'breadth'"
    column :height, label: 'Height', sortable: "dimensions->>'height'"
    column :volume, label: 'Volume', sortable: true
  end
end

Since some of the fields are not associated with the table, we need need to define custom ransackers for them. ActiveAdmin uses ransack gem for implementing searching.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
class Box < ActiveRecord::Base
  ransacker :length do |parent|
    # '->>' is a part of the syntax for accessing the json keys, 'dimensions' the column name and 
    # 'length' is the actual key on which the search is to be implemented.
    Arel::Nodes::InfixOperation.new('->>', parent.table[:dimensions], Arel::Nodes.build_quoted('length'))
  end

  ransacker :breadth do |parent|
    Arel::Nodes::InfixOperation.new('->>', parent.table[:dimensions], Arel::Nodes.build_quoted('breadth'))
  end

  ransacker :height do |parent|
    Arel::Nodes::InfixOperation.new('->>', parent.table[:dimensions], Arel::Nodes.build_quoted('height'))
  end


  # The below ransackers do not work. Also, we need to declare three ransackers :length_equals, :length_greater_than & 
  # :length_less_than for each field. Dont know the reason but am looking into it.

  ransacker :length_greater_than, formatter: -> (value){
    data = where("dimensions ->> 'length' > '#{value}'").pluck :id
    data = data.present? ? data : nil
  } do |parent|
    parent.table[:id]
  end
end

This still won’t work. The filters are displayed weirdly(dropdown on top and search input field at the bottom) rather in the expected layout(dropdown on left and search input field on the right). This is because ActiveAdmin adds a class select_and_search which decides if a filter field is searchable or not. This class is responsible for the proper layout and working of filters. The below code is referenced from ActiveAdmin. It decides whether the class is to be added or not when the filter is initialized.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
module ActiveAdmin
  module Inputs
    module Filters
      module Base
        module SearchMethodSelect
          def wrapper_html_options
            opts = super
              (opts[:class] ||= '') << ' select_and_search' unless seems_searchable?
            opts
          end
        end
      end
    end
  end
end

module ActiveAdmin
  module Filters
    module FormtasticAddons
      def seems_searchable?
        # ransacker? checks if any custom ransackers are defined. 
        has_predicate? || ransacker? || scope?
      end
    end
  end
end

So, we remove the check for ransacker? by adding a patch in the initializers.

1
2
3
4
5
6
7
8
9
10
11
# config/initializers/hack_for_filter.rb

module ActiveAdmin
  module Filters
    module FormtasticAddons
      def seems_searchable?
        has_predicate? || scope?
      end
    end
  end
end

Now, the filters will be displayed properly and would send the selected option(Greater than, Equals or Less than) with the specified value to the ransackers.

I am not inclined towards overriding the library methods but haven’t found any other solution for the same. So, for now, this it the hack that works for me.

Working source code can be found here. Hope it was interesting.

Sorting for Json Keys as Column in Active Admin

Recently, I had a requirement wherein I need to have sorting on the keys of a json column data. We are using postgres 9.4 and the schema of the json data is static for all the records. The keys needed to be displayed as columns with sorting enabled against them. Below is the way I implemented it. Also, on digging into ActiveAdmin, I learned some interesting implementation details of the library.

Lets take an example. We have a model named Box which stores (length, width & height) as json in a column named dimensions.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
\d boxes;

Table "public.boxes"
  Column   |            Type             |                     Modifiers
------------+-----------------------------+----------------------------------------------------
id         | integer                     | not null default nextval('boxes_id_seq'::regclass)
user_id    | integer                     |
dimensions | json                        |
volume     | integer                     |
created_at | timestamp without time zone | not null
updated_at | timestamp without time zone | not null
Indexes:
"boxes_pkey" PRIMARY KEY, btree (id)


# select * from boxes limit 5;
  id | user_id |             dimensions              | volume |         created_at         |         updated_at
 ----+---------+-------------------------------------+--------+----------------------------+----------------------------
   1 |       3 | {"length":4,"breadth":7,"height":3} |     84 | 2016-04-19 18:24:54.281761 | 2016-04-19 18:30:21.885756
   2 |       3 | {"length":3,"breadth":3,"height":8} |     72 | 2016-04-19 18:24:54.28487  | 2016-04-19 18:30:21.904715
   3 |       2 | {"length":1,"breadth":4,"height":3} |     12 | 2016-04-19 18:24:54.287235 | 2016-04-19 18:30:21.911726
   4 |       4 | {"length":0,"breadth":2,"height":4} |      0 | 2016-04-19 18:24:54.289777 | 2016-04-19 18:30:21.917753
   5 |       4 | {"length":7,"breadth":4,"height":0} |      0 | 2016-04-19 18:24:54.292116 | 2016-04-19 18:30:21.924538
(5 rows)

In ActiveAdmin, columns can be displayed for non-table fields. All you have to do is project them in the query. In the below example, length, breadth and height are not columns of boxes but have been projected in the select query.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
ActiveAdmin.register Box do
  controller do
    def scoped_collection
      Box.select("*, dimensions ->> 'length' as length, dimensions ->> 'breadth' as breadth, dimensions ->> 'height' as height")
    end
  end

  index do
    column :length, label: 'Length', sortable: "dimensions->>'length'"
    column :breadth, label: 'Breadth', sortable: "dimensions->>'breadth'"
    column :height, label: 'Height', sortable: "dimensions->>'height'"
    column :volume, label: 'Volume', sortable: true
  end
end

When defining the columns to be displayed, we pass the query by which the json key would be accessed when sorting is applied. By default ActiveAdmin appends _asc and _desc to the table columns when sorting. Here, we pass the query overriding the default column name.

ActiveAdmin has a method named apply_sorting which check if the order is valid or not and calls the ordering query.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# When sorting the breadth in descending order, the params sent to the controller are as below.
# {"order"=>"dimensions->>'length'_desc", "controller"=>"admin/boxes", "action"=>"index"}

def apply_sorting(chain)
  params[:order] ||= active_admin_config.sort_order #default order for sorting which is 'id_desc'

  orders = []
    params[:order].split('_and_').each do |fragment|
    order_clause = ActiveAdmin::OrderClause.new(fragment) #dimensions->>'breadth'_desc
    # <ActiveAdmin::OrderClause:0x007fad6dfe @column="dimensions->>'length'", @field="dimensions->>'length'", 
    # @op=nil, @order="desc">
    if order_clause.valid? #check if field.present? and order.present?
      orders << order_clause.to_sql(active_admin_config)
    end
  end

  if orders.empty?
    chain
  else
    chain.reorder(orders.shift).order(orders)
    # executes SELECT  *, dimensions ->> 'length' as length, dimensions ->> 'breadth' as breadth, 
    # dimensions ->> 'height' as height 
    # FROM "boxes"  ORDER BY "boxes".dimensions->>'length' desc;
  end
end

The last change that we need is to rewrite the ActiveAdmin::OrderClause. The OrderClause parses the field and order string(say, volume_desc or dimensions->>‘length’_desc) and splits it into the field name, operation and the order thats to be applied.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
# Original code
module ActiveAdmin
  class OrderClause
    attr_reader :field, :order

    def initialize(clause)
      clause =~ /^([\w\_\.]+)(->'\w+')?_(desc|asc)$/
      @column = $1
      @op = $2
      @order = $3

      @field = [@column, @op].compact.join
    end

    def valid?
      @field.present? && @order.present?
    end

    def to_sql(active_admin_config)
      table = active_admin_config.resource_column_names.include?(@column) ? active_admin_config.resource_table_name : nil
      table_column = (@column =~ /\./) ? @column : [table, active_admin_config.resource_quoted_column_name(@column)].compact.join(".")
      [table_column, @op, ' ', @order].compact.join
    end
  end
end

# Updated code in config/initializers/hacked_order_clause.rb
module ActiveAdmin
  class OrderClause
    attr_reader :field, :order

    def initialize(clause)
      clause =~ /^([\w\_\.]+)(->'\w+')?_(desc|asc)$|^([\w\_\.]+->>'[\w\_]+')(->'\w+')?_(desc|asc)$/
      @column = $1 || $4
      @op = $2 || $5
      @order = $3 || $6

      @field = [@column, @op].compact.join
    end

    def valid?
      @field.present? && @order.present?
    end

    def to_sql(active_admin_config)
      table = column_in_table?(active_admin_config.resource_column_names, @column) ? active_admin_config.resource_table_name : nil
      table_column = (@column =~ /\./) ? @column : [
        table, json_column?(@column) ? @column : active_admin_config.resource_quoted_column_name(@column)
      ].compact.join(".")
      [table_column, @op, ' ', @order].compact.join
    end

    private

    def json_column?(column)
      column.include?('->>')
    end

    def
      column_in_table?(names, column)
      column = json_column?(column) ? column.split('->>')[0].strip : column
      names.include?(column)
    end
  end
end

Working source code can be found here. Hope it was interesting.