# Doing find\_by\_sql outside an ActiveRecord object? **URL:** \ **Category:** Rails\ **Created:** [February 19, 2008, 3:59am UTC](https://www.ruby-forum.com/t/doing-find-by-sql-outside-an-activerecord-object/129501 "2008-02-19T03:59:02Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![pahagon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ac8455/32.png) [@pahagon](https://www.ruby-forum.com/u/pahagon)\ **Post date:** [February 19, 2008, 3:59am UTC](https://www.ruby-forum.com/t/doing-find-by-sql-outside-an-activerecord-object/129501/1 "2008-02-19T03:59:02Z")
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. ---
**Author:** ![pahagon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ac8455/32.png) [@pahagon](https://www.ruby-forum.com/u/pahagon)\ **Post date:** [February 19, 2008, 4:14am UTC](https://www.ruby-forum.com/t/doing-find-by-sql-outside-an-activerecord-object/129501/2 "2008-02-19T04:14:16Z")
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? ---
**Author:** ![pahagon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ac8455/32.png) [@pahagon](https://www.ruby-forum.com/u/pahagon)\ **Post date:** [February 19, 2008, 5:36am UTC](https://www.ruby-forum.com/t/doing-find-by-sql-outside-an-activerecord-object/129501/3 "2008-02-19T05:36:21Z")
Hi, Use Active Record and you can relax well. I can code yours with Active Record. _~_~_~_~_~\*\*~_~_~_~_~_ Reinhart Ariando YM : Booking2Heaven WEB : [http://teapoci.blogspot.com](http://teapoci.blogspot.com) ---
**Author:** ![pahagon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ac8455/32.png) [@pahagon](https://www.ruby-forum.com/u/pahagon)\ **Post date:** [February 19, 2008, 11:22am UTC](https://www.ruby-forum.com/t/doing-find-by-sql-outside-an-activerecord-object/129501/4 "2008-02-19T11:22:51Z")
On 19 Feb 2008, at 02:58, Paulo P. wrote: > 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 ---
**Author:** ![pahagon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ac8455/32.png) [@pahagon](https://www.ruby-forum.com/u/pahagon)\ **Post date:** [February 19, 2008, 6:41am UTC](https://www.ruby-forum.com/t/doing-find-by-sql-outside-an-activerecord-object/129501/5 "2008-02-19T06:41:34Z")
How is that possible, then? On Feb 19, 4:36 am, Visit Indonesia 2008 \ **Author:** ![pahagon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ac8455/32.png) [@pahagon](https://www.ruby-forum.com/u/pahagon)\ **Post date:** [February 19, 2008, 2:28pm UTC](https://www.ruby-forum.com/t/doing-find-by-sql-outside-an-activerecord-object/129501/6 "2008-02-19T14:28:40Z") On 19 Feb 2008, at 12:28, Paulo P. wrote: > 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 > > =\> #\ > 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’ > > =\> #\ > updated\_at: “2007-09-18 22:07:25”, texpert\_id: 96\> > > > a.foo > > =\> “123” ---
**Author:** ![pahagon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ac8455/32.png) [@pahagon](https://www.ruby-forum.com/u/pahagon)\ **Post date:** [February 19, 2008, 1:28pm UTC](https://www.ruby-forum.com/t/doing-find-by-sql-outside-an-activerecord-object/129501/7 "2008-02-19T13:28:50Z")
I tested in irb, not happening… I’m trying select\_all then On Feb 19, 10:21 am, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![pahagon](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ac8455/32.png) [@pahagon](https://www.ruby-forum.com/u/pahagon)\ **Post date:** [February 19, 2008, 5:42pm UTC](https://www.ruby-forum.com/t/doing-find-by-sql-outside-an-activerecord-object/129501/8 "2008-02-19T17:42:14Z")
Thanks a lot Frederick. It really works, damn irb 🙂