Rails - Feb 2008 - 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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?

On Feb 19, 2:58 am, Paulo Pereira
<paulo.z...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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



-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

How is that possible, then?

On Feb 19, 4:36 am, Visit Indonesia 2008 <rails-mailing-l...@andreas-
s.net> wrote:
> 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
>
> --
> Posted viahttp://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 19 Feb 2008, at 02:58, Paulo Pereira wrote:
>
> 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 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
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On Feb 19, 10:21 am, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On 19 Feb 2008, at 02:58, Paulo Pereira wrote:
>
>
>
>
>
> > 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 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 19 Feb 2008, at 12:28, Paulo Pereira 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
=> #<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"

> On Feb 19, 10:21 am, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> wrote:
>> On 19 Feb 2008, at 02:58, Paulo Pereira wrote:
>>
>>
>>
>>
>>
>>> 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 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
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

Frederick Cheung wrote:
> On 19 Feb 2008, at 12:28, Paulo Pereira 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
> => #<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"
>
>
> > On Feb 19, 10:21 am, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> > wrote:
> >> On 19 Feb 2008, at 02:58, Paulo Pereira wrote:
> >>
> >>
> >>
> >>
> >>
> >>> 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 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
> > >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---