query for ActiveRecord relationship

[markbrown4]

Hi,

My two Model objects are FeatureMaster and Feature,
I need to be able to get the clients value from the Feature table if it exists or it gets the default from FeatureMaster.

SELECT COALESCE(f.VALUE, fm.VALUE) AS VALUE
  FROM feature_master AS fm
  LEFT OUTER JOIN feature AS f
    ON f.client_id=1
    AND f.feature_master_id = fm.id
 WHERE fm.feature_key = 'ManageUsers')

How would achieve this in ActiveRecord?

Thanks,

[Louis Simoneau]

I'm not an expert, so don't take my word for it, but I don't think ActiveRecord has an abstraction for that (it would probably end up making more than one query if you tried to do it in ruby)...

That said, there's always find_by_sql : just use your own SQL and pull the results into activerecord: http://api.rubyonrails.org/classes/A...e.html#M001781

[markbrown4]

I'm not an expert, so don't take my word for it, but I don't think ActiveRecord has an abstraction for that (it would probably end up making more than one query if you tried to do it in ruby)...

That said, there's always find_by_sql : just use your own SQL and pull the results into activerecord: http://api.rubyonrails.org/classes/A...e.html#M001781

Thanks Louis,

If you were to tackle it which way would you go:

• Find Feature, if no record Find Feature Master
• FeatureMaster.find_by_sql()
• Create a view

[Louis Simoneau]

Well, I'm a bit wary of premature optimization: in theory the find_by_sql method is faster, but it's also a bit less readable, and unless you run into a bottleneck my instinct would be to put it in controller logic, so the first of your proposed options.

[markbrown4]

Point taken,

In this case I am wanting to make it as efficient as possible because Features(Client wide enabled functionality) and a very simliar type of concept Permissions(User enabled functionality) are going to be used extensively on the majority of pages.
e.g.

if has_feature('ManageUsers')
  // do something
end
if has_feature('OtherFeature')
  // do something else
end

I'm even considering baking this out or saving these values in session or something simliar so I only need to query all the features and permissions for a user on login.

SELECT COALESCE(f.VALUE, fm.VALUE) AS VALUE
  FROM feature_master AS fm
  LEFT OUTER JOIN feature AS f
    ON f.client_id=1
    AND f.feature_master_id = fm.id
 WHERE fm.feature_key IN ('ManageUsers','OtherFeature','AnotherFeature')

Given that info would you still make up to 10 queries per page for this data?

Thanks Louis,

[Louis Simoneau]

Yeah, if you're using it that often you could either pull it in with your current_user when you load that or put it in the session as you suggest.

But maybe it's worth taking a step back. I'm a little confused by the architecture: what other data is stored in the database along with these features? Can users modify features in any way or are you the only one that does that? Why do you have feature_master as well as feature?

[markbrown4]

Yeah, if you're using it that often you could either pull it in with your current_user when you load that or put it in the session as you suggest.

But maybe it's worth taking a step back. I'm a little confused by the architecture: what other data is stored in the database along with these features? Can users modify features in any way or are you the only one that does that? Why do you have feature_master as well as feature?

The client cannot set the value of a feature.
I will use it to limit which parts of the application a particular client has access to. A feature is key value pair where the value can be anything True/False, a URL, etc.

FeatureMaster contains all of the features for the app and their default values:
id
key
value

Feature contains a client specific override:
feature_master_id
client_id
value

When I develop a new piece of functionality I can limit who has access to it - It means that I can configure the app per client.

[Louis Simoneau]

Ah right. Why not load in all the feature values from both tables when you first load the user? That way you're dealing with just the one query per request, to grab the current user, and after that you just play with that object.