Doing find_by_sql outside an ActiveRecord object???

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 Pereira

I have kind of a "solution". Doing both Download.find_by_sql AND
Permission.find_by_sql, using limit and offset in each.
Any better solution?

Hi,

Use Active Record and you can relax well. I can code yours with Active
Record.

How is that possible, then?

Hi there. I have a lit

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.

That's odd - either way you should get all the columns from the result
set in the returned model object.
You could just drop down to using connection.select_all

Fred

I tested in irb, not happening... I'm trying select_all then

I tested in irb, not happening... I'm trying select_all then

What you see in irb may be misleading you:

>> a = Conversation.find 132456
=> #<Conversation id: 132456, created_at: "2007-09-18 22:05:53",
updated_at: "2007-09-18 22:05:53">
>> a.foo
NoMethodError: undefined method `foo' for #<Conversation:0x19708e4>
...
>> a = Conversation.find 132465, :select => 'conversations.*, 123 as
foo'
=> #<Conversation id: 132465, created_at: "2007-09-18 22:07:25",
updated_at: "2007-09-18 22:07:25", texpert_id: 96>
>> a.foo
=> "123"

Thanks a lot Frederick. It really works, damn irb :slight_smile:

Frederick Cheung wrote: