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