Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The question is why would you need a "nested query"? We do not need to use "nested queries" this is thinking in the mindset of SQL not Relational Algebra. With relational algebra we derive relations and use the output of one relation as input to another so the following would hold true:</p> <pre><code>points = Table(:points, {:as =&gt; 'sorted'}) # rename in the options hash final_points = points.order('timestamp DESC').group(:client_id, :timestamp).project(:client_id, :timestamp) </code></pre> <p>It's best if we leave the renaming to arel unless absolutely necessary.</p> <p>Here the projection of client_id AND timestamp is VERY important since we cannot project all domains from the relation (i.e. sorted.*). You must specifically project all domains that will be used within the grouping operation for the relation. The reason being is there is no value for * that would be distinctly representative of a grouped client_id. For instance say you have the following table</p> <pre><code>client_id | score ---------------------- 4 | 27 3 | 35 2 | 22 4 | 69 </code></pre> <p>Here if you group you could not perform a projection on the score domain because the value could either be 27 or 69 but you could project a sum(score)</p> <p>You may only project the domain attributes that have unique values to the group (which are usually aggregate functions like sum, max, min). With your query it would not matter if the points were sorted by timestamp because in the end they would be grouped by client_id. the timestamp order is irrelevant since there is no single timestamp that could represent a grouping.</p> <p>Please let me know how I can help you with Arel. Also, I have been working on a learning series for people to use Arel at its core. The first of the series is at <a href="http://Innovative-Studios.com/#pilot" rel="noreferrer">http://Innovative-Studios.com/#pilot</a> I can tell you are starting to know how to since you used Table(:points) rather than the ActiveRecord model Point.</p>
    singulars
    1. This table or related slice is empty.
    1. PONested queries in Arel
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USSnuggs
    1. USSnuggs
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you for the detailed response. "the timestamp order is irrelevant since there is no single timestamp that could represent a grouping." You're right; I see what you are saying. It appears MySQL works around this inconsistency by returning just the first row of the client_id group, which is what I was aiming for. I see now this is not behavior I should count on. My goal is to return the most recent point for all client_ids, i.e. a single point with the maximum timestamp per each client_id grouping. It is important to do in one query because it will be polled often.
      singulars
      1. PO
      1. USSchrockwell
    2. COWe would need to use some aggregate function. If we ask ourselves "What are we trying to do?" The answer would be to find the most recent or "maximum" date so we would pass max(timestamp) in sql. This would correspond to Arel::Attribute::Expression::Maximum which can be called with syntactic sugar on an Arel::Attribute like sorted[:timestamp].maximum() . There is one caveat. Make sure you add timestamp to group operation #group('client_id, timestamp') or the entire grouping scenario will error. I know the MAX aggregate function works on dates in Postgres and I'm sure in MySQL as well.
      singulars
      1. PO
      1. USSnuggs
    3. COFirstly, sorting and order is not part of relational algebra. Arel defines it anyway. Secondly, whether or not subqueries are part of relational algebra is irrelevant. Conceptually, the result of a SELECT is not visible until the WHERE clause executes. Therefore not all databases (eg. Postgres) allow column aliases in WHERE clauses and instead depend on subqueries. If Arel cannot handle subqueries then names in the WHERE clause cannot be aliased. This can get messy when you can't depend on Arel to generate names.
      singulars
      1. PO
      1. USSamuel Danielson
 

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