CODEWITHSUNDEEP
mysqlsql
Stefan
Stefan

Reputation: 3051

Complex MySQL Query 1-N Relationship

I'm currently designing a database (MySQL) for drink recipes and want users to be able to enter what ingredients they have and I will give them all recipes that include only those ingredients. What I need is a query where I can select all drinks from table drinks where the only ingredients are like (the reason for this is someone may enter vodka or smirnoff vodka but I will treat them as the same thing) the ingredients in the table user_ingredients

Here are the three tables that will be involved in this query:

drinks

CREATE TABLE `drinks` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `drink` varchar(64) NOT NULL DEFAULT '',
  `glass` int(11) unsigned NOT NULL,
  `instructions` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_drink` (`drink`),
  KEY `in_id` (`id`),
  KEY `fk_glass` (`glass`),
  CONSTRAINT `fk_glass` FOREIGN KEY (`glass`) REFERENCES `glasses` (`id`) ON DELETE NO         ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=11946 DEFAULT CHARSET=utf8;

recipe_ingredients

CREATE TABLE `drink_ingredients` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `drink` int(11) unsigned NOT NULL,
  `ingredient` int(11) unsigned NOT NULL,
  `amount` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `in_drink` (`drink`),
  KEY `in_ingredient` (`ingredient`),
  KEY `fk_amount` (`amount`),
  CONSTRAINT `fk_amount` FOREIGN KEY (`amount`) REFERENCES `amounts` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_drink` FOREIGN KEY (`drink`) REFERENCES `drinks` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ingredient` FOREIGN KEY (`ingredient`) REFERENCES `ingredients` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=46026 DEFAULT CHARSET=utf8;

user_ingredients

CREATE TABLE `user_ingredients` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user` int(11) unsigned DEFAULT NULL,
  `ingredient` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_user_ingredient` (`ingredient`),
  CONSTRAINT `fk_user_ingredient` FOREIGN KEY (`ingredient`) REFERENCES `ingredients` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Thank you, I've been stuck on this for awhile. -Stefan

Upvotes: 2

Views: 722

Answers (2)

tomfumb
tomfumb

Reputation: 3759

I think your two criteria:

  1. recipes with only the user's entered ingredients, and
  2. matching ambiguous ingredients with 'like'

are incompatible. Imagine you have a (hideous) cocktail with red & white wine (unlikely I know but just an example) and your user enters 'wine'. Now you don't know if they have the right ingredients for your drink

I would say if possible it makes more sense to build a definitive list of permitted ingredients (e.g. a distinct list of all ingredients available in your database) and provide a very easy method for users to suggest additions.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562731

You can find the recipes for which the user has all ingredients by using an outer join. If a given ingredient is not found in the user's supply, ui.* will be NULL.

Then count the ingredients for the drink, compare to the count of matching ingredients in the user's supply (NULLs don't count), and if the counts are equal, then all the ingredients are matched by the user's supply.

SELECT d.*
FROM drinks d
INNER JOIN drink_ingredient di ON d.id = di.drink
LEFT OUTER JOIN user_ingredients ui 
    ON di.ingredient = ui.ingredient AND ui.user = :user
GROUP BY d.id
HAVING COUNT(di.ingredient) = COUNT(ui.ingredient);

Even if the user has additional ingredients that are not part of this drink's recipe, I assume that's okay, those ingredients will just stay in the cupboard for next time. :-)

Matching a user's ingredient input to the canonical row in the ingredients table is another matter. That is, populating the user_ingredients table so the numeric foreign keys reference the correct row. You can do that during the user's data entry where they list the ingredients they have.

INSERT INTO user_ingredients (user, ingredient)
SELECT :user, i.id
FROM ingredients i
WHERE :ingredient REGEXP CONCAT('[[:<:]]', i.name, '[[:>:]]');

So the user's input 'smirnoff vodka' matches the regular expression '[[:<:]]vodka[[:>:]]'

Note that a substring match like this is hard to optimize. It'll have to run a full table-scan. But I assume you only have a few hundred rows in the ingredients table, so it won't be too bad. Otherwise, you'd have to use a fulltext indexing solution.

Upvotes: 2

Related Questions