# Dynamic Finders and :finder\_sql

**URL:** <https://www.ruby-forum.com/t/dynamic-finders-and-finder-sql/90441>\
**Category:** Rails\
**Created:** [March 28, 2007, 9:44pm UTC](https://www.ruby-forum.com/t/dynamic-finders-and-finder-sql/90441 "2007-03-28T21:44:23Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![mudge](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/258eb7/32.png) [@mudge](https://www.ruby-forum.com/u/mudge)\
**Post date:** [March 28, 2007, 9:44pm UTC](https://www.ruby-forum.com/t/dynamic-finders-and-finder-sql/90441/1 "2007-03-28T21:44:23Z")

</div>

In order to handle the access rights in my application (where users  
only have access to designs within their own group and belonging to a  
customer that they are associated with), I have the following has\_many  
association in the User model:

has\_many :designs,  
:finder\_sql =\> ‘SELECT DISTINCT designs.\* FROM designs INNER JOIN  
(users, groups\_users) ON designs.user\_id = users.id AND  
groups\_users.user\_id = users.id WHERE (groups\_users.group\_id IN  
(SELECT resources.id FROM resources INNER JOIN (groups\_users) ON  
groups\_users.group\_id = resources.id WHERE groups\_users.user\_id =  
#{id}) AND designs.customer\_id IN (SELECT companies.id FROM companies  
INNER JOIN (customers\_users) ON customers\_users.customer\_id =  
companies.id WHERE customers\_users.user\_id = #{id}))’,  
:counter\_sql =\> ‘SELECT COUNT(_) FROM (SELECT DISTINCT designs._  
FROM designs INNER JOIN (users, groups\_users) ON designs.user\_id =  
users.id AND groups\_users.user\_id = users.id WHERE  
(groups\_users.group\_id IN (SELECT resources.id FROM resources INNER  
JOIN (groups\_users) ON groups\_users.group\_id = resources.id WHERE  
groups\_users.user\_id = #{id}) AND designs.customer\_id IN (SELECT  
companies.id FROM companies INNER JOIN (customers\_users) ON  
customers\_users.customer\_id = companies.id WHERE  
customers\_users.user\_id = #{id}))) AS d’

The association works fine when used in the following simple cases:

user.designs  
user.designs.count  
user.designs.find(1)

However it does not support any of the dynamic finders:

user.designs.find(:first)  
user.designs.find(:all)  
user.designs.find\_by\_reference(‘S’)

The problem is entirely down to the SQL that Rails itself injects into  
my custom finder\_sql; the main culprit being the addition of the  
following:

SELECT \* FROM designs WHERE (

This is appended to the beginning of my statement thereby rendering it  
invalid.

Is there anyway to modify Rails’ behaviour in this situation so that  
it only adds its conditions to the end of my existing finder\_sql? Or  
perhaps there is something inherently wrong with the format of my  
otherwise-working finder\_sql which is causing Rails to append the  
SELECT?

Here is a sample query generated by Rails when performing  
user.designs.find\_by\_reference(“REF”):

SELECT \* FROM designs WHERE (SELECT \* FROM designs INNER JOIN (users,  
groups\_users) ON designs.user\_id = users.id AND groups\_users.user\_id =  
users.id WHERE (groups\_users.group\_id IN (SELECT resources.id FROM  
resources INNER JOIN (groups\_users) ON groups\_users.group\_id =  
resources.id WHERE groups\_users.user\_id = 1) AND designs.customer\_id  
IN (SELECT companies.id FROM companies INNER JOIN (customers\_users) ON  
customers\_users.customer\_id = companies.id WHERE  
customers\_users.user\_id = 1))) AND (designs.`reference` = ‘REF’)  
LIMIT 1

Thanks in advance,

– Paul