current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 8 down vote favorite

I have the following tables set up:

t_users table:

|----------------------------------|
|uid | name   | email              |
|----------------------------------|
|1   | Maria  | [email protected]  |
|2   | George | [email protected] |
|----------------------------------|

t_settings table:

|---------------------------------|
|t_sid | name     | default_value |
|---------------------------------|
|1     | uicolour | #f00          |
|2     | language | en            |
|---------------------------------|

t_users_settings table:

|--------------------|
|uid | t_sid | value |
|--------------------|
|2   | 1     | #000  |
|1   | 2     | es    |
|--------------------|

What I want, is for each user, to be able to pull all of their custom settings, and also pull the default settings for anything that they haven't changed. I have the following query that works:

SELECT t_users.*, t_users_settings.*, t_settings.*
FROM t_users 
LEFT JOIN t_users_settings ON t_users.uid = t_users_settings.t_uid  
LEFT JOIN t_settings ON t_settings.t_sid != t_users_settings.t_sid
WHERE t_users_settings.t_sid IS NOT NULL 
AND t_users_settings.t_uid IS NOT NULL 
AND t_users.uid = '<input user id here>';

However, it appears that if I add more settings (Such as country), I then have to add another JOIN and AND statement. I think there is something missing something in the setup, or I'm just not familiar enough with MySQL to format the query correctly.

Little help?

share|improve this question

2 Answers 2

up vote 5 down vote accepted

At first glance, there are some alarm bells ringing.

  • You have t_users_settings.t_sid IS NOT NULL AND t_users_settings.t_uid IS NOT NULL. The first seems to imply you might have NULL values in your t_users_settings.t_sid column. The second seems to defeat the purpose of doing a left join to t_users_settings in the first place.
  • A second concern is that you're over-generalising.
    Notice that you're unable to choose a specific type for your t_users_settings.value column. This means you have to roll your own custom type checking or do without.
    Yes your design allows you to add new settings "without code changes". But in reality, those settings would be meaningless without code changes to make use of them in the first place.

You could probably solve your current problems quite easily by defining your settings table with a separate column for each possible setting, and doing the minimal work of adding columns as you add new settings.

However, that said here's a suggestion based on your current design.

First you want to get all permutations of settings and users. For that you need a cross join.

SELECT  u.*, s.*
FROM    t_users u
        CROSS JOIN t_settings s

That gives all combinations with their defaults. Now you want a left outer join to find out which defaults have been overridden for each user.

SELECT  u.*, s.*
FROM    t_users u
        CROSS JOIN t_settings s
        LEFT JOIN t_users_settings us ON
            us.uid = u.uid
        AND us.t_sid = s.t_sid

Finally, cleanup it up with explicit column selection, and you can even derive the actual users_value with a COALESCE, ISNULL or CASE statement.

SELECT  u.uid,
        u.name,
        u.email,
        s.t_sid,
        s.name,
        s.default_value,
        COALESCE(us.value, s.default_value) AS users_value
FROM    t_users u
        CROSS JOIN t_settings s
        LEFT JOIN t_users_settings us ON
            us.uid = u.uid
        AND us.t_sid = s.t_sid
share|improve this answer
up vote 2 down vote

Your query is much too complicated. Get rid of the WHERE clauses and add

IFNULL(value, default_value) user_value

That's all.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.