Databases
StackOverflow2013
Postsdbo
PORuby: Return data grouped by multiple columns
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORuby: Return data grouped by multiple columns
    text
    copied!<p>I'm trying group data for a web service.</p> <p>The web service is running on Ruby on Rails and I'm working in my API controller (lets call it the index action of my projects_controller.</p> <p>The table schema looks like this (the data types and example has been changed for NDA reasons). Unfortunately, the example here suggests that I break employee and projects into different tables, but please overlook that for now. This is the data that I am given:</p> <pre><code>COLUMNS: employee, e_id, company, hire_date, project_name, project_due_date ROWS: John, 12345, XYZ, 01-01-2001, Project_A, 12-31-2012 John, 12345, XYZ, 01-01-2001, Project_B, 03-15-2013 John, 12345, XYZ, 01-01-2001, Project_C, 06-25-2013 Jane, 98765, XYZ, 05-22-2003, Project_Q, 01-15-2013 Jane, 98765, XYZ, 05-22-2003, Project_W, 02-25-2013 Jane, 98765, XYZ, 05-22-2003, Project_E, 08-01-2013 </code></pre> <p>In order to reduce data transfer, I would like to return the above as follows:</p> <pre><code>[ { "employee":"John", "e_id":"12345", "company":"XYZ", "hire_date":"01-01-2001", "projects":[ { "project_name":"Project_A", "project_due_date":"12-31-2012" }, { "project_name":"Project_B", "project_due_date":"03-15-2013" }, { "project_name":"Project_C", "project_due_date":"06-25-2013" } ] }, { "employee":"Jane", "e_id":"98765", "company":"XYZ", "hire_date":"05-22-2003", "projects":[ { "project_name":"Project_Q", "project_due_date":"01-15-2013" }, { "project_name":"Project_W", "project_due_date":"02-25-2013" }, { "project_name":"Project_E", "project_due_date":"08-01-2013" } ] } ] </code></pre> <p>I can't seem to figure out the best way to group my SQL query results (rows) into the organized hash(es) that I have in the ideal data. I imagine I need some .each and hashes to post-process the data returned by my SQL call, but I can't seem to figure out the "Ruby" way (I'm also not a seasoned Ruby developer, so any reference links would also be appreciated so I can read up on the solution).</p> <p>How can I accomplish this?</p> <p>[EDIT]</p> <p>I am performing a SQL query on the Project object. My controller is as follows:</p> <pre><code>def index sql = "SELECT employee, e_id, company, hire_date, project_name, project_due_date FROM projects AND created_at = (SELECT created_at FROM projects ORDER BY created_at DESC LIMIT 1) ORDER BY company, employee, project_due_date" result = Project.find_by_sql(sql) respond_with(result) end </code></pre> <p>The data I am getting back is a bunch of Project objects in the following format</p> <p>RUBY DEBUGGER:</p> <pre><code>(rdb:2) result [#&lt;Project employee: "John", e_id: 12345, company: "XYZ", hire_date: "01-01-2001", project_name: "Project_A", project_due_date: "12-31-2012"&gt;, #&lt;Project employee: "John", e_id: 12345, company: "XYZ", hire_date: "01-01-2001", project_name: "Project_B", project_due_date: "03-15-2013"&gt;, #&lt;Project employee: "John", e_id: 12345, company: "XYZ", hire_date: "01-01-2001", project_name: "Project_C", project_due_date: "06-25-2013"&gt;, #&lt;Project employee: "Jane", e_id: 98765, company: "XYZ", hire_date: "05-22-2003", project_name: "Project_Q", project_due_date: "01-15-2013"&gt;, #&lt;Project employee: "Jane", e_id: 98765, company: "XYZ", hire_date: "05-22-2003", project_name: "Project_W", project_due_date: "02-25-2013"&gt;, #&lt;Project employee: "Jane", e_id: 98765, company: "XYZ", hire_date: "05-22-2003", project_name: "Project_E", project_due_date: "08-01-2013"&gt;] </code></pre> <p>[EDIT 2]</p> <p>I know I can resolve this problem in a very naive, non-Ruby way, but I'd like to know the proper way to get it working. A basic solution could consist of iterating through the result array and parsing out the data row by row, saving the employee data to a temp hash and their project data to an array of hashes. When the iteration comes to a new employee, save the data for the previous employee data in an array and reset the temp array/hashes for the next employee. Very ugly, but very possible.</p> <p>However, there MUST be a Ruby way. Please help!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload