Rails 3选择查询将不工作 [英] Rails 3 Select Query Won't Work

问题描述

我有以下的查询不工作，我想知道如何解决它。

I had the following query that doesn't work and I was wondering how to fix it.

Player.select("players.*, 
    (SELECT COUNT(*) FROM Results 
    WHERE results.player_id = players.id and win = true) 
as wins").where("wins > 0").order("wins desc")

当布尔 win 设置为true时，其外键在结果表中显示的次数。但是，有时外键将出现在结果表中，但布尔 win 字段将为false，导致我不想看到的记录计数为零，所以我想我会尝试使用.where（wins> 0）子句消除这些零记录计数，但我得到这个错误：

I'm trying to limit parent Player records by a count of how many times its foreign key appears in the Results table when the boolean win is set to true. However, sometimes the foreign key will appear in the Results table but the boolean win field will be false resulting in a count of zero for records that I don't want to see, so I thought I would try and eliminate these count of zero records using the .where("wins > 0") clause but I get this error:

PGError：ERROR：列wins不存在

PGError: ERROR: column "wins" does not exist

有趣的是，它找到

The funny thing is it finds the wins field when I try and order by it, but not with the added where clause.

推荐答案

当我尝试和排序时，我相信以下查询将工作：

I believe the following query will work:

select players.*, count(results.id) as wins from players left join results on results.player_id = players.id and results.win = true group by players.id having count(results.id) > 0 order by wins desc;

这应该是Railsify，虽然自然我没有测试它：

This should Railsify thus, though naturally I haven't tested it:

Player.select('players.*, count(results.id) as wins').joins('left join results on results.player_id = players.id and results.win = true').group('players.id').having('count(results.id) > 0').order('wins desc')

如果你不关心wins = 0的行，你可以做一个内部连接，而不是一个左连接，这可能更快。

If you don't care about rows where wins = 0, you can do an inner join instead of a left join, which might be faster.

这篇关于Rails 3选择查询将不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！