Modify MySQL Query or Run in PHP

Question

I'm trying to run a query to find which inventory I should promote and which campaign I should run so I can move that inventory.

I have three tables:

campaigns lists different campaigns that I can run, each campaign has a unique id. Some campaigns promote only one item and some promote multiple items.
inventory has all the items I have in stock and the quantity of those items.
campaign_to_inventory matches the unique campaign id to the inventory item.

campaigns:

name         | id
-------------|---
blue-widgets | 1 
gluten-free  | 2
gadget       | 3

inventory:

item   | qty
-------|----
thing1 | 0
thing2 | 325
thing3 | 452
thing5 | 123
thing7 | 5

campaign_to_inventory:

id | item
---|-------
1  | thing1
1  | thing2
1  | thing5
2  | thing1
2  | thing3
3  | thing7

I'd like to run a query to find all the campaigns I could run where I have the needed inventory in stock. I'm currently running this query:

SELECT * FROM `campaigns` LEFT JOIN `campaign_to_inventory` ON `campaigns`.`id` = `campaign_to_inventory`.`id` LEFT JOIN `inventory` ON `campaign_to_inventory`.`item` = `inventory`.`item`

Which returns:

name         | id | item   | qty
-------------|----|--------|----
blue-widgets | 1  | thing1 | 0
blue-widgets | 1  | thing2 | 325
blue-widgets | 1  | thing5 | 123
gluten-free  | 2  | thing1 | 0
gluten-free  | 2  | thing3 | 452
gadget       | 3  | thing7 | 5

Should I use PHP to process this data to find only campaigns where all item quantities are greater than a minimum threshold, or is there a way to modify the query to limit the rows there? Is there a rule of thumb of when I can/should do it in one and not the other?

It likely works in SQL. Where are the minima defined?

sticky bit
– sticky bit

2018-05-21 02:58:45 +00:00
Commented May 21, 2018 at 2:58 — sticky bit
– sticky bit, Commented May 21, 2018 at 2:58

SebastBake · Accepted Answer · 2018-05-21 04:07:27Z

There's no need to process the data in PHP.

One way to do this would be to select the campaign_to_inventory.id column where the number of items is less than your threshold, like this:

SET @min_qty = 1;
SELECT `c_to_i`.`id` FROM `campaign_to_inventory` AS `c_to_i`
INNER JOIN `inventory` ON `inventory`.`item` = `c_to_i`.`item`
WHERE `inventory`.`qty` <= @min_qty;

... And then do a left outer join from campaign_to_inventory to that like this:

SET @min_qty = 1;
SELECT `id`, `name` FROM  `campaigns`
LEFT JOIN (
  /* Table of campaigns which contain items with not enough qty*/
  SELECT `c_to_i`.`id` FROM `campaign_to_inventory` AS `c_to_i`
  INNER JOIN `inventory` ON `inventory`.`item` = `c_to_i`.`item`
  WHERE `inventory`.`qty` <= @min_qty
) AS `campaigns_with_not_enough_items`
ON `campaigns`.`id`  = `campaigns_with_not_enough_items`.`id`
WHERE `campaigns_with_not_enough_items`.`id` is NULL;

The result should be a table of campaigns which have the needed inventory in stock.

As an aside, you should rename your campaign_to_inventory.id column to campaign since the name id implies that the column is the primary key for the table.

Collectives™ on Stack Overflow

Modify MySQL Query or Run in PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related