Retrieving hierarchical config values with Eloquent

Hi All,

I have Three tables: companies, markets, and locations. A company has one or more markets and a market has one or more locations. I also have a configurations table. The companies, markets, and locations tables each has a nullable configuration_id field and Company, Market, and Location each have a hasOne() relationship to Configuration. (Configuration has a belongsTo() relationship to each of the three models.) The purpose is so that configuration settings can be defined at any of those three levels in an overriding fashion, with company being the default and location being the most granular. In other words, if a configuration_id is defined at the location level then that is used. Otherwise if locations.configuration_id is null and markets.configuration_id is populated then markets.configuration_id is used. Otherwise, companies.configuration_id is used.

I'm looking for the most elegant way to figure out which configuration record to use based on the company > market > location hierarchy. This can be accomplished in raw SQL (MySQL) like so:

SELECT IFNULL(locations.configuration_id,
    IFNULL(markets.configuration_id,
        companies.configuration_id
    )
) AS configuration_id
FROM locations
JOIN markets ON markets.id = locations.market_id
JOIN companies ON companies.id = markets.company_id
WHERE locations.id = 1

Is there a GOOD way to do this in Eloquent short of reproducing this query with some raw() statements? Or does anyone have a suggestion about a better way to solve the problem of allowing for overriding configuration in this scenario? Thanks in advance!

luoshiben

11 years ago

Level 2

Here's a pretty good Stack answer, and another great article from @philipbrown on different config storage strategies if anyone is interested:

http://stackoverflow.com/questions/10204902/database-design-for-user-settings

http://culttt.com/2015/02/02/storing-user-settings-relational-database/

I believe that my general strategy works well for the scenario, but am still wondering if there's a cleaner way to get what I need from the database without the raw query. Right now I'm doing $results = \DB::select( $bigRawSqlUsingIfNulls, [$params] );.

luoshiben

11 years ago

Level 2

I still haven't found a better way of dealing with a hierarchy of config values as described in my original post, so now I'm on to attempting to add my raw query onto the model relationship definition, or through a query scope of some kind. The setup looks like this:

| configuration |
-----------------
| id            |
| some_field    |

| location         |
--------------------
| id               |
| market_id        |
| configuration_id |
| some_field       |

| market           |
--------------------
| id               |
| company_id       |
| configuration_id |
| some_field       |

| company          |
--------------------
| id               |
| configuration_id |
| some_field       |

class Configuration extends Model {
    public function location() {
        return $this->belongsTo('Location');
    }
    public function market() {
        return $this->belongsTo('Market');
    }
    public function company() {
        return $this->belongsTo('Company');
    }
}

class Location extends Model {
    public function configuration() {
        return $this->hasOne('Configuration');
    }
}

What I want to do is Location::where('id',1)->with('configuration'); and have the correct Configuration object returned with the Location. The id of the associated Configuration record will depend on the Location > Market > Company hierarchy of nullable configuration_ids, as illustrated by the query in my first post. I've started messing with doing this in the Location->configuration() method with something like return $this->hasOne('Configuration')->whereRaw('...big sql query to find proper config_id...');. However, my join depends on the location_id in order to not return multiple records so I haven't figured out how to apply this correctly. Any ideas?? Thanks!

luoshiben

11 years ago

Level 2

As another thought, maybe (probably) I'm making this more complex than it needs to be. Instead of setting the configuration_id fields as nullable and only having the applicable field set depending on which level of the hierarchy is defining the settings, I could simply populate ALL configuration_id values all the time. In other words, if configuration is set at the company level, then I would set the same configuration_id on the company, related markets, and related locations. If configuration needed to be overridden at the market level for a specific market, I update that market's configuration_id value and all related location configuration_ids also. This way, no matter which entity I'm looking at, the proper configuration_id is always readily available. Of course, this means that my code needs to ensure that all of the configuration_ids are set correctly in a cascading manner, but that's easy enough to do. Anyone see problems with this approach? (Or, have a completely different and better take on this whole thing?)

Please or to participate in this conversation.