CODEWITHSUNDEEP
mysql
Mark van Herpen
Mark van Herpen

Reputation: 347

How to select from Many-to-Many relation with optional junction table?

I have a user-settings setup with a so called 'property bag' I guess. I want to store settings for my users. Most users won't change the default setting, so I thought I should make a 'default value' for each setting. This way I don't have store a user_setting record for each setting for each user.

This is my (simplified) mysql database:

CREATE TABLE `user` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`user_id`)
);

CREATE TABLE `setting` (
  `setting_id` INT NOT NULL AUTO_INCREMENT,
  `key` VARCHAR(100) NOT NULL,
  `default_value` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`setting_id`)
);  

CREATE TABLE `user_setting` (
  `user_setting_id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `setting_id` INT NOT NULL,
  `value` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`user_setting_id`),
  CONSTRAINT `fk_user_setting_1`
    FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_user_setting_2`
    FOREIGN KEY (`setting_id`)
    REFERENCES `setting` (`setting_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

INSERT INTO `user` VALUES (1, 'username1'),(2, 'username2');
INSERT INTO `setting` VALUES (1, 'key1', 'somevalue'),(2, 'key2', 'someothervalue');

In my code I can easy do a lookup for each setting for each user. By checking if there is a row in the user_setting table, I know that this is other then the default value.

But is there a way to get an overview for all the settings for each user? Normaly I would left-join the user -> user_setting -> setting tables for each user, but now I don't have a user_setting record for each user/setting. Is this possible with a single query?

Upvotes: 1

Views: 198

Answers (2)

Paul Dixon
Paul Dixon

Reputation: 300845

If you just had a cartesian join of user against setting, you'll get one row for every user/setting combination. Then simply left join the user_setting table and you can pick up the overridden value when it exists.

So something like this:

SELECT u.user_id, s.key, s.default_value, us.value 
FROM user u, setting s
LEFT JOIN user_setting us 
  ON(us.user_id=u.user_id AND us.setting_id=s.setting_id)
ORDER BY u.user_id, s.key

You could refine this further using IFNULL so that you get the value of the setting regardless of whether it's overridden or default:

SELECT u.user_id, s.key, IFNULL(us.value , s.default_value) AS value
FROM user u, setting s
LEFT JOIN user_setting us 
  ON(us.user_id=u.user_id AND us.setting_id=s.setting_id)
ORDER BY u.user_id, s.key

Upvotes: 1

Mark van Herpen
Mark van Herpen

Reputation: 347

(Answering my own question isn't the way I normaly work, but I'm not sure if this is the correct answer and it's based on Paul Dixon's answer)

As mentioned, a cartesian join is needed between user and setting. The correct query would be:

SELECT u.user_id, s.key, IFNULL(us.value , s.default_value) AS value
FROM user u 
CROSS JOIN setting s 
LEFT JOIN user_setting us ON
(us.user_id=u.user_id AND us.setting_id=s.setting_id) 
ORDER BY u.user_id, s.key;

Upvotes: 0

Related Questions