Databases
StackOverflow2013
Postsdbo
POHow would you do this Rails sub-query using Squeel?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow would you do this Rails sub-query using Squeel?
    primarykey
    data
    text
    <p>I want to restructure the query below using Squeel. I'd like to do this so that I can chain the operators in it and re-use the logic in the different parts of the query. </p> <pre><code>User.find_by_sql("SELECT users.*, users.computed_metric, users.age_in_seconds, ( users.computed_metric / age_in_seconds) as compound_computed_metric from ( select users.*, (users.id *2 ) as computed_metric, (extract(epoch from now()) - extract(epoch from users.created_at) ) as age_in_seconds from users ) as users") </code></pre> <p>The query has to all operate in the DB and should not be a hybrid Ruby solution since it has to order and slice millions of records.</p> <p>I've set the problem up so that it should run against a normal <code>user</code> table and so that you can play with the alternatives to it. </p> <h2>Restrictions on an acceptable answer</h2> <ul> <li>the query should return a <code>User</code> object with all the normal attributes</li> <li>each user object should also include <code>extra_metric_we_care_about</code>, <code>age_in_seconds</code> and <code>compound_computed_metric</code></li> <li>the query should not duplicate any logic by just printing out a string in multiple places - I want to avoid doing the same thing twice</li> <li>[updated] The query should all be do-able in the DB so that a result set that may consist of millions of records can be ordered and sliced in the DB before returning to Rails</li> <li>[updated] The solution should work for a Postgres DB</li> </ul> <h2>Example of the type of solution I'd like</h2> <p>The solution below doesn't work but it shows the type of elegance that I'm hoping to achieve</p> <pre><code>class User &lt; ActiveRecord::Base # this doesn't work - it just illustrates what I want to achieve def self.w_all_additional_metrics select{ ['*', computed_metric, age_in_seconds, (computed_metric / age_in_seconds).as(compound_computed_metric)] }.from{ User.w.compound_computed_metric.w_age_in_seconds } end def self.w_computed_metric where{ '(id *2 ) as computed_metric' } end def self.w_age_in_seconds where{ '(extract(epoch from now()) - extract(epoch from created_at) ) as age_in_seconds' } end end </code></pre> <h3>You should be able to run this against your existing database</h3> <p><em>Please note that I've somewhat contrived the problem so that you can use your existing <code>User</code> class and play with it in your console.</em></p> <h2>EDIT</h2> <ol> <li>The DB I'm using is Postgres.</li> <li>I'm not sure I made it 100% clear that the query should all execute in the DB. It can't be a hybrid answer were some of the logic is essentially done in Rails. This is important since I want to be able to order and slice millions of records using the computed columns. </li> </ol>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPeter Nixey
    1. USPeter Nixey
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow would you do this Rails sub-query using Squeel?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow would you do this Rails sub-query using Squeel?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow would you do this Rails sub-query using Squeel?
      1. USPeter Nixey
      1. VTBountyStart
    1. COJust lobbing softballs my way... how hard could solving this be... ugh! The race is on to get the bounty!
      singulars
      1. POHow would you do this Rails sub-query using Squeel?
      1. USerroric
    2. COI'm not sure that this is actually possible without violating your third restriction on an acceptable answer. In case you haven't already had a look, I've taken steps towards something similar [here](http://stackoverflow.com/questions/15412142/ranking-results-with-complex-conditions-using-rails-and-squeel) and [here](http://stackoverflow.com/questions/15274910/multiple-joins-to-the-same-model-using-multiple-belongs-to-associations).
      singulars
      1. POHow would you do this Rails sub-query using Squeel?
      1. USerroric
    3. COI don't get it. What you described can be simply solved without any fancy squeel chaining. There is no need to use a sub-query. The compound_computed_metric is calculated against each row in the table, and there is no aggregation.
      singulars
      1. POHow would you do this Rails sub-query using Squeel?
      1. USwanghq
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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