ActiveRecord的嵌套的SELECT - 我能做到这一点，无需人工SQL？ [英] ActiveRecord nested SELECT -- can I do it without manual SQL?

问题描述

我有一个表（除其他事项外）的名称和等级。我想返回集合的所有独特的名字，但每个名称回来，我想挑一行最高等级。这就是简单的用两个嵌套的SELECT语句：

  SELECT * FROM（SELECT * FROM foo的ORDER BY级DESC）AS订购GROUP BY名称
 

MySQL的需要第一打每个名称，它（因为早期ORDER BY的）永远是排名最高的之一。

现在，如果我想连线到这个表的ActiveRecord，我在一个小的损失的。我可以扔到上述的find_by_sql ，但这只是觉得脏。我想是这样

 的结果= foo.all
result.delete_if做|项目|
  isOutranked = FALSE
  result.each做|行|
    如果（row.name == item.name）和（row.rank＆GT; item.rank）则isOutranked =真
  结束
  isOutranked
结束
 

我觉得和的，但它仍然感觉应该有一个更好的办法。或者通过ActiveRecord的挂羊头卖狗肉或更优雅的数组操作解决它会受到欢迎！

解决方案

  

MySQL的需要第一打每个名称，它（因为早期ORDER BY的）永远是排名最高的之一。

您使用返回该组中的最上面一行查询的没有的保证。这只是执行情况的巧合，它是随时可能更改。 不要依赖此。

正在试图解决最大的正每组问题我看到张贴在计算器频繁。下面是更可靠地得到答案的查询：

  SELECT T1。*
一个从Foo为T1
LEFT OUTER JOIN富为T2
 ON（t1.name = t2.name和t1.rank＆LT; t2.rank）
WHERE t2.name IS NULL;
 

替代方案，做同样的事情：

  SELECT *
一个从Foo为T1
WHERE NOT EXISTS
    （SELECT * FROM FOO为T2
     WHERE t1.name = t2.name和t1.rank＆LT; t2.rank）;
 

  

我可以扔到上述的find_by_sql ，但这只是觉得脏。

ActiveRecord的是非常方便的某些类型的查询，但不能解决与ActiveRecord的每一个数据库查询。你越早渡过这个概念，因此越早，你会得到你的工作完成并获得成功。 SQL是不会咬你。

I have a table with (among other things) a name and a rank. I'd like to return the set of all unique names, but for each name returned, I'd like to pick the row with the highest rank. This is simple with two nested SELECT statements:

SELECT * FROM (SELECT * FROM foo ORDER BY rank DESC) AS ordered GROUP BY name

MySQL takes the first "hit" for each name, which (because of the earlier ORDER BY) will always be the highest-ranking one.

Now, if I want to wire into this table with ActiveRecord, I'm at a bit of a loss. I could just throw the above into find_by_sql, but that just feels dirty. I tried something like

result = foo.all
result.delete_if do |item|
  isOutranked = false
  result.each do |row|
    if (row.name == item.name) and (row.rank > item.rank) then isOutranked = true
  end
  isOutranked
end

I think that works, but it still feels like there ought to be a better way. Solving it either via ActiveRecord trickery or a more elegant array manipulation would be welcome!

解决方案

MySQL takes the first "hit" for each name, which (because of the earlier ORDER BY) will always be the highest-ranking one.

The query you're using to return the top row in the group is not guaranteed. It's only a coincidence of the implementation, and it's subject to change. Do not rely on this.

You are trying to solve the "greatest-n-per-group" problem that I see posted on StackOverflow frequently. Here is a query that gets the answer more reliably:

SELECT t1.*
FROM foo AS t1
LEFT OUTER JOIN foo AS t2
 ON (t1.name = t2.name AND t1.rank < t2.rank)
WHERE t2.name IS NULL;

Alternative that does the same thing:

SELECT *
FROM foo AS t1
WHERE NOT EXISTS 
    (SELECT * FROM foo AS t2
     WHERE t1.name = t2.name AND t1.rank < t2.rank);

I could just throw the above into find_by_sql, but that just feels dirty.

ActiveRecord is very handy for certain kinds of queries, but you can't solve every database query with ActiveRecord. The sooner you get over this notion the sooner you will get your work done and be successful. SQL is not going to bite you.

这篇关于ActiveRecord的嵌套的SELECT - 我能做到这一点，无需人工SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！