Shifting query from Controller to Model


#1

Hi All, I want to shift my Method from Controller to Model, I
have a
controller with no Model because there is no table. Now I want to shift
my
Method from Controller to Model. The code is here…
//////////Dashboard Controller////////////////////////
class DashboardController < ApplicationController
layout ‘standard’
def index
@dashs = Lead.find_by_sql("(SELECT
opportunities.created_on,opportunities.created_by,opportunities.position_title
FROM opportunities) UNION DISTINCT
(SELECT
customers.created_on,customers.created_by,customers.company_name FROM
customers) UNION DISTINCT
(SELECT
leads.created_on,leads.created_by,leads.first_name FROM leads)
ORDER BY created_on DESC")

@dashboards = @dashs.paginate :per_page => 3,:page => params[:page]

end

end

Now I want to make a Model for Dashboard and shift my queries from
Controller to Model.

Thanks & Regards,
Shahroon


#2

Hi Shahroon

It looks like you are creating a resource for leads, and that you want
to
display these leads in the dashboard for your application.

What you can do is create a controller for leads. Then you have a
resource
=> :leads.

In your routes file you can map the route of your application to the
leads_controller.

map.root :controller => ‘leads’, :action => ‘index’

You can also add a named route like so

map.dashboard ‘/dashboard’, controller => ‘leads’, :action => ‘index’

This will still allow you to use dashboard_path.

If you want to move the query to your Lead model you can do it like
this:

in the controller

@dashs = Lead.some_method_name

And then in the lead model

def self.some_method_name
.find_by_sql("(SELECT
opportunities.created_on,opportunities.created_by,opportunities.position_title
FROM opportunities) UNION DISTINCT
(SELECT
customers.created_on,customers.created_by,customers.company_name
FROM customers) UNION DISTINCT
(SELECT
leads.created_on,leads.created_by,leads.first_name FROM leads)
ORDER BY created_on DESC")

end

or

class << self
def some_method_name
find_by_sql("(SELECT
opportunities.created_on,opportunities.created_by,opportunities.position_title
FROM opportunities) UNION DISTINCT
(SELECT
customers.created_on,customers.created_by,customers.company_name
FROM customers) UNION DISTINCT
(SELECT
leads.created_on,leads.created_by,leads.first_name FROM leads)
ORDER BY created_on DESC")

end
end

Regards
Ivor


#3

Thanks a lot Ivor,
Well this works and in fact I wanna know why
I
need mapping in route file cuz I got the results without routing it ,
look
what i did
1> I placed my method in Leads Model with query
2> Then I called it in my dashboard controller

and it worked, now I am facing problem with its pagintion look at the
lines
given below

def self.display_data
@dashs = Lead.find_by_sql("(SELECT
opportunities.created_on,opportunities.created_by,opportunities.position_title
FROM opportunities) UNION DISTINCT(SELECT
customers.created_on,customers.created_by,customers.company_name FROM
customers) UNION DISTINCT (SELECT
leads.created_on,leads.created_by,leads.first_name FROM leads)
ORDER BY created_on DESC")
@dashs.paginate :per_page => 3,:page => params[:page]
end
when i apply this, I get this error

undefined local variable or method `params’

but when I removed the last line then it gives me the records but
without pagination of course
just because the pagination method is removed, so could you help me
regarding this issue.

Thanks again,
Shahroon