Internal counting of object collection
Reported by ander | June 16th, 2011 @ 09:10 AM
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?
Comments and changes to this ticket
Please Sign in or create a free account to add a new ticket.
With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป