Shifting query from Controller to Model

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

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

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