This project is archived and is in readonly mode.

#2118 ✓resolved
Patrick Joyce

AssociationCollection.count Returns Incorrect Value

Reported by Patrick Joyce | March 3rd, 2009 @ 03:33 PM | in 3.0.2

AssociationCollection.count can return an incorrect value if a finder_sql is a multiline string and there is a new line character immediately following "SELECT". This issue affects HasManyAssociations, HasAndBelongToManyAssociations, and HasManyThroughAssociations.

The root of the problem is that when construct_sql generates the counter_sql from the finder_sql it uses a regex that includes a space after the SELECT. This is the current regex:

/SELECT (\/\*.*?\*\/ )?(.*)\bFROM\b/im

The solution is to match any word break character after the SELECT

/SELECT\b(\/\*.*?\*\/ )?(.*)\bFROM\b/im

I added tests to show the problem to both the HasManyAssociationsTest and HasAndBelongsToManyAssociationsTest. I did not add a test to the HasManyThroughAssociationsTest because there were no existing tests for using finder_sql and counter_sql.


To show the issue in the HasManyAssociationsTest I needed to add an additional client fixture. This is because of the way that AssociationsCollection count uses connection.select_value to execute the counter_sql. select_value returns a single value from a record. If there are multiple values in the record, it returns the first element of the hash. In this case, that was the rating for companies(:another_client) and the rating happened to be 1, which was the expected count. I added another company that was a client of companies(:another_firm) so that the expected count would be 2. This required changing several tests to reflect that there now was another company and largely accounts for the size of my patch.

In addition to changing the regular expression to respect line breaks immediately following the SELECT statement in finder_sql, I also refactored the code that sets the counter_sql into a method, construct_counter_sql, on the base AssociationCollection. I did this because the code to generate the counter_sql was identical in HasManyCollection, HasAndBelongsToManyCollection, and HasManyThroughCollection. Modifying the regex in 3 separate places felt dirty.

Comments and changes to this ticket

Create your profile

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

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href=""></a>


Referenced by