SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  1. #1
    padawan silver trophybronze trophy markbrown4's Avatar
    Join Date
    Jul 2006
    Location
    Victoria, Australia
    Posts
    4,115
    Mentioned
    28 Post(s)
    Tagged
    2 Thread(s)

    query for ActiveRecord relationship

    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.
    Code SQL:
    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,

  2. #2
    Team ********* Louis Simoneau's Avatar
    Join Date
    Sep 2009
    Location
    Melbourne, Australia
    Posts
    228
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    padawan silver trophybronze trophy markbrown4's Avatar
    Join Date
    Jul 2006
    Location
    Victoria, Australia
    Posts
    4,115
    Mentioned
    28 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Louis Simoneau View Post
    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

  4. #4
    Team ********* Louis Simoneau's Avatar
    Join Date
    Sep 2009
    Location
    Melbourne, Australia
    Posts
    228
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    padawan silver trophybronze trophy markbrown4's Avatar
    Join Date
    Jul 2006
    Location
    Victoria, Australia
    Posts
    4,115
    Mentioned
    28 Post(s)
    Tagged
    2 Thread(s)
    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.
    Code:
    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.
    Code SQL:
    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,

  6. #6
    Team ********* Louis Simoneau's Avatar
    Join Date
    Sep 2009
    Location
    Melbourne, Australia
    Posts
    228
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?

  7. #7
    padawan silver trophybronze trophy markbrown4's Avatar
    Join Date
    Jul 2006
    Location
    Victoria, Australia
    Posts
    4,115
    Mentioned
    28 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Louis Simoneau View Post
    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.

  8. #8
    Team ********* Louis Simoneau's Avatar
    Join Date
    Sep 2009
    Location
    Melbourne, Australia
    Posts
    228
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •