Ruby China

新手问题 rails 怎么用关联的表里面的字段排序？

kikyous · 2012年06月26日 · 最后由 yangman_wenzhu 回复于 2014年04月22日 · 7970 次阅读

Videos 表

class Video < ActiveRecord::Base
   attr_accessible :name , :cid
   has_one :videodatum
end

Videodata 表

class Videodatum < ActiveRecord::Base
  attr_accessible :hits
  belongs_to :video
end

e 是数组 取出 video 中 cid 在 e 中的记录，并按 Videodata 中的 hits 排序 首先想到的是

Video.includes(:videodatum).where(:cid => e).order('videodatum.hits DESC').limit(10)

不行

Video.joins('LEFT OUTER JOIN videodata ON videodata.video_id = videos.id').where(:cid => e).order('hits DESC').limit(10)

还是不行，怎么办

Rei #1 2012年06月26日

videodatum*s*

kikyous #2 2012年06月26日

#1 楼 @Rei 他的复数是 videodata

Rei #3 2012年06月26日

第一句报了什么错？

Video.includes(:videodatum).where(:cid => e).order('videodata.hits DESC').limit(10)
kikyous #4 2012年06月26日

#3 楼 @Rei 没有 videodata.hits 这个字段

kikyous #8 2012年06月26日

#7 楼 @Rei undefined method `id' for #ActiveRecord::Relation:0xb66472f4

kikyous #9 2012年06月26日

#7 楼 @Rei 我看了一下，返回的数据现在是正常的，但是不知道为什么不用 id

Rei #10 2012年06月26日

#9 楼 @kikyous Relation 是一个查询对象，to_a 执行返回数组，first 拿第一个数据

kikyous #11 2012年06月26日

#10 楼 @Rei 嗯，现在好了

Started GET "/" for 127.0.0.1 at 2012-06-26 20:32:19 +0800
Processing by VideosController#index as HTML
  Categorgy Load (2.1ms)  SELECT `categorgies`.* FROM `categorgies` ORDER BY cup
  Rendered videos/_toolbar.html.erb (0.1ms)
  Video Load (5.8ms)  SELECT `videos`.* FROM `videos` WHERE `videos`.`cid` IN (5, 6, 7, 8, 9, 10, 11) ORDER BY lastdate DESC LIMIT 5
  Videodatum Load (1.6ms)  SELECT `videodata`.* FROM `videodata` WHERE `videodata`.`video_id` IN (1041, 1039, 1036, 1000, 1037)
  Video Load (4.8ms)  SELECT `videos`.* FROM `videos` WHERE `videos`.`cid` IN (13, 15, 16, 17, 37) ORDER BY lastdate DESC LIMIT 5
  Videodatum Load (1.8ms)  SELECT `videodata`.* FROM `videodata` WHERE `videodata`.`video_id` IN (1033, 941, 943, 942, 929)
  Video Load (6.7ms)  SELECT `videos`.* FROM `videos` WHERE `videos`.`cid` IN (32, 20, 30) ORDER BY lastdate DESC LIMIT 5
  Videodatum Load (1.5ms)  SELECT `videodata`.* FROM `videodata` WHERE `videodata`.`video_id` IN (776, 672, 947, 930, 866)
  Video Load (3.7ms)  SELECT `videos`.* FROM `videos` WHERE `videos`.`cid` IN (29, 33, 34) ORDER BY lastdate DESC LIMIT 5
  Videodatum Load (1.5ms)  SELECT `videodata`.* FROM `videodata` WHERE `videodata`.`video_id` IN (1040, 491, 925, 924, 923)
  SQL (34.0ms)  SELECT `videos`.`id` AS t0_r0, `videos`.`cid` AS t0_r1, `videos`.`nid` AS t0_r2, `videos`.`author` AS t0_r3, `videos`.`authorid` AS t0_r4, `videos`.`postdate` AS t0_r5, `videos`.`lastdate` AS t0_r6, `videos`.`name` AS t0_r7, `videos`.`picfolder` AS t0_r8, `videos`.`pic` AS t0_r9, `videos`.`playactor` AS t0_r10, `videos`.`director` AS t0_r11, `videos`.`tag` AS t0_r12, `videos`.`year` AS t0_r13, `videos`.`description` AS t0_r14, `videos`.`best` AS t0_r15, `videos`.`serialise` AS t0_r16, `videos`.`memo` AS t0_r17, `videos`.`yz` AS t0_r18, `videodata`.`video_id` AS t1_r0, `videodata`.`hits` AS t1_r1, `videodata`.`reply` AS t1_r2, `videodata`.`sale` AS t1_r3, `videodata`.`need` AS t1_r4, `videodata`.`usernth` AS t1_r5, `videodata`.`fraction` AS t1_r6, `videodata`.`star` AS t1_r7 FROM `videos` LEFT OUTER JOIN `videodata` ON `videodata`.`video_id` = `videos`.`id` WHERE `videos`.`cid` IN (5, 6, 7, 8, 9, 10, 11) ORDER BY videodata.hits DESC LIMIT 10
  SQL (11.6ms)  SELECT `videos`.`id` AS t0_r0, `videos`.`cid` AS t0_r1, `videos`.`nid` AS t0_r2, `videos`.`author` AS t0_r3, `videos`.`authorid` AS t0_r4, `videos`.`postdate` AS t0_r5, `videos`.`lastdate` AS t0_r6, `videos`.`name` AS t0_r7, `videos`.`picfolder` AS t0_r8, `videos`.`pic` AS t0_r9, `videos`.`playactor` AS t0_r10, `videos`.`director` AS t0_r11, `videos`.`tag` AS t0_r12, `videos`.`year` AS t0_r13, `videos`.`description` AS t0_r14, `videos`.`best` AS t0_r15, `videos`.`serialise` AS t0_r16, `videos`.`memo` AS t0_r17, `videos`.`yz` AS t0_r18, `videodata`.`video_id` AS t1_r0, `videodata`.`hits` AS t1_r1, `videodata`.`reply` AS t1_r2, `videodata`.`sale` AS t1_r3, `videodata`.`need` AS t1_r4, `videodata`.`usernth` AS t1_r5, `videodata`.`fraction` AS t1_r6, `videodata`.`star` AS t1_r7 FROM `videos` LEFT OUTER JOIN `videodata` ON `videodata`.`video_id` = `videos`.`id` WHERE `videos`.`cid` IN (13, 15, 16, 17, 37) ORDER BY videodata.hits DESC LIMIT 10
  SQL (16.7ms)  SELECT `videos`.`id` AS t0_r0, `videos`.`cid` AS t0_r1, `videos`.`nid` AS t0_r2, `videos`.`author` AS t0_r3, `videos`.`authorid` AS t0_r4, `videos`.`postdate` AS t0_r5, `videos`.`lastdate` AS t0_r6, `videos`.`name` AS t0_r7, `videos`.`picfolder` AS t0_r8, `videos`.`pic` AS t0_r9, `videos`.`playactor` AS t0_r10, `videos`.`director` AS t0_r11, `videos`.`tag` AS t0_r12, `videos`.`year` AS t0_r13, `videos`.`description` AS t0_r14, `videos`.`best` AS t0_r15, `videos`.`serialise` AS t0_r16, `videos`.`memo` AS t0_r17, `videos`.`yz` AS t0_r18, `videodata`.`video_id` AS t1_r0, `videodata`.`hits` AS t1_r1, `videodata`.`reply` AS t1_r2, `videodata`.`sale` AS t1_r3, `videodata`.`need` AS t1_r4, `videodata`.`usernth` AS t1_r5, `videodata`.`fraction` AS t1_r6, `videodata`.`star` AS t1_r7 FROM `videos` LEFT OUTER JOIN `videodata` ON `videodata`.`video_id` = `videos`.`id` WHERE `videos`.`cid` IN (32, 20, 30) ORDER BY videodata.hits DESC LIMIT 10
  SQL (15.6ms)  SELECT `videos`.`id` AS t0_r0, `videos`.`cid` AS t0_r1, `videos`.`nid` AS t0_r2, `videos`.`author` AS t0_r3, `videos`.`authorid` AS t0_r4, `videos`.`postdate` AS t0_r5, `videos`.`lastdate` AS t0_r6, `videos`.`name` AS t0_r7, `videos`.`picfolder` AS t0_r8, `videos`.`pic` AS t0_r9, `videos`.`playactor` AS t0_r10, `videos`.`director` AS t0_r11, `videos`.`tag` AS t0_r12, `videos`.`year` AS t0_r13, `videos`.`description` AS t0_r14, `videos`.`best` AS t0_r15, `videos`.`serialise` AS t0_r16, `videos`.`memo` AS t0_r17, `videos`.`yz` AS t0_r18, `videodata`.`video_id` AS t1_r0, `videodata`.`hits` AS t1_r1, `videodata`.`reply` AS t1_r2, `videodata`.`sale` AS t1_r3, `videodata`.`need` AS t1_r4, `videodata`.`usernth` AS t1_r5, `videodata`.`fraction` AS t1_r6, `videodata`.`star` AS t1_r7 FROM `videos` LEFT OUTER JOIN `videodata` ON `videodata`.`video_id` = `videos`.`id` WHERE `videos`.`cid` IN (29, 33, 34) ORDER BY videodata.hits DESC LIMIT 10
  Rendered videos/_sidebar.html.erb (119.0ms)
  Rendered videos/index.html.erb within layouts/application (410.0ms)
Completed 200 OK in 532ms (Views: 286.4ms | ActiveRecord: 154.0ms)
[2012-06-26 20:32:20] WARN  Could not determine content-length of response body. Set content-length of the response or set Response#chunked = true
kikyous #12 2012年06月26日 
@categ.each_with_index do |e,i|
  if i != 0
    @videos << Video.includes(:videodatum).where(:cid => e).order('lastdate DESC').limit(5)
    # @tops << Video.joins('LEFT OUTER JOIN videodata ON videodata.video_id = videos.id').where(:cid => e).order('hits DESC').limit(10)
    @tops << Video.includes(:videodatum).where(:cid => e).order('videodata.hits DESC').limit(10)

  end
end
yangman_wenzhu #13 2014年04月22日

有没有尝试 

Video.find(:all, :joins => "LEFT JOIN videodatum vd ON vd.video_id = video.id", :conditions => ["cid in (?)", e], :order => "videodata.hits DESC")
需要 登录 后方可回复, 如果你还没有账号请 注册新账号