Select closest match from database

[Adamhumbug]

HI All

Not sure how best to describe what i am trying to do so here goes.

People use my system to order food from a menu - this is not for a restaurant where you order one starter main and desert, they will be ordering 00's of meals.

The table layout is as follows:

ssm_menu

menu_id | menu_name | menu_price

ssm_menu_connection

menu_id | menu_item_id | surrogate_id

ssm_menu_items

menu_item_id | menu_item_name | menu_item_category

ssm_menu_order

job_id | menu_id | menu_item_id | menu_item_qty

Menu items can appear on more than one menu.

If a user orders 100 of menu_item_id 1, 2 & 3 these appear on menu_id 1& 2

Menu_id 1 only contains item_id 1, 2, 3

Menu_id 2 contains menu_item 1,2,3,4,5,6,7,8,910

when querying the database, i would like the menu_id to comeback as 1 as all of the items appear on this menu and out of all available menu items the higher percentage have  been picked from this menu than menu_id 2.

So on menu_id 1 = 100% of the available menu_item_id have been given a menu_item_qty but on menu_id 2, only 30% of the available menu_item_id have been selected.

the sql that i have started with is the following:

select menu_name 
from ssm_menu a
inner join ssm_menu_connection b
on a.menu_id = b.menu_id
inner join ssm_menu_items c
on b.menu_item_id = c.menu_item_id
inner join ssm_menu_order d
on c.menu_item_id = d.menu_item_id
where job_id = 27

The result of this is:

Menu One, Menu One, Menu One, Menu Two, Menu Two, Menu Two

I hope this is enough information to shed some light on what i am trying to achieve and appreciate any feedback in advance.

Kind Regards

Adam

[Barand]

Can you provide some actual data that might give us more of an insight into what you want - a dump of those tables perhaps?

[Adamhumbug]

9 minutes ago, Barand said:

Can you provide some actual data that might give us more of an insight into what you want - a dump of those tables perhaps?

ssm_menu

ssm_menu_connection

ssm_menu_items

ssm_menu_order

 

 

Edited December 10, 2019 by Adamhumbug

[Barand]

Tell me how to load those images into a test database and I'll continue to help.

[Adamhumbug]

1 minute ago, Barand said:

Tell me how to load those images into a test database and I'll continue to help.

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;


CREATE TABLE `ssm_menu` (
  `menu_id` int(11) NOT NULL,
  `menu_name` varchar(200) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `menu_price` decimal(6,2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `ssm_menu` (`menu_id`, `menu_name`, `menu_price`) VALUES
(1, 'Menu One', '22.90'),
(2, 'Private Dinner Party/Wedding - 3 Course Fine Dining Menu', '79.50');

CREATE TABLE `ssm_menu_connection` (
  `menu_id` int(11) NOT NULL,
  `menu_item_id` int(11) NOT NULL,
  `surrogate_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `ssm_menu_connection` (`menu_id`, `menu_item_id`, `surrogate_id`) VALUES
(1, 1, 1),
(1, 7, 2),
(1, 15, 3),
(2, 1, 4),
(2, 2, 5),
(2, 3, 6),
(2, 4, 7),
(2, 5, 8),
(2, 6, 9),
(2, 7, 10),
(2, 8, 11),
(2, 9, 12),
(2, 10, 13),
(2, 11, 14),
(2, 12, 15),
(2, 13, 16),
(2, 14, 17),
(2, 15, 18),
(2, 16, 19);

CREATE TABLE `ssm_menu_items` (
  `menu_item_id` int(11) NOT NULL,
  `menu_item_name` varchar(200) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `menu_item_category` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `ssm_menu_items` (`menu_item_id`, `menu_item_name`, `menu_item_category`) VALUES
(1, 'Bruschetta of Italian prosciutto, Fior di latte mozzarella, heirloom tomatoes, basil, mint and aged balsamic vinegar reduction (V)', 'Starter'),
(2, 'Vitello tonnato - poached veal fillet sliced thin, tuna mayonnaise, capers, flat leaf parsley, shaved parmigano (gf) (df)', 'Starter'),
(3, 'Pan seared scallops, chorizio, cauliflower, sherry soaked raisins, apple, pressed celery (gf)', 'Starter'),
(4, 'Pumpkin ravioli, burnt butter, blistered cherry tomatoes, crisp sage, herb & parmesan pangrattato', 'Starter'),
(5, 'Prawns char-grilled, avocado mousse, salmon pearls, micro salad, lime (nf, gf, df)', 'Starter'),
(6, 'Baked leek & pea tart in a butternut pumpkin pastry, heritage carrot & tarragon salad (vegan)', 'Starter'),
(7, 'Crisp skin chicken breast, stuffed with sage, shallots & garlic crumbs, potato gratin, sautee mushroom & spinach, jus gras', 'Main'),
(8, 'Herb crusted Marinated Rump of Lamb, served with petit pois a la francese, roasted baby potato, blistered cherry tomatoes, rich lamb jus', 'Main'),
(9, 'Scotch fillet steak (200g), roasted purple congo potatoes, dutch carrots, beans a la Grecque, red wine jus (gf) (df)', 'Main'),
(10, 'Eye fillet steak (200g), roasted purple congo potatoes, dutch carrots, beans a la Grecque, red wine jus (gf) (df)', 'Main'),
(11, 'Pressed pork belly with sautéed silverbeet, potato gratin, caramelised pear puree, pomegranate jus (gf)', 'Main'),
(12, 'Crisp skin duck breast, spiced red cabbage, broccolini, mash, sweet potato chips, pinot jus (gf)', 'Main'),
(13, 'Slow braised lamb shank, truffled wet polenta, sautéed broccolini and gremolata (gf)', 'Main'),
(14, 'Pan seared Barramundi fillet with rosemary & garlic chat potatoes and steamed seasonal vegetables', 'Main'),
(15, 'Tagine of vegetable lightly spiced, on preserved fruit and nut cous cous with mint and yoghurt (v, vegan option)', 'Main'),
(16, 'Crème brulee & raspberry compote with vanilla madeleines', 'Dessert'),
(17, 'Mi qui-melting centre Italian soufflé, strawberries, cream Chantilly, tuile (gf)', 'Dessert'),
(18, 'Apple and frangipani tart with cinnamon ice cream and crème anglaise', 'Dessert'),
(19, 'Tiramisu jarcake - marsala spiked mousse, coffee soaked savoiardi biscuit, chocolate shavings, fresh strawberry', 'Dessert'),
(20, 'Mini dessert selection for centre of tables to include macaroons, mini cheesecakes, French ecliars, and petit fours (3 per serve)', 'Dessert'),
(21, 'Orange scented Chia seed pudding, coconut shards, crushed pistachio & berry compote (vegan) (gf)', 'Dessert'),
(22, 'Chocolate and almond pavlova with raspberries and Bailey’s cream', 'Dessert');

CREATE TABLE `ssm_menu_order` (
  `surrogate_id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `menu_id` int(11) NOT NULL,
  `menu_item_id` int(11) NOT NULL,
  `menu_item_qty` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `ssm_menu_order` (`surrogate_id`, `job_id`, `menu_id`, `menu_item_id`, `menu_item_qty`) VALUES
(140, 25, 1, 15, 111),
(139, 25, 1, 7, 222),
(137, 27, 2, 15, 10),
(136, 27, 2, 7, 10),
(138, 25, 1, 1, 333),
(135, 27, 2, 1, 10);


ALTER TABLE `ssm_menu`
  ADD PRIMARY KEY (`menu_id`);

ALTER TABLE `ssm_menu_connection`
  ADD PRIMARY KEY (`surrogate_id`);

ALTER TABLE `ssm_menu_items`
  ADD PRIMARY KEY (`menu_item_id`);

ALTER TABLE `ssm_menu_order`
  ADD PRIMARY KEY (`surrogate_id`),
  ADD UNIQUE KEY `job_id` (`job_id`,`menu_id`,`menu_item_id`);


ALTER TABLE `ssm_menu`
  MODIFY `menu_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
ALTER TABLE `ssm_menu_connection`
  MODIFY `surrogate_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;
ALTER TABLE `ssm_menu_items`
  MODIFY `menu_item_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;
ALTER TABLE `ssm_menu_order`
  MODIFY `surrogate_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=141;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 

[Barand]

Thanks, that loaded OK. I'll look at your problem, which I interpret as ranking the menus by percentage of content of the ordered items.

I expect there could be a situation where

• items A,B,C,D,E are ordered
• menu 1 contains A,B,C.
• menu 2 contains B,C,D
• menu 3 contains C,D,E

ie No single menu contains all items ordered?

[Adamhumbug]

6 minutes ago, Barand said:

Thanks, that loaded OK. I'll look at your problem, which I interpret as ranking the menus by percentage of content of the ordered items.

I expect there could be a situation where

• items A,B,C,D,E are ordered
• menu 1 contains A,B,C.
• menu 2 contains B,C,D
• menu 3 contains C,D,E

ie No single menu contains all items ordered?

You are correct, that could happen.

i would need to echo something to the page in this case

 

[Barand]

Am I right in thinking that, as well as the % of the menu used by the order, you would want the % of the order covered by the menu?

[Adamhumbug]

Just now, Barand said:

Am I right in thinking that, as well as the % of the menu used by the order, you would want the % of the order covered by the menu?

That would be ideal

[Barand]

OK. I'm going to be in head-scratching mode for a while (enlarging my bald patch).

I'll get back when I have something.

[Adamhumbug]

3 minutes ago, Barand said:

OK. I'm going to be in head-scratching mode for a while (enlarging my bald patch).

I'll get back when I have something.

Legend of a man

[Barand]

As you test data contained only a menu that was a perfect match to the test order and a menu that contained everything I added a couple of extra menus and orders.

SELECT m.menu_id
     , m.menu_name
     , COUNT(*) as matched
     , ROUND(count(*)/menuitems*100, 1) as `%menu`
     , ROUND(count(*)/orderitems*100, 1) as `%order`
FROM 
        ssm_menu m
    INNER JOIN 
        ssm_menu_connection c
            ON c.menu_id = m.menu_id
    INNER JOIN
        ssm_menu_order o
            ON o.menu_item_id = c.menu_item_id
    INNER JOIN
        (
            SELECT job_id
                , COUNT(DISTINCT menu_item_id) as orderitems
            FROM ssm_menu_order
            GROUP BY job_id
        ) jtot  ON jtot.job_id = o.job_id
    INNER JOIN
        (
            SELECT menu_id
                , COUNT(DISTINCT menu_item_id) as menuitems
            FROM ssm_menu_connection
            GROUP BY menu_id
        ) mtot ON m.menu_id = mtot.menu_id
WHERE o.job_id = 27
GROUP BY m.menu_id
ORDER BY matched DESC, `%menu` DESC, `%order` DESC;
+---------+----------------------------------------------------------+---------+-------+--------+
| menu_id | menu_name                                                | matched | %menu | %order |
+---------+----------------------------------------------------------+---------+-------+--------+
|       1 | Menu One                                                 |       3 | 100.0 |  100.0 |
|       2 | Private Dinner Party/Wedding - 3 Course Fine Dining Menu |       3 |  18.8 |  100.0 |
|       3 | Menu Three                                               |       2 |  66.7 |   66.7 |
|       4 | Menu Four                                                |       2 |  50.0 |   66.7 |
|       5 | Menu Five                                                |       1 |  25.0 |   33.3 |
+---------+----------------------------------------------------------+---------+-------+--------+

 

[Adamhumbug]

15 hours ago, Barand said:

As you test data contained only a menu that was a perfect match to the test order and a menu that contained everything I added a couple of extra menus and orders.

SELECT m.menu_id
     , m.menu_name
     , COUNT(*) as matched
     , ROUND(count(*)/menuitems*100, 1) as `%menu`
     , ROUND(count(*)/orderitems*100, 1) as `%order`
FROM 
        ssm_menu m
    INNER JOIN 
        ssm_menu_connection c
            ON c.menu_id = m.menu_id
    INNER JOIN
        ssm_menu_order o
            ON o.menu_item_id = c.menu_item_id
    INNER JOIN
        (
            SELECT job_id
                , COUNT(DISTINCT menu_item_id) as orderitems
            FROM ssm_menu_order
            GROUP BY job_id
        ) jtot  ON jtot.job_id = o.job_id
    INNER JOIN
        (
            SELECT menu_id
                , COUNT(DISTINCT menu_item_id) as menuitems
            FROM ssm_menu_connection
            GROUP BY menu_id
        ) mtot ON m.menu_id = mtot.menu_id
WHERE o.job_id = 27
GROUP BY m.menu_id
ORDER BY matched DESC, `%menu` DESC, `%order` DESC;
+---------+----------------------------------------------------------+---------+-------+--------+
| menu_id | menu_name                                                | matched | %menu | %order |
+---------+----------------------------------------------------------+---------+-------+--------+
|       1 | Menu One                                                 |       3 | 100.0 |  100.0 |
|       2 | Private Dinner Party/Wedding - 3 Course Fine Dining Menu |       3 |  18.8 |  100.0 |
|       3 | Menu Three                                               |       2 |  66.7 |   66.7 |
|       4 | Menu Four                                                |       2 |  50.0 |   66.7 |
|       5 | Menu Five                                                |       1 |  25.0 |   33.3 |
+---------+----------------------------------------------------------+---------+-------+--------+

 

This is absolutely perfect, thank you so much.

Amazing help!!

[Adamhumbug]

1 hour ago, Adamhumbug said:

This is absolutely perfect, thank you so much.

Amazing help!!

So i have been having a look at this work of art and wondered if you would mind helping me take it a little further. 

From the selections that it makes, how would i select the row that has 100 %order and the highest %menu.

If none of them have 100 %order i will do {something}

if i can select the row that has 100 %order and the highest %menu i will do {something else}

if nothing can be found will do {another thing}

[Barand]

The first record should always be the best match so you could add "LIMIT 1' to the end of the query and just process the single result. Check the values in the record and take the appropriate action. However, I don't now how your process operates so I don't know how significant other records in the results are if the first does not adequately suit the order.

[Adamhumbug]

2 hours ago, Barand said:

The first record should always be the best match so you could add "LIMIT 1' to the end of the query and just process the single result. Check the values in the record and take the appropriate action. However, I don't now how your process operates so I don't know how significant other records in the results are if the first does not adequately suit the order.

This will actually work perfectly.  Thank again