Databases
StackOverflow2013
Postsdbo
POrails Active record selection
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POrails Active record selection
    primarykey
    data
    text
    <p>Q: all post and related comments selection in one query, loading comments relation into p.comments, but select only title from comments</p> <p>Post</p> <pre><code>#db create_table :posts do |t| t.string :title t.text :text t.timestamps end #modlel class Post &lt; ActiveRecord::Base has_many :comments end </code></pre> <p>Comment</p> <pre><code>#db create_table :comments do |t| t.string :title t.text :text t.integer :post_id t.timestamps end #model class Comment &lt; ActiveRecord::Base belongs_to :post end </code></pre> <p>What I need: Select all post and related comments in one query and load comments relation into p.comments, but select only title from comments:</p> <pre><code>posts = Post.includes(:comments).select("posts.*, comments.title") posts.each do |p| puts(p.title) puts(p.text) p.comments.each do |c| puts(c.title) end end </code></pre> <p>If first part is done by .includes:</p> <pre><code>posts = Post.includes(:comments).select('posts.*, comments.title') posts.first.comments.loaded? # true </code></pre> <p>=> SELECT <code>posts</code>.<code>id</code> AS t0_r0, <code>posts</code>.<code>title</code> AS t0_r1, <code>posts</code>.<code>text</code> AS t0_r2, <code>posts</code>.<code>created_at</code> AS t0_r3, <code>posts</code>.<code>updated_at</code> AS t0_r4, <code>comments</code>.<code>id</code> AS t1_r0, <code>comments</code>.<code>title</code> AS t1_r1, <code>comments</code>.<code>text</code> AS t1_r2, <code>comments</code>.<code>post_id</code> AS t1_r3, <code>comments</code>.<code>created_at</code> AS t1_r4, <code>comments</code>.<code>updated_at</code> AS t1_r5 FROM <code>posts</code> LEFT OUTER JOIN <code>comments</code> ON <code>comments</code>.<code>post_id</code> = <code>posts</code>.<code>id</code></p> <p>its completely ignores select.</p> <p>Ok, lets try joins:</p> <pre><code>Post.joins(:comments).select('posts.*, comments.title') </code></pre> <p>=> SELECT posts.*, comments.title FROM <code>posts</code> INNER JOIN <code>comments</code> ON <code>comments</code>.<code>post_id</code> = <code>posts</code>.<code>id</code></p> <p>better, but we need table alias -- Rails doesn't handle relation (why?)</p> <pre><code>posts = Post.joins(:comments).select('posts.*, comments.title as comments_title').first </code></pre> <p>=> SELECT posts.*, comments.title as comments_title FROM <code>posts</code> INNER JOIN <code>comments</code> ON <code>comments</code>.<code>post_id</code> = <code>posts</code>.<code>id</code></p> <pre><code>posts.first.comments.loaded? # false posts.first.comments_title # "Comment 1.1" </code></pre> <p>Ok, lets try ARel</p> <pre><code>p = Post.arel_table c = Comment.arel_table posts = Post.find_by_sql( p.join(c).on(c[:post_id].eq(p[:id])) \ .project(p[:id],p[:title],c[:id],c[:title]).to_sql ) </code></pre> <p>=> SELECT <code>posts</code>.<code>id</code>, <code>posts</code>.<code>title</code>, <code>comments</code>.<code>id</code>, <code>comments</code>.<code>title</code> FROM <code>posts</code> INNER JOIN <code>comments</code> ON <code>comments</code>.<code>post_id</code> = <code>posts</code>.<code>id</code></p> <p>same story -- ActiveRecord doesn't handle relation.</p> <p>any suggestions?</p> <p><strong>UPD</strong></p> <ol> <li><p>if this question hasn't direct solution, maybe there is a way to use some AR methods to convert resulting query. Because, AR uses ARel, then call find_by_sql on it and then do some magic, that I cant find when and were its performed, and viola: generated aliases and loading associations.</p></li> <li><p>Maybe there is solution for one-to-one relation?</p> <p>posts = Post.select('posts.id,posts.title,comments.id,comments.title').includes(:comment) posts.first.comment.loaded? #true</p></li> </ol> <p>=> SELECT <code>posts</code>.<code>id</code> AS t0_r0, <code>posts</code>.<code>title</code> AS t0_r1, <code>posts</code>.<code>text</code> AS t0_r2, <code>posts</code>.<code>created_at</code> AS t0_r3, <code>posts</code>.<code>updated_at</code> AS t0_r4, <code>comments</code>.<code>id</code> AS t1_r0, <code>comments</code>.<code>title</code> AS t1_r1, <code>comments</code>.<code>text</code> AS t1_r2, <code>comments</code>.<code>post_id</code> AS t1_r3, <code>comments</code>.<code>created_at</code> AS t1_r4, <code>comments</code>.<code>updated_at</code> AS t1_r5 FROM <code>posts</code> LEFT OUTER JOIN <code>comments</code> ON <code>comments</code>.<code>post_id</code> = <code>posts</code>.<code>id</code></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAlexParamonov
    1. USAlexParamonov
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    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. POrails Active record selection
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POrails Active record selection
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POrails Active record selection
      1. USAlexParamonov
      1. VTFavorite
    1. This table or related slice is empty.
 

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