Doing find_by_sql outside an ActiveRecord object???

rubyonrails-talk
1

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

2

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?

3

Hi,

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

4

How is that possible, then?

5

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

6

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

7

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"

8

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

Frederick Cheung wrote: