Hi there. I have a little problem here.
I have 2 different models.
Download:
create_table :downloads do |t|
t.column :name, :string, :null => false
t.column :user, :string, :null => false
t.column :size, :integer, :null => false
t.column :created_at, :datetime
t.column :updated_at, :datetime
end
Permission:
create_table :permissions do |t|
t.column :user, :string, :null => false
t.column :ban, :boolean, :null => false, :default => 0
end
(You ask: Why not having a User model? Because user data is in an
external LDAP server.)
The problem is that any “user” can be in one model but not on the
other. I want to fetch some data from the database. For each distinct
user (that may be on permissions or downloads), i want to get the
total size of their files and the ban status. I’ve got the query.
SELECT bans.user, ban, COALESCE(SUM(size), 0) as size FROM
(SELECT users.user, COALESCE(ban, 0) as ban FROM
(SELECT user FROM downloads UNION SELECT user from permissions) AS
users
LEFT JOIN permissions
ON users.user = permissions.user) as bans
LEFT JOIN downloads
ON bans.user = downloads.user GROUP BY user
With this I get user, ban, size.
When I do Download.find_by_sql, I only get user and size.
When I do Permission.find_by_sql, I only get user and ban.
I could fetch everything with different queries, and merge the
results, but I want to paginate the query to improve efficiency.
Is there any way to get the data? Doing find_by_sql in another way?
Thanks in advance,
Paulo P.
