从SQL到Rails活动记录

Question

我怎么能有这个SQL查询到Rails ActiveRecord?

选择对象。* (objects.value)→在0FROM "users"， jsonb_each(hit->'opportunity') objects WHERE对象。关键::文本~ ^ [0 - 9]{0,4}_pct美元"和((objects.value)→祝辞0)::数字祝辞= 0.5

Answer 1

你可以这样做:

User.select("REPLACE(objects.key, '_pct', '' )::int AS key")
.joins("inner join jsonb_each(users.hit->'opportunity') AS objects ON true")
.where("objects.key::text ~ '^[0-9]{0,4}_pct$' AND ((objects.value)->>0)::numeric >= 0.5")