Sometimes, you can
squeeze multiple calls by Active Record into just one SQL query. It??™s possible to turn this example into
a custom SQL query. You can do that in a few ways, but they all require either subqueries or temporary
tables. Here??™s a replacement query loop that uses custom SQL:
users = User.find_by_sql("
SELECT users_meetings.*,
COALESCE(rewards_1.description, '') as reward_description
FROM
(SELECT users.*,
COUNT(meetings.id) as meeting_count
FROM (users
RIGHT OUTER JOIN
meetings
ON meetings.assigned_user_id=
users.id)
GROUP BY
meetings.assigned_user_id
) as users_meetings
INNER JOIN
rewards as rewards_1
ON rewards_1.meeting_count <
users_meetings.meeting_count
AND NOT EXISTS (
SELECT *
FROM rewards as rewards_2
WHERE (rewards_2.meeting_count <
users_meetings.meeting_count)
AND
(rewards_2.meeting_count >
rewards_1.meeting_count))
CHAPTER 8 n CREATING SALES PERFORMANCE REPORTS WITH SUGARCRM 166
ORDER BY last_name ASC, first_name ASC
;
")
end
It??™s definitely more complicated, but it works.
Pages:
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241