#275 new
ander

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.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Shared Ticket Bins

People watching this ticket

Pages