TOO MANY DATABASE QUERIES

I have created a named_scope in order to get a better result from the database. Here it is the named scope

cell_meeting.rb

  named_scope :report, lambda {|report_id|
    {
            :conditions=>{"cell_reports.id"=>report_id},
            :include=>[:cell_meeting_presences,:cell_report,:cell,{:cell_meeting_presences=>:member}],
            :order => "members.name ASC, cell_meetings.date ASC"
    }
  }

This named scope is supposed to include the tables :
       :cell_meeting_presences,:cell_report,:cell and :member

So I would imagine that when accessing some database where it was already include it was not necessary to fetch the database again.

I have the following code in my controller
cell_reports_controller.rb
  def retrieve_report_info(report)
    logger.debug "retrieve_report_info -->"
    @cellmeetings = CellMeeting.report(report)
    logger.debug "getting the report"
    @cell_report =  @cellmeetings[0].cell_report
    logger.debug "getting the cell"
    @cell = @cell_report.cell_meetings.first.cell
    logger.debug "getting the members"
    @members = @cell_report.cell_meetings.first.cell.members
    logger.debug "database get the leaders"
    @leaders =  @cell.leaders
    logger.debug "database get the supervisors"
    @supervisors = @leaders.first.supervisors
    logger.debug "database get the coordinators"
    @coordinators = @supervisors.first.coordinators
    build_members_presences()
    logger.debug "retrieve_report_info <--"
  end

Here is the output, where you can see several database operations
retrieve_report_info -->
getting the report
  CellMeeting Load Including Associations (250.0ms)   SELECT "cell_meetings"."id" AS t0_r0, "cell_meetings"."date" AS t0_r1, "cell_meetings"."cell_id" AS t0_r2, "cell_meetings"."cell_report_id" AS t0_r3, "cell_meetings"."created_at" AS t0_r4, "cell_meetings"."updated_at" AS t0_r5, "cell_meeting_presences"."id" AS t1_r0, "cell_meeting_presences"."presence" AS t1_r1, "cell_meeting_presences"."member_id" AS t1_r2, "cell_meeting_presences"."cell_meeting_id" AS t1_r3, "cell_meeting_presences"."created_at" AS t1_r4, "cell_meeting_presences"."updated_at" AS t1_r5, "cell_reports"."id" AS t2_r0, "cell_reports"."date" AS t2_r1, "cell_reports"."teacher" AS t2_r2, "cell_reports"."assistance" AS t2_r3, "cell_reports"."extra_activity" AS t2_r4, "cell_reports"."remark" AS t2_r5, "cell_reports"."created_at" AS t2_r6, "cell_reports"."updated_at" AS t2_r7, "cells"."id" AS t3_r0, "cells"."name" AS t3_r1, "cells"."cell_type" AS t3_r2, "cells"."weekday" AS t3_r3, "cells"."hour" AS t3_r4, "cells"."minute" AS t3_r5, "cells"."address_id" AS t3_r6, "cells"."created_at" AS t3_r7, "cells"."updated_at" AS t3_r8, "cell_meeting_presences_cell_meetings"."id" AS t4_r0, "cell_meeting_presences_cell_meetings"."presence" AS t4_r1, "cell_meeting_presences_cell_meetings"."member_id" AS t4_r2, "cell_meeting_presences_cell_meetings"."cell_meeting_id" AS t4_r3, "cell_meeting_presences_cell_meetings"."created_at" AS t4_r4, "cell_meeting_presences_cell_meetings"."updated_at" AS t4_r5, "members"."id" AS t5_r0, "members"."name" AS t5_r1, "members"."date_of_birth" AS t5_r2, "members"."phone" AS t5_r3, "members"."cell_phone" AS t5_r4, "members"."marital_status" AS t5_r5, "members"."consort" AS t5_r6, "members"."username" AS t5_r7, "members"."crypted_password" AS t5_r8, "members"."password_salt" AS t5_r9, "members"."persistence_token" AS t5_r10, "members"."email" AS t5_r11, "members"."photo_file_name" AS t5_r12, "members"."photo_content_type" AS t5_r13, "members"."photo_file_size" AS t5_r14, "members"."photo_updated_at" AS t5_r15, "members"."created_at" AS t5_r16, "members"."updated_at" AS t5_r17, "members"."address_id" AS t5_r18, "members"."cell_id" AS t5_r19 FROM "cell_meetings" LEFT OUTER JOIN "cell_meeting_presences" ON cell_meeting_presences.cell_meeting_id = cell_meetings.id LEFT OUTER JOIN "cell_reports" ON "cell_reports".id = "cell_meetings".cell_report_id LEFT OUTER JOIN "cells" ON "cells".id = "cell_meetings".cell_id LEFT OUTER JOIN "cell_meeting_presences" cell_meeting_presences_cell_meetings ON cell_meeting_presences_cell_meetings.cell_meeting_id = cell_meetings.id LEFT OUTER JOIN "members" ON "members".id = "cell_meeting_presences_cell_meetings".member_id WHERE ("cell_reports"."id" = '2053932787') ORDER BY members.name ASC, cell_meetings.date ASC
getting the cell
  CellMeeting Load (0.0ms)   SELECT * FROM "cell_meetings" WHERE ("cell_meetings".cell_report_id = 2053932787) LIMIT 1
  Cell Load (0.0ms)   SELECT * FROM "cells" WHERE ("cells"."id" = 1)
getting the members
  CACHE (0.0ms)   SELECT * FROM "cell_meetings" WHERE ("cell_meetings".cell_report_id = 2053932787) LIMIT 1
  CACHE (0.0ms)   SELECT * FROM "cells" WHERE ("cells"."id" = 1)
database get the leaders
database get the supervisors
  Leader Load (16.0ms)   SELECT * FROM "leaders" WHERE ("leaders".cell_id = 1) LIMIT 1
database get the coordinators
  Supervisor Load (0.0ms)   SELECT * FROM "supervisors" INNER JOIN "leaders_supervisors" ON "supervisors".id = "leaders_supervisors".supervisor_id WHERE ("leaders_supervisors".leader_id = 1 ) LIMIT 1
  Member Load (0.0ms)   SELECT * FROM "members" WHERE ("members".cell_id = 1) ORDER BY name ASC
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 3 AND ("cell_meeting_presences"."member_id" = 3)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 4 AND ("cell_meeting_presences"."member_id" = 3)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 5 AND ("cell_meeting_presences"."member_id" = 3)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 6 AND ("cell_meeting_presences"."member_id" = 3)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 7 AND ("cell_meeting_presences"."member_id" = 3)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 3 AND ("cell_meeting_presences"."member_id" = 4)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 4 AND ("cell_meeting_presences"."member_id" = 4)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 5 AND ("cell_meeting_presences"."member_id" = 4)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 6 AND ("cell_meeting_presences"."member_id" = 4)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 7 AND ("cell_meeting_presences"."member_id" = 4)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 3 AND ("cell_meeting_presences"."member_id" = 1)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 4 AND ("cell_meeting_presences"."member_id" = 1)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 5 AND ("cell_meeting_presences"."member_id" = 1)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 6 AND ("cell_meeting_presences"."member_id" = 1)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 7 AND ("cell_meeting_presences"."member_id" = 1)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 3 AND ("cell_meeting_presences"."member_id" = 2)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 4 AND ("cell_meeting_presences"."member_id" = 2)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 5 AND ("cell_meeting_presences"."member_id" = 2)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 6 AND ("cell_meeting_presences"."member_id" = 2)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 7 AND ("cell_meeting_presences"."member_id" = 2)) LIMIT 1
retrieve_report_info <--


QUESTION
 Why it is going to the database again to fetch the cell (for example) if the cell table was already in the named_scope?
 Am I doing something wrong there?
 How to fix this problem?
* Leaders, supervisors, coordinators are not included, so I can understand it will go to the database to retrieve the information
LVL 2
lusfernandosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wesgarrisonCommented:
You can simplify your include by not repeating cell_meeting_presences:

 :include=>[:cell_report,:cell,{:cell_meeting_presences=>:member}], ...

To your question about why it's looking up the cell:
# your code, my comments

# prepare the named scope, loading everything in @cellmeetings
# this means all the 'included' tables need to be referenced
# from this variable
@cellmeetings = CellMeeting.report(report)

# look up the cell report.  doesn't cause an extra query
# since it has been eager loaded
@cell_report =  @cellmeetings[0].cell_report

# Look up the cell, but via the report => meeting
# causes an extra query because the CELL DIDN'T LOAD THE MEETING
@cell = @cell_report.cell_meetings.first.cell

# you already looked up the meeting's cell with :include
# so just use:
@cell = @cellmeetings[0].cell

# Comments
* Use :first if you're only getting one record by id

Open in new window

0
lusfernandosAuthor Commented:
Hi wesgarrison,

  I did what you have proposed,  accessing the tables using the @cell_meetings, however it is still doing the same, actually now the SQL is a little more complex.
 

Here is the code:

  named_scope :report, lambda {|report_id|
    {
            :conditions=>{"cell_reports.id"=>report_id},
            :include=>[:cell_report,:cell,{:cell_meeting_presences=>:member}],
            :order => "members.name ASC, cell_meetings.date ASC"
    }
  }


 def retrieve_report_info(report)
    logger.debug "retrieve_report_info -->"
    @cellmeetings = CellMeeting.report(report)
    logger.debug "getting the report"
    @cell_report =  @cellmeetings.first.cell_report
    logger.debug "getting the cell"
    @cell = @cellmeetings.first.cell
    logger.debug "getting the members"
    @members = @cellmeetings.first.cell_report.cell_meetings.first.cell.members
    logger.debug "database get the leaders"
    @leaders =  @cell.leaders
    logger.debug "database get the supervisors"
    @supervisors = @leaders.first.supervisors
    logger.debug "database get the coordinators"
    @coordinators = @supervisors.first.coordinators
    build_members_presences()
    logger.debug "retrieve_report_info <--"
  end


Here is the log
database -->
retrieve_report_info -->
getting the report
  CellMeeting Load IDs For Limited Eager Loading (0.0ms)   SELECT DISTINCT "cell_meetings".id FROM "cell_meetings" LEFT OUTER JOIN "cell_reports" ON "cell_reports".id = "cell_meetings".cell_report_id LEFT OUTER JOIN "cell_meeting_presences" ON cell_meeting_presences.cell_meeting_id = cell_meetings.id LEFT OUTER JOIN "members" ON "members".id = "cell_meeting_presences".member_id WHERE ("cell_reports"."id" = '2053932787') ORDER BY members.name ASC, cell_meetings.date ASC LIMIT 1
  CellMeeting Load Including Associations (0.0ms)   SELECT "cell_meetings"."id" AS t0_r0, "cell_meetings"."date" AS t0_r1, "cell_meetings"."cell_id" AS t0_r2, "cell_meetings"."cell_report_id" AS t0_r3, "cell_meetings"."created_at" AS t0_r4, "cell_meetings"."updated_at" AS t0_r5, "cell_reports"."id" AS t1_r0, "cell_reports"."date" AS t1_r1, "cell_reports"."teacher" AS t1_r2, "cell_reports"."assistance" AS t1_r3, "cell_reports"."extra_activity" AS t1_r4, "cell_reports"."remark" AS t1_r5, "cell_reports"."created_at" AS t1_r6, "cell_reports"."updated_at" AS t1_r7, "cells"."id" AS t2_r0, "cells"."name" AS t2_r1, "cells"."cell_type" AS t2_r2, "cells"."weekday" AS t2_r3, "cells"."hour" AS t2_r4, "cells"."minute" AS t2_r5, "cells"."address_id" AS t2_r6, "cells"."created_at" AS t2_r7, "cells"."updated_at" AS t2_r8, "cell_meeting_presences"."id" AS t3_r0, "cell_meeting_presences"."presence" AS t3_r1, "cell_meeting_presences"."member_id" AS t3_r2, "cell_meeting_presences"."cell_meeting_id" AS t3_r3, "cell_meeting_presences"."created_at" AS t3_r4, "cell_meeting_presences"."updated_at" AS t3_r5, "members"."id" AS t4_r0, "members"."name" AS t4_r1, "members"."date_of_birth" AS t4_r2, "members"."phone" AS t4_r3, "members"."cell_phone" AS t4_r4, "members"."marital_status" AS t4_r5, "members"."consort" AS t4_r6, "members"."username" AS t4_r7, "members"."crypted_password" AS t4_r8, "members"."password_salt" AS t4_r9, "members"."persistence_token" AS t4_r10, "members"."email" AS t4_r11, "members"."photo_file_name" AS t4_r12, "members"."photo_content_type" AS t4_r13, "members"."photo_file_size" AS t4_r14, "members"."photo_updated_at" AS t4_r15, "members"."created_at" AS t4_r16, "members"."updated_at" AS t4_r17, "members"."address_id" AS t4_r18, "members"."cell_id" AS t4_r19 FROM "cell_meetings" LEFT OUTER JOIN "cell_reports" ON "cell_reports".id = "cell_meetings".cell_report_id LEFT OUTER JOIN "cells" ON "cells".id = "cell_meetings".cell_id LEFT OUTER JOIN "cell_meeting_presences" ON cell_meeting_presences.cell_meeting_id = cell_meetings.id LEFT OUTER JOIN "members" ON "members".id = "cell_meeting_presences".member_id WHERE ("cell_reports"."id" = '2053932787') AND "cell_meetings".id IN (3) ORDER BY members.name ASC, cell_meetings.date ASC
getting the cell
  CACHE (0.0ms)   SELECT DISTINCT "cell_meetings".id FROM "cell_meetings" LEFT OUTER JOIN "cell_reports" ON "cell_reports".id = "cell_meetings".cell_report_id LEFT OUTER JOIN "cell_meeting_presences" ON cell_meeting_presences.cell_meeting_id = cell_meetings.id LEFT OUTER JOIN "members" ON "members".id = "cell_meeting_presences".member_id WHERE ("cell_reports"."id" = '2053932787') ORDER BY members.name ASC, cell_meetings.date ASC LIMIT 1
  CACHE (0.0ms)   SELECT "cell_meetings"."id" AS t0_r0, "cell_meetings"."date" AS t0_r1, "cell_meetings"."cell_id" AS t0_r2, "cell_meetings"."cell_report_id" AS t0_r3, "cell_meetings"."created_at" AS t0_r4, "cell_meetings"."updated_at" AS t0_r5, "cell_reports"."id" AS t1_r0, "cell_reports"."date" AS t1_r1, "cell_reports"."teacher" AS t1_r2, "cell_reports"."assistance" AS t1_r3, "cell_reports"."extra_activity" AS t1_r4, "cell_reports"."remark" AS t1_r5, "cell_reports"."created_at" AS t1_r6, "cell_reports"."updated_at" AS t1_r7, "cells"."id" AS t2_r0, "cells"."name" AS t2_r1, "cells"."cell_type" AS t2_r2, "cells"."weekday" AS t2_r3, "cells"."hour" AS t2_r4, "cells"."minute" AS t2_r5, "cells"."address_id" AS t2_r6, "cells"."created_at" AS t2_r7, "cells"."updated_at" AS t2_r8, "cell_meeting_presences"."id" AS t3_r0, "cell_meeting_presences"."presence" AS t3_r1, "cell_meeting_presences"."member_id" AS t3_r2, "cell_meeting_presences"."cell_meeting_id" AS t3_r3, "cell_meeting_presences"."created_at" AS t3_r4, "cell_meeting_presences"."updated_at" AS t3_r5, "members"."id" AS t4_r0, "members"."name" AS t4_r1, "members"."date_of_birth" AS t4_r2, "members"."phone" AS t4_r3, "members"."cell_phone" AS t4_r4, "members"."marital_status" AS t4_r5, "members"."consort" AS t4_r6, "members"."username" AS t4_r7, "members"."crypted_password" AS t4_r8, "members"."password_salt" AS t4_r9, "members"."persistence_token" AS t4_r10, "members"."email" AS t4_r11, "members"."photo_file_name" AS t4_r12, "members"."photo_content_type" AS t4_r13, "members"."photo_file_size" AS t4_r14, "members"."photo_updated_at" AS t4_r15, "members"."created_at" AS t4_r16, "members"."updated_at" AS t4_r17, "members"."address_id" AS t4_r18, "members"."cell_id" AS t4_r19 FROM "cell_meetings" LEFT OUTER JOIN "cell_reports" ON "cell_reports".id = "cell_meetings".cell_report_id LEFT OUTER JOIN "cells" ON "cells".id = "cell_meetings".cell_id LEFT OUTER JOIN "cell_meeting_presences" ON cell_meeting_presences.cell_meeting_id = cell_meetings.id LEFT OUTER JOIN "members" ON "members".id = "cell_meeting_presences".member_id WHERE ("cell_reports"."id" = '2053932787') AND "cell_meetings".id IN (3) ORDER BY members.name ASC, cell_meetings.date ASC
getting the members
  CACHE (0.0ms)   SELECT DISTINCT "cell_meetings".id FROM "cell_meetings" LEFT OUTER JOIN "cell_reports" ON "cell_reports".id = "cell_meetings".cell_report_id LEFT OUTER JOIN "cell_meeting_presences" ON cell_meeting_presences.cell_meeting_id = cell_meetings.id LEFT OUTER JOIN "members" ON "members".id = "cell_meeting_presences".member_id WHERE ("cell_reports"."id" = '2053932787') ORDER BY members.name ASC, cell_meetings.date ASC LIMIT 1
  CACHE (0.0ms)   SELECT "cell_meetings"."id" AS t0_r0, "cell_meetings"."date" AS t0_r1, "cell_meetings"."cell_id" AS t0_r2, "cell_meetings"."cell_report_id" AS t0_r3, "cell_meetings"."created_at" AS t0_r4, "cell_meetings"."updated_at" AS t0_r5, "cell_reports"."id" AS t1_r0, "cell_reports"."date" AS t1_r1, "cell_reports"."teacher" AS t1_r2, "cell_reports"."assistance" AS t1_r3, "cell_reports"."extra_activity" AS t1_r4, "cell_reports"."remark" AS t1_r5, "cell_reports"."created_at" AS t1_r6, "cell_reports"."updated_at" AS t1_r7, "cells"."id" AS t2_r0, "cells"."name" AS t2_r1, "cells"."cell_type" AS t2_r2, "cells"."weekday" AS t2_r3, "cells"."hour" AS t2_r4, "cells"."minute" AS t2_r5, "cells"."address_id" AS t2_r6, "cells"."created_at" AS t2_r7, "cells"."updated_at" AS t2_r8, "cell_meeting_presences"."id" AS t3_r0, "cell_meeting_presences"."presence" AS t3_r1, "cell_meeting_presences"."member_id" AS t3_r2, "cell_meeting_presences"."cell_meeting_id" AS t3_r3, "cell_meeting_presences"."created_at" AS t3_r4, "cell_meeting_presences"."updated_at" AS t3_r5, "members"."id" AS t4_r0, "members"."name" AS t4_r1, "members"."date_of_birth" AS t4_r2, "members"."phone" AS t4_r3, "members"."cell_phone" AS t4_r4, "members"."marital_status" AS t4_r5, "members"."consort" AS t4_r6, "members"."username" AS t4_r7, "members"."crypted_password" AS t4_r8, "members"."password_salt" AS t4_r9, "members"."persistence_token" AS t4_r10, "members"."email" AS t4_r11, "members"."photo_file_name" AS t4_r12, "members"."photo_content_type" AS t4_r13, "members"."photo_file_size" AS t4_r14, "members"."photo_updated_at" AS t4_r15, "members"."created_at" AS t4_r16, "members"."updated_at" AS t4_r17, "members"."address_id" AS t4_r18, "members"."cell_id" AS t4_r19 FROM "cell_meetings" LEFT OUTER JOIN "cell_reports" ON "cell_reports".id = "cell_meetings".cell_report_id LEFT OUTER JOIN "cells" ON "cells".id = "cell_meetings".cell_id LEFT OUTER JOIN "cell_meeting_presences" ON cell_meeting_presences.cell_meeting_id = cell_meetings.id LEFT OUTER JOIN "members" ON "members".id = "cell_meeting_presences".member_id WHERE ("cell_reports"."id" = '2053932787') AND "cell_meetings".id IN (3) ORDER BY members.name ASC, cell_meetings.date ASC
  CellMeeting Load (0.0ms)   SELECT * FROM "cell_meetings" WHERE ("cell_meetings".cell_report_id = 2053932787) LIMIT 1
  Cell Load (0.0ms)   SELECT * FROM "cells" WHERE ("cells"."id" = 1)
database get the leaders
database get the supervisors
  Leader Load (0.0ms)   SELECT * FROM "leaders" WHERE ("leaders".cell_id = 1) LIMIT 1
database get the coordinators
  Supervisor Load (0.0ms)   SELECT * FROM "supervisors" INNER JOIN "leaders_supervisors" ON "supervisors".id = "leaders_supervisors".supervisor_id WHERE ("leaders_supervisors".leader_id = 1 ) LIMIT 1
  Member Load (0.0ms)   SELECT * FROM "members" WHERE ("members".cell_id = 1) ORDER BY name ASC
  CellMeeting Load Including Associations (110.0ms)   SELECT "cell_meetings"."id" AS t0_r0, "cell_meetings"."date" AS t0_r1, "cell_meetings"."cell_id" AS t0_r2, "cell_meetings"."cell_report_id" AS t0_r3, "cell_meetings"."created_at" AS t0_r4, "cell_meetings"."updated_at" AS t0_r5, "cell_reports"."id" AS t1_r0, "cell_reports"."date" AS t1_r1, "cell_reports"."teacher" AS t1_r2, "cell_reports"."assistance" AS t1_r3, "cell_reports"."extra_activity" AS t1_r4, "cell_reports"."remark" AS t1_r5, "cell_reports"."created_at" AS t1_r6, "cell_reports"."updated_at" AS t1_r7, "cells"."id" AS t2_r0, "cells"."name" AS t2_r1, "cells"."cell_type" AS t2_r2, "cells"."weekday" AS t2_r3, "cells"."hour" AS t2_r4, "cells"."minute" AS t2_r5, "cells"."address_id" AS t2_r6, "cells"."created_at" AS t2_r7, "cells"."updated_at" AS t2_r8, "cell_meeting_presences"."id" AS t3_r0, "cell_meeting_presences"."presence" AS t3_r1, "cell_meeting_presences"."member_id" AS t3_r2, "cell_meeting_presences"."cell_meeting_id" AS t3_r3, "cell_meeting_presences"."created_at" AS t3_r4, "cell_meeting_presences"."updated_at" AS t3_r5, "members"."id" AS t4_r0, "members"."name" AS t4_r1, "members"."date_of_birth" AS t4_r2, "members"."phone" AS t4_r3, "members"."cell_phone" AS t4_r4, "members"."marital_status" AS t4_r5, "members"."consort" AS t4_r6, "members"."username" AS t4_r7, "members"."crypted_password" AS t4_r8, "members"."password_salt" AS t4_r9, "members"."persistence_token" AS t4_r10, "members"."email" AS t4_r11, "members"."photo_file_name" AS t4_r12, "members"."photo_content_type" AS t4_r13, "members"."photo_file_size" AS t4_r14, "members"."photo_updated_at" AS t4_r15, "members"."created_at" AS t4_r16, "members"."updated_at" AS t4_r17, "members"."address_id" AS t4_r18, "members"."cell_id" AS t4_r19 FROM "cell_meetings" LEFT OUTER JOIN "cell_reports" ON "cell_reports".id = "cell_meetings".cell_report_id LEFT OUTER JOIN "cells" ON "cells".id = "cell_meetings".cell_id LEFT OUTER JOIN "cell_meeting_presences" ON cell_meeting_presences.cell_meeting_id = cell_meetings.id LEFT OUTER JOIN "members" ON "members".id = "cell_meeting_presences".member_id WHERE ("cell_reports"."id" = '2053932787') ORDER BY members.name ASC, cell_meetings.date ASC
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 3 AND ("cell_meeting_presences"."member_id" = 3)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 4 AND ("cell_meeting_presences"."member_id" = 3)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 5 AND ("cell_meeting_presences"."member_id" = 3)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 6 AND ("cell_meeting_presences"."member_id" = 3)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 7 AND ("cell_meeting_presences"."member_id" = 3)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 3 AND ("cell_meeting_presences"."member_id" = 4)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 4 AND ("cell_meeting_presences"."member_id" = 4)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 5 AND ("cell_meeting_presences"."member_id" = 4)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 6 AND ("cell_meeting_presences"."member_id" = 4)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 7 AND ("cell_meeting_presences"."member_id" = 4)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 3 AND ("cell_meeting_presences"."member_id" = 1)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 4 AND ("cell_meeting_presences"."member_id" = 1)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 5 AND ("cell_meeting_presences"."member_id" = 1)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 6 AND ("cell_meeting_presences"."member_id" = 1)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 7 AND ("cell_meeting_presences"."member_id" = 1)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 3 AND ("cell_meeting_presences"."member_id" = 2)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 4 AND ("cell_meeting_presences"."member_id" = 2)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 5 AND ("cell_meeting_presences"."member_id" = 2)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 6 AND ("cell_meeting_presences"."member_id" = 2)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 7 AND ("cell_meeting_presences"."member_id" = 2)) LIMIT 1
retrieve_report_info <--
database <--
0
wesgarrisonCommented:
What version of rails are you on?

Let's try getting the report first, then the meeting from that.

We'll do it all in the controller for now so that we can play around with it, then we can move it to a named_scope after we get it working.

Please put code and sql logs in a code window or use gist.github.com.
# try this instead:

@cell_report = CellReport.find(report)
@cellmeeting = @cell_report.cell_meetings.first(
  :include=>[:cell_report,:cell,{:cell_meeting_presences=>:member}],
  :order => "members.name ASC, cell_meetings.date ASC")

Open in new window

0
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

lusfernandosAuthor Commented:
Hi as I will need all the meeting I have changed a bit, however seems the :include clause is not bringing all the data needed.
rails= 2.3.4
    logger.debug "retrieve_report_info -->"
    logger.debug "find report"
    @cell_report = CellReport.find(report)
    logger.debug "retrieve meetings"    
    @cellmeetings = @cell_report.cell_meetings(:all,
      :include=>[:cell_report,:cell,{:cell_meeting_presences=>:member}],
      :order => "members.name ASC, cell_meetings.date ASC")

    #@cellmeetings = CellMeeting.report(report)
    #logger.debug "getting the report"
    #@cell_report =  @cellmeetings.first.cell_report
    logger.debug "getting the cell"
    @cell = @cellmeetings.first.cell


=============================== log
retrieve_report_info -->
find report
  CellReport Load (0.0ms)   SELECT * FROM "cell_reports" WHERE ("cell_reports"."id" = 2053932787) 
retrieve meetings
  CellMeeting Load (0.0ms)   SELECT * FROM "cell_meetings" WHERE ("cell_meetings".cell_report_id = 2053932787) 
getting the cell
  Cell Load (0.0ms)   SELECT * FROM "cells" WHERE ("cells"."id" = 1)  

Open in new window

0
wesgarrisonCommented:
Yeah, that doesn't look right at all.  The conditions aren't on there at all and the sorting isn't there either.

Try it with find() on association?
@cellmeetings = @cell_report.cell_meetings.find(:all,
      :include=>[:cell_report,:cell,{:cell_meeting_presences=>:member}],
      :order => "members.name ASC, cell_meetings.date ASC")

Open in new window

0
lusfernandosAuthor Commented:
looking a little better
1) Did not perform a select to get cell
2) Did not perform a select to get members (I changed the include to get members from cell)
3) It is still performing a select to get the members presence, please take a look at    retrieve_member_presence

* I tried putting it back to the namescope, but then it starts to do all selects again ...
 def retrieve_member_presence(member)
    member_presence = []
    @cellmeetings.each do |meeting|
      member_presence << meeting.cell_meeting_presences.first(:conditions=> {:member_id => member}).presence
    end
    return member_presence 
  end

  # ordering is important here.
  # It is considered that it is order by member and date.
  def build_members_presences()
    logger.debug "build_members_presences -->"
    @members_presence = []
    @members.each do |member|
      @members_presence << [member.name,member.date_of_birth,retrieve_member_presence(member.id)]
    end
    logger.debug "build_members_presences <--"
  end

  def retrieve_report_info(report)
    logger.debug "retrieve_report_info -->"
    logger.debug "find report"
    @cell_report = CellReport.find(report)
    logger.debug "retrieve meetings"
    @cellmeetings = @cell_report.cell_meetings.find(:all,
      :include=>[:cell_meeting_presences,:cell_report,:cell,{:cell=>:members}],
      :order => "members.name ASC, cell_meetings.date ASC")

    #logger.debug "retrieve meetings"
    #@cellmeetings = CellMeeting.report(report)
    logger.debug "getting the report"
    @cell_report =  @cellmeetings.first.cell_report
    logger.debug "getting the cell"
    @cell = @cellmeetings.first.cell
    logger.debug "getting the members"
    @members = @cellmeetings.first.cell.members
    logger.debug "database get the leaders"
    @leaders =  @cell.leaders
    logger.debug "database get the supervisors"
    @supervisors = @leaders.first.supervisors
    logger.debug "database get the coordinators"
    @coordinators = @supervisors.first.coordinators
    build_members_presences()
    logger.debug "retrieve_report_info <--"
  end



retrieve_report_info -->
find report
  CellReport Load (0.0ms)   SELECT * FROM "cell_reports" WHERE ("cell_reports"."id" = 2053932787) 
retrieve meetings
  CellMeeting Load Including Associations (78.0ms)   SELECT "cell_meetings"."id" AS t0_r0, "cell_meetings"."date" AS t0_r1, "cell_meetings"."cell_id" AS t0_r2, "cell_meetings"."cell_report_id" AS t0_r3, "cell_meetings"."created_at" AS t0_r4, "cell_meetings"."updated_at" AS t0_r5, "cell_meeting_presences"."id" AS t1_r0, "cell_meeting_presences"."presence" AS t1_r1, "cell_meeting_presences"."member_id" AS t1_r2, "cell_meeting_presences"."cell_meeting_id" AS t1_r3, "cell_meeting_presences"."created_at" AS t1_r4, "cell_meeting_presences"."updated_at" AS t1_r5, "cell_reports"."id" AS t2_r0, "cell_reports"."date" AS t2_r1, "cell_reports"."teacher" AS t2_r2, "cell_reports"."assistance" AS t2_r3, "cell_reports"."extra_activity" AS t2_r4, "cell_reports"."remark" AS t2_r5, "cell_reports"."created_at" AS t2_r6, "cell_reports"."updated_at" AS t2_r7, "cells"."id" AS t3_r0, "cells"."name" AS t3_r1, "cells"."cell_type" AS t3_r2, "cells"."weekday" AS t3_r3, "cells"."hour" AS t3_r4, "cells"."minute" AS t3_r5, "cells"."address_id" AS t3_r6, "cells"."created_at" AS t3_r7, "cells"."updated_at" AS t3_r8, "cells_cell_meetings"."id" AS t4_r0, "cells_cell_meetings"."name" AS t4_r1, "cells_cell_meetings"."cell_type" AS t4_r2, "cells_cell_meetings"."weekday" AS t4_r3, "cells_cell_meetings"."hour" AS t4_r4, "cells_cell_meetings"."minute" AS t4_r5, "cells_cell_meetings"."address_id" AS t4_r6, "cells_cell_meetings"."created_at" AS t4_r7, "cells_cell_meetings"."updated_at" AS t4_r8, "members"."id" AS t5_r0, "members"."name" AS t5_r1, "members"."date_of_birth" AS t5_r2, "members"."phone" AS t5_r3, "members"."cell_phone" AS t5_r4, "members"."marital_status" AS t5_r5, "members"."consort" AS t5_r6, "members"."username" AS t5_r7, "members"."crypted_password" AS t5_r8, "members"."password_salt" AS t5_r9, "members"."persistence_token" AS t5_r10, "members"."email" AS t5_r11, "members"."photo_file_name" AS t5_r12, "members"."photo_content_type" AS t5_r13, "members"."photo_file_size" AS t5_r14, "members"."photo_updated_at" AS t5_r15, "members"."created_at" AS t5_r16, "members"."updated_at" AS t5_r17, "members"."address_id" AS t5_r18, "members"."cell_id" AS t5_r19 FROM "cell_meetings" LEFT OUTER JOIN "cell_meeting_presences" ON cell_meeting_presences.cell_meeting_id = cell_meetings.id LEFT OUTER JOIN "cell_reports" ON "cell_reports".id = "cell_meetings".cell_report_id LEFT OUTER JOIN "cells" ON "cells".id = "cell_meetings".cell_id LEFT OUTER JOIN "cells" cells_cell_meetings ON "cells_cell_meetings".id = "cell_meetings".cell_id LEFT OUTER JOIN "members" ON members.cell_id = cells_cell_meetings.id WHERE ("cell_meetings".cell_report_id = 2053932787) ORDER BY members.name ASC, cell_meetings.date ASC
getting the report
getting the cell
getting the members
database get the leaders
database get the supervisors
  Leader Load (0.0ms)   SELECT * FROM "leaders" WHERE ("leaders".cell_id = 1) LIMIT 1
database get the coordinators
  Supervisor Load (0.0ms)   SELECT * FROM "supervisors" INNER JOIN "leaders_supervisors" ON "supervisors".id = "leaders_supervisors".supervisor_id WHERE ("leaders_supervisors".leader_id = 1 ) LIMIT 1
build_members_presences -->
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 3 AND ("cell_meeting_presences"."member_id" = 3)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 4 AND ("cell_meeting_presences"."member_id" = 3)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 5 AND ("cell_meeting_presences"."member_id" = 3)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 6 AND ("cell_meeting_presences"."member_id" = 3)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 7 AND ("cell_meeting_presences"."member_id" = 3)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 3 AND ("cell_meeting_presences"."member_id" = 4)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 4 AND ("cell_meeting_presences"."member_id" = 4)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 5 AND ("cell_meeting_presences"."member_id" = 4)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 6 AND ("cell_meeting_presences"."member_id" = 4)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 7 AND ("cell_meeting_presences"."member_id" = 4)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 3 AND ("cell_meeting_presences"."member_id" = 1)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 4 AND ("cell_meeting_presences"."member_id" = 1)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 5 AND ("cell_meeting_presences"."member_id" = 1)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 6 AND ("cell_meeting_presences"."member_id" = 1)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 7 AND ("cell_meeting_presences"."member_id" = 1)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 3 AND ("cell_meeting_presences"."member_id" = 2)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 4 AND ("cell_meeting_presences"."member_id" = 2)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 5 AND ("cell_meeting_presences"."member_id" = 2)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 6 AND ("cell_meeting_presences"."member_id" = 2)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 7 AND ("cell_meeting_presences"."member_id" = 2)) LIMIT 1
build_members_presences <--
retrieve_report_info <--

Open in new window

0
wesgarrisonCommented:
Something like this?
:include=>[:cell_report,:cell=>{:members =>{:cell_meeting_presences}}

Open in new window

0
lusfernandosAuthor Commented:
Not sure ! seems the same thing ...
I am wondering about the following code, is there something I am accessing that cause a database fetch? Perhaps there is some other way of doing it?



  def retrieve_member_presence(member)
    member_presence = []
    @cellmeetings.each do |meeting|
      member_presence << meeting.cell_meeting_presences.first(:conditions=> {:member_id => member}).presence
    end
    return member_presence 
  end

========
the log is:

build_members_presences -->
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 4 AND ("cell_meeting_presences"."member_id" = 3)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 4 AND ("cell_meeting_presences"."member_id" = 4)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 4 AND ("cell_meeting_presences"."member_id" = 1)) LIMIT 1
  CellMeetingPresence Load (0.0ms)   SELECT * FROM "cell_meeting_presences" WHERE ("cell_meeting_presences".cell_meeting_id = 4 AND ("cell_meeting_presences"."member_id" = 2)) LIMIT 1
build_members_presences <--

Open in new window

0
wesgarrisonCommented:
I'm not sure how your relationships are set up, but I'm going to suggest that the amount of finagling we're doing to try to get it working indicates that maybe we should revisit the root problem: the relationships aren't right.

Also, I wouldn't worry about prematurely optimizing down to the nth detail.

That said, I've found the bullet plugin (http://github.com/flyerhzm/bullet) to be excellent at diagnosing where you need :include and :joins (and where you don't!) so I suggest you try that as it'll help you more directly than I can without knowing all your relationships.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Ruby

From novice to tech pro — start learning today.