我有两张桌子,users和votations,在投票中有两个字段win_id和user_id。我需要使用win_id (投票)和id (用户)在活动记录中创建查询。
我的sql查询如下:Select users.name, Count(votations.win_id) from votations Inner Join users ON vontations.win_id = users.id Group By users.name;
嘿,我正在尝试在rails中创建一个sql查询,如何将这个sql查询转换为rails活动记录。查询为:
SELECT f.id, COUNT( f.id ) AS total
FROM feeds AS f LEFT JOIN feed_likes AS l
ON f.id = l.feed_id
GROUP BY f.id
这里有两个表: feeds表和feed_likes表。
我需要实现与How to get list of values in GROUP_BY clause?完全类似的功能,但我需要使用rails 4.2.1中的活动记录查询接口。 我只能走到这一步。 Roles.where(id: 2)
.select("user_roles.id, user_roles.role, GROUP_CONCAT(DISTINCT roles.group_id SEPARATOR ',') ")
.group(:role) 但这只返回一个ActiveRecord::Relation对象,该对象只有一个条
我想为这个SQL查询编写一个rails活动记录查询:
SELECT c.name FROM categories as c where c.id IN (select c.parent_id FROM categories as c join categories_coaches as cc on c.id=cc.category_id where cc.coach_id=17 group by c.parent_id)
型号:
class Category < ActiveRecord::Base
has_and_belongs_to_many :coaches
end
clas
我正在尝试将以下SQL转换为rails活动记录查询
"SELECT * FROM stocks
INNER JOIN
(
SELECT product_id, MIN(ask_price) min_price
FROM stocks
GROUP BY product_id,size
) sub ON stocks.product_id = sub.product_id AND
stocks.ask_price =
有以下sql查询,我将如何翻译活动记录中两列的乘法?
SELECT plan_name, plan_price, count(plan_id), plan_price*count(plan_id) AS totalrevenue
FROM leads
INNER JOIN plans p ON leads.plan_id = p.id
WHERE lead_status_id = 5
GROUP BY plan_name, plan_price;
我在使用Ruby on Rails 4用Squeel或直接活动记录重写这个SQL查询时遇到了很多问题。
查询中的所有3个数字都需要在执行前传递给查询。SQL查询如下。
SELECT "users".*
FROM "users"
WHERE "users"."id"
IN (SELECT "users"."id" FROM "users"
INNER JOIN marketing_assets ON users.id = marketing_assets.user_id
我希望创建一个活动记录查询,并将结果存储在散列中,其中包括来自关联表的汇总信息,如下所示。
以下是表格和协会:
Post belongs_to: category
Category has_many: posts
在这里,我想对每个类别中的帖子#进行计数,并按如下方式创建一个汇总表(使用对所需表的SQL查询):
select c.name, count(p.id) from posts a left join categories c on p.category_id = c.id where p.status = 'Approved' group by (c.name) or
无论如何,我可以在rails控制台中通过在下面放置行来获取结果。
group = UserGroup.find_by_sql("select user_group_memberships.user_group_id, sum(-bets.amount + bets.amount_paid) as bankroll_net_profit from bets inner join user_group_memberships ON bets.user_id = user_group_memberships.user_id GROUP BY user_group_memberships.
我使用的是带有活动记录的ruby on rails。我正在尝试连接一些表,以便可以基于父类进行分组,但我希望在只查看该用户的最大日期条目的情况下,对每个用户的子类上的所有列进行求和。很难描述,下面的查询成功地得到了我想要的结果(除了限制结果只添加与客户在该位置的客户交互的最大日期匹配的记录)
@routeMeals = ClientInteraction.joins('INNER JOIN location_camps as lc on lc.id = location_camp_id')
.joins('INNER JO
我想查询两个用户之间的常见组。
这是我的模特
用户模型
class User < ActiveRecord::Base
has_many :group_memberships
has_many :groups, through: :group_memberships
end
群模型
class Group < ActiveRecord::Base
has_many :group_memberships
has_many :users, through: :group_memberships
end
用户和组之间的连接表模型
class GroupMembership
如何将其转换为活动记录查询?
@test = @keyword_list.find_by_sql("
SELECT keywords.keyword, minimum_ranking.min_position from keywords
JOIN (select keyword_id, min(position) as min_position from rankings group by keyword_id) minimum_ranking
on minimum_ranking.keyword_id = keywords.id
where created_at be
模型定义:
class Article < ActiveRecord::Base
has_and_belongs_to_many :groups
end
class Group < ActiveRecord::Base
has_and_belongs_to_many :articles
has_many :domains, :inverse_of=>:group
end
class Domain < ActiveRecord::Base
belongs_to :group, :inverse_of=>:domains
has_many :p
我有模型用户、组和成员,其结构如下:
class User < ApplicationRecord
has_many :memberships
has_many :groups, through: :memberships
end
class Group < ApplicationRecord
has_many :memberships
has_many :users, through: :memberships
end
class Membership < ApplicationRecord
belongs_to :user
belongs_to
如何将以下内容转换为活动记录查询:
SELECT reviews.style_id, AVG("col1"), AVG("col2")
FROM reviews, audios
WHERE reviews.consumer_id = audios.consumer_id
GROUP BY style_id;
col1和col2属于audios表,但它们的名称是唯一的(reviews中没有相似的列名),因此不存在歧义错误。
我正在使用PostgreSQL。
class User < ActiveRecord::Base
has_many :roles
has_many :allocations, class_name: 'Project', inverse_of: :user
end
class Role < ActiveRecord::Base
belongs_to :user
end
class Project < ActiveRecord::Base
belongs_to :user, inverse_of: :allocations
end
我想要找到那些没有分配的用户,以及分配的star
我想使用active record生成以下sql。
WHERE (column_name1, column_name1) IN (SELECT ....)
我不知道怎么做,这是活动记录。
到目前为止我已经试过了
where('column_name1, column_name2' => {})
where([:column_name1, :column_name2] => {})
这是我想要创建的完整查询
SELECT a, Count(1)
FROM table
WHERE ( a, b ) IN (SEL
在我们的Rails 4.2应用程序中,下面是在sum()记录上执行payment和group()的查询:
Payment.joins(:project, :pr_category).select("sum(payments.amount) as Total, projects.name as Project, definitions.name as Category").group('payments.project_id, payments.pr_category_id').order('payments.project_id')
查询根据项目
我有四个模型:
User (users)
Caretaker (a completely different type of user)
MessageGroup (a group)
MessageGroupMembership (a join table between MessageGroup and (Users && Caretakers))
我正在尝试定义一个方法,用于检查是否存在具有x个成员的组(成员可以是用户或管理员)。
def group_containing(members)
# return group if a MessageGroup exists
我有两种型号,如下所示。Address表有一个属性城市。能不能找出完全没有。在每个城市中使用活动记录查询的产品,还是只能通过本地sql查询完成?
class Product < ActiveRecord::Base
has_one :address
end
class Address < ActiveRecord::Base
has_many :products
end
所以我有一个SQL查询,我想在rails中复制它:
select g.*
from gamebox_favorites f
inner join gameboxes g on f.gamebox_id = g.id
group by f.gamebox_id
order by count(f.gamebox_id) desc;
我已经阅读了rails活动记录查询接口站点,但似乎不太能把它放在一起。我希望查询返回一个Gamebox记录的集合,按照游戏框拥有的“收藏”的数量进行排序。在rails中做这件事最干净的方法是什么?
如何使用活动记录形成以下查询?
SELECT c.*
FROM `course_enrollments` ce JOIN courses c ON ce.course_id = c.id
WHERE ce.created_at
BETWEEN '2000-01-01' and '2012-01-01' [AND ANOTHER POSSIBLE CONDITION]
GROUP BY c.id
我希望能够这样做:(我知道下面是不正确的,但我只想展示一个通用的示例)
courses = Course.joins(:course_enrollments).whe
我需要使用rails活动记录编写以下查询:
select c.name,p.name,SUM(e.hours)
from Events e, Projects p, Unities u, Clients c
where e.project_id = p.id AND
p.unity_id = u.id AND
u.client_id = c.id
group by c.name, p.name, e.project_id
型号:
class Event < ActiveRecord::Base
belongs_to :project
class Proje
我有一个叫User的模态
class User < ActiveRecord::Base
has_many: :posts
end
还有另一个叫Post的模式
class Post < ActiveRecord::Base
belongs_to :user
end
如果我有一个作用域posts对象(比如posts = Post.where(id: [1,2,3,4])
如何使用活动记录方法查询所有帖子的uniq用户?
我知道它可以在两个查询中完成,有方法在一个查询中完成吗?
我需要编写一个查询,其中只包括以前没有参加过活动的个人。
这是模型
class Individual < ActiveRecord::Base
has_many :attendances, dependent: :destroy
has_many :events, through: :attendances
end
class Attendance < ActiveRecord::Base
belongs_to :event
belongs_to :individual
end
class Event < ActiveRecord::Base
has