ruby-on-rails-3 - ActiveRecord 查询对我的小脑袋来说太难了

Question

我有一个 activerecord 模型 Event，我想按等级排序。排名将是加权属性的总和。例如，我可能想用一些逻辑对事件进行排名，例如:

LOG(number of followers) + (7 - number of days from now)

下面的工作，但并不令人满意，因为它返回一个结果集而不是一个关系对象。因此，我无法将其视为一个范围。 (仅供引用，我正在使用带有 PostGIS 扩展的 Postgres)

x = Event.find_by_sql("
            SELECT   
                (
                    CASE WHEN COUNT(follows.*) = 0 
                    THEN 0 
                    ELSE LOG(COUNT(follows.*)) END

                    +

                    SIGN(7 - (EXTRACT(EPOCH FROM start) - EXTRACT(EPOCH FROM NOW())) / 86400) * LOG(ABS(7 - (EXTRACT(EPOCH FROM start) - EXTRACT(EPOCH FROM NOW())) / 86400))
                ) as score,
                events.*
            FROM events
            LEFT OUTER JOIN follows 
                ON events.id = follows.followable_id AND follows.followable_type = 'Event' AND follows.blocked = 'f'
            WHERE (events.start > NOW()) AND (ST_DWithin(st_setsrid(st_point(#{@location[:lng]}, #{@location[:lat]}), 4326), st_transform(loc, 4326), 48280.2, true))
            GROUP BY events.id
            ORDER BY 1 DESC
            ")

我知道我可以向 Events 表添加一个计数器缓存并避免连接，但将来我想通过其他一些关联来计算排名，所以这将非常有帮助知道怎么做。

谢谢

Answer 1

这实际上很容易拆分成 ActiveRecord::Relation 查询。

x = Event.select("(
                CASE WHEN COUNT(follows.*) = 0 
                THEN 0 
                ELSE LOG(COUNT(follows.*)) END

                +

                SIGN(7 - (EXTRACT(EPOCH FROM start) - EXTRACT(EPOCH FROM NOW())) / 86400) * LOG(ABS(7 - (EXTRACT(EPOCH FROM start) - EXTRACT(EPOCH FROM NOW())) / 86400))
            ) as score,
            events.*")

x = x.joins("LEFT OUTER JOIN follows 
            ON events.id = follows.followable_id AND follows.followable_type = 'Event' AND follows.blocked = 'f'")

x = x.where("(events.start > NOW()) AND (ST_DWithin(st_setsrid(st_point(#{@location[:lng]}, #{@location[:lat]}), 4326), st_transform(loc, 4326), 48280.2, true))")

x = x.group('events.id')

x = x.order('1 desc')

当然，我建议将它们分成不同的范围，但这至少应该让您朝着正确的方向前进。