Here is my request to the database:
@certificates = Certificate.paginate page: params[:page],
per_page: 50, conditions: search_conditions(params), order:
sort_order('not_after_date', 'up'), select: ", count(id) as count",
group: 'subject_common_name', include: [:clones]
Here are the resulting database queries:
1) SELECT id, verify_return_code, not_after_date,
not_before_date, issuer, subject, subject_common_name, lead_id,
count(id) as count FROM certificates WHERE
(verify_return_code = '0' AND not_after_date >= '2011-06-15')
GROUP BY subject_common_name ORDER BY not_after_date ASC LIMIT 50
OFFSET 0
2) SELECT certificates.* FROM
certificates WHERE
(certificates.subject_common_name IN
('foo.ru','bar.ru','bas.ua'))
3) SELECT COUNT(DISTINCT
certificates.id) AS count_id,
subject_common_name AS subject_common_name FROM
certificates LEFT OUTER JOIN certificates
clones_certificates ON
clones_certificates.subject_common_name =
certificates.subject_common_name WHERE
(verify_return_code = '0' AND not_after_date >= '2011-06-15')
GROUP BY subject_common_name
The first query gets grouped certificate collection.
The second query is made for eager loading of associated objects.
And the :clones refers to the same Certificate model.
But the last query results in error message from mysql:
ActiveRecord::StatementInvalid (Mysql2::Error: Column
'subject_common_name' in field list is ambiguous: SELECT
COUNT(DISTINCT certificates.id) AS
count_id, subject_common_name AS subject_common_name FROM
certificates LEFT OUTER JOIN certificates
clones_certificates ON
clones_certificates.subject_common_name =
certificates.subject_common_name WHERE
(verify_return_code = '0' AND not_after_date >= '2011-06-15')
GROUP BY subject_common_name)
If I omit group or include parameters everything goes ok. But I
need them both.
I don't use joins parameter. And the include parameter doesn't
affect the size of the resulting collection. So I don't think that
will_paginate should use joins when trying to count the objects in
the database.
How this behaviour can be bypassed?