Complex JOIN SQL query in Laravel 5

Question

I have three tables that I need to join: products, orders_items, orders

The products table contains, obviously, the products. When a user adds a product to their cart, it creates an order and a line item in the order_items table. When the user pays, it puts the total paid into the orders table. Therefore, purchased products removed from inventory only include products that are attached to an order_item attached to an order that has been marked paid.

I need to return all the products, but also somehow calculate how many are left in inventory. The products table contains a field called "initial_quantity" which is how many of the product we started with. If that field is NULL, there's no limit on the product.

Here is my current query, which works just fine for returning all the products of a certain type:

$swims = Product::where('product_type_id', 1)
            ->whereDate('visible_date', '<=', Carbon::now())
            ->where('active', true)
            ->where(function ($query) {
                $query->where('end_date', '>=', Carbon::now())
                      ->orWhere('end_date', null);  
            })          
            ->get();

Here is the SQL for that query:

select * from `products` where `product_type_id` = 1 and date(`visible_date`) <= '2019-08-08 17:10:12' and `active` = 1 and (`end_date` >= '2019-08-08 17:10:12' or `end_date` is null)

The above query returns three results:

+----+-------------+------------+------------+--------------+------------------+-----------------+--------+
| id | name        | base_price | end_date   | visible_date | initial_quantity | product_type_id | active |
+----+-------------+------------+------------+--------------+------------------+-----------------+--------+
|  1 | membership  |      30.97 | NULL       | 2019-08-04   |             NULL |               1 |      1 |
| 12 | repellendus |     779.24 | 2027-03-16 | 1990-12-19   |             NULL |               1 |      1 |
| 16 | ducimus     |     708.33 | NULL       | 1999-03-24   |             NULL |               1 |      1 |
+----+-------------+------------+------------+--------------+------------------+-----------------+--------+

I can run another query to see how much of each product has been sold:

select product_id, SUM(quantity) sold from `order_items` inner join `orders` on `order_items`.`order_id` = `orders`.`id` and `orders`.`paid` is not null group by `product_id`

This query returns:

+------------+------+
| product_id | sold |
+------------+------+
|          1 |    5 |
|          7 |    3 |
|         11 |    1 |
|         12 |    1 |
+------------+------+

What I'm looking for is a way to combine those two (or similar) queries so I get all the products I'm looking for, plus the quantity of those products that have been sold, to give me a result similar to this:

+----+-------------+------------+------------+--------------+------------------+-----------------+--------+------+
| id | name        | base_price | end_date   | visible_date | initial_quantity | product_type_id | active | sold |
+----+-------------+------------+------------+--------------+------------------+-----------------+--------+------+
|  1 | membership  |      30.97 | NULL       | 2019-08-04   |             NULL |               1 |      1 |    5 |
| 12 | repellendus |     779.24 | 2027-03-16 | 1990-12-19   |             NULL |               1 |      1 |    1 |
| 16 | ducimus     |     708.33 | NULL       | 1999-03-24   |             NULL |               1 |      1 |    0 |
+----+-------------+------------+------------+--------------+------------------+-----------------+--------+------+

I would like a query that returns all products I'm searching for and also how many are remaining. I've tried join, leftJoin, rightJoin, subquery joins and unions. Laravel doesn't seem to have outer joins or I would have tried those too.

I have tried this query and many other iterations and I always get a syntax error:

select * from (`products` where `product_type_id` = 1 and date(`visible_date`) <= '2019-08-08 17:10:12' and `active` = 1 and (`end_date` >= '2019-08-08 17:10:12' or `end_date` is null)) x INNER JOIN (select product_id, SUM(quantity) sold from `order_items` inner join `orders` on `order_items`.`order_id` = `orders`.`id` and `orders`.`paid` is not null group by `product_id`) y ON x.id = y.product_id

Can anybody come up with the query I'm looking for? If not in Laravel then in raw SQL would be fine and I can use it as-is or try to Laravelize it.

Thank you in advance.

Here is my MRE:

CREATE TABLE `products` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `base_price` double(5,2) NOT NULL,
  `end_date` date DEFAULT NULL,
  `visible_date` date DEFAULT NULL,
  `initial_quantity` smallint(5) UNSIGNED DEFAULT NULL,
  `product_type_id` int(10) UNSIGNED NOT NULL,
  `active` tinyint(3) UNSIGNED NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `products` (`id`, `name`, `base_price`, `end_date`, `visible_date`, `initial_quantity`, `product_type_id`, `active`) VALUES
(1, 'membership', 30.97, NULL, '2019-08-04', NULL, 1, 1),
(2, 'delectus', 347.44, NULL, '1975-06-08', 45, 1, 0),
(3, 'aut', 283.36, '1981-05-30', '1973-08-18', NULL, 3, 0),
(4, 'adipisci', 984.00, '1986-01-04', '1989-03-29', NULL, 3, 0),
(5, 'voluptas', 310.55, '2012-05-04', '1992-11-16', 45, 2, 1),
(6, 'quia', 657.81, '1976-11-23', '1978-10-23', 57, 2, 0),
(7, 'delectus', 601.91, NULL, '1987-03-09', 53, 2, 1),
(8, 'consequatur', 723.25, '1994-09-24', '1985-01-23', 33, 1, 0),
(9, 'perferendis', 427.33, NULL, '1995-12-08', 51, 3, 1),
(10, 'id', 674.39, '1974-12-13', '2007-05-12', 41, 2, 0),
(11, 'maxime', 133.49, NULL, '1986-07-15', NULL, 2, 1),
(12, 'repellendus', 779.24, '2027-03-16', '1990-12-19', NULL, 1, 1),
(13, 'sit', 956.76, NULL, '1993-07-02', 39, 2, 0),
(14, 'molestiae', 425.16, NULL, '1981-03-25', NULL, 3, 1),
(15, 'omnis', 418.78, '1976-11-03', '2007-10-07', NULL, 3, 1),
(16, 'ducimus', 708.33, NULL, '1999-03-24', NULL, 1, 1),
(17, 'numquam', 328.26, '2011-09-19', '2001-10-07', NULL, 3, 1),
(18, 'est', 962.26, NULL, '1970-12-24', NULL, 1, 0),
(19, 'quis', 520.18, NULL, '1978-03-25', NULL, 3, 0),
(20, 'facere', 891.89, NULL, '1979-10-31', 36, 3, 0),
(21, 'voluptatem', 518.47, '1982-06-26', '1975-03-14', NULL, 1, 1);
COMMIT;

    CREATE TABLE `order_items` (
      `order_id` bigint(20) UNSIGNED NOT NULL,
      `product_id` bigint(20) UNSIGNED NOT NULL,
      `quantity` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
      `price` double(5,2) NOT NULL,
      `tax` double(5,2) NOT NULL,
      `buy_now` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
      `created_at` timestamp NULL DEFAULT NULL,
      `updated_at` timestamp NULL DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    INSERT INTO `order_items` (`order_id`, `product_id`, `quantity`, `price`, `tax`, `buy_now`, `created_at`, `updated_at`) VALUES
    (1, 1, 1, 30.97, 4.03, 1, '2019-08-04 11:35:26', '2019-08-04 11:35:26'),
    (1, 12, 1, 779.24, 654.56, 1, '2019-08-04 11:35:26', '2019-08-04 11:35:26'),
    (2, 1, 1, 30.97, 4.03, 1, '2019-08-04 11:56:16', '2019-08-04 11:56:16'),
    (3, 1, 1, 30.97, 4.03, 1, '2019-08-07 17:25:43', '2019-08-07 17:25:43'),
    (3, 7, 1, 601.91, 210.67, 1, '2019-08-07 17:25:43', '2019-08-07 17:25:43'),
    (4, 1, 1, 30.97, 4.03, 1, '2019-08-08 15:44:45', '2019-08-08 15:44:45'),
    (4, 7, 1, 601.91, 210.67, 1, '2019-08-08 15:44:45', '2019-08-08 15:44:45'),
    (5, 1, 1, 30.97, 4.03, 1, '2019-08-08 15:46:07', '2019-08-08 15:46:07'),
    (5, 11, 1, 133.49, 88.10, 1, '2019-08-08 15:46:07', '2019-08-08 15:46:07'),
    (6, 1, 1, 30.97, 4.03, 1, '2019-08-08 15:46:55', '2019-08-08 15:46:55'),
    (6, 16, 1, 708.33, 687.08, 1, '2019-08-08 15:46:55', '2019-08-08 15:46:55'),
    (7, 1, 1, 30.97, 4.03, 1, '2019-08-08 15:47:37', '2019-08-08 15:47:37'),
    (7, 7, 1, 601.91, 210.67, 1, '2019-08-08 15:47:37', '2019-08-08 15:47:37'),
    (8, 1, 1, 30.97, 4.03, 1, '2019-08-08 15:48:13', '2019-08-08 15:48:13'),
    (8, 11, 1, 133.49, 88.10, 1, '2019-08-08 15:48:13', '2019-08-08 15:48:13');

    CREATE TABLE `orders` (
      `id` bigint(20) UNSIGNED NOT NULL,
      `user_id` bigint(20) UNSIGNED NOT NULL,
      `subtotal` double(7,2) DEFAULT NULL,
      `tax` double(7,2) DEFAULT NULL,
      `paid` double(7,2) DEFAULT NULL,
      `transaction` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `created_at` timestamp NULL DEFAULT NULL,
      `updated_at` timestamp NULL DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    INSERT INTO `orders` (`id`, `user_id`, `subtotal`, `tax`, `paid`, `transaction`, `created_at`, `updated_at`) VALUES
    (1, 1, 810.21, 658.59, 12343.00, NULL, '2019-08-04 11:35:26', '2019-08-04 11:35:26'),
    (2, 1, 30.97, 4.03, NULL, NULL, '2019-08-04 11:56:16', '2019-08-04 11:56:16'),
    (3, 1, 632.88, 214.70, 847.58, NULL, '2019-08-05 17:25:43', '2019-08-07 17:25:43'),
    (4, 5, 632.88, 214.70, 847.58, NULL, '2019-08-08 15:44:45', '2019-08-08 15:44:45'),
    (5, 5, 164.46, 92.13, 256.59, NULL, '2019-08-08 15:46:07', '2019-08-08 15:46:07'),
    (6, 5, 739.30, 691.11, NULL, NULL, '2019-08-08 15:46:55', '2019-08-08 15:46:55'),
    (7, 10, 632.88, 214.70, 847.58, NULL, '2019-08-08 15:47:37', '2019-08-08 15:47:37'),
    (8, 10, 164.46, 92.13, NULL, NULL, '2019-08-08 15:48:13', '2019-08-08 15:48:13');

@Strawberry, done. I tried to make a fiddle too, but it doesn't seem to take more than one table at a time. — Jacey
– Jacey, Commented Aug 8, 2019 at 16:03
@Strawberry, while I appreciate the guidance you're offering, this was a custom solution in a tight timeframe. I've already moved profile data out of the users table and into its own, and I have at least one pivot, so I do understand the concept. The site is live and functioning and I'll continue to work on refactoring my code and normalizing the database. At this point, I really just need help with the query. — Jacey
– Jacey, Commented Aug 8, 2019 at 16:22
@Strawberry, I want all the products that fit the parameters: select * from products where product_type_id = 1 and date(visible_date) <= 2019-08-08 and active = 1 and (end_date >= 2019-08-08 or end_date is null) And how many of those products have been sold, or more precisely, how many are remaning. — Jacey
– Jacey, Commented Aug 8, 2019 at 18:00

Strawberry · Accepted Answer · 2019-08-09 12:50:41Z

SELECT a.*
     , COALESCE(b.sold,0) sold
  FROM 
     ( SELECT * 
         FROM products p
        WHERE p.product_type_id = 1 
          AND DATE(p.visible_date) <= '2019-08-08 17:10:12' 
          AND p.active = 1 
          AND (p.end_date >= '2019-08-08 17:10:12' or p.end_date IS NULL)
     ) a
  LEFT
  JOIN   
     ( SELECT product_id
            , SUM(quantity) sold 
         FROM order_items oi
         JOIN orders o
           ON o.id = oi.order_id
          AND o.paid is not null 
        GROUP 
           BY product_id
     ) b
    ON b.product_id = a.id;

...or, better...

SELECT p.*
     , COALESCE(y.sold,0) sold
  FROM products p
  LEFT
  JOIN 
     ( SELECT product_id
            , SUM(quantity) sold 
         FROM order_items oi
         JOIN orders o
           ON o.id = oi.order_id 
          AND o.paid is not null 
        GROUP 
           BY product_id
     ) y 
    ON y.product_id = p.id
 WHERE p.product_type_id = 1 
   AND DATE(p.visible_date) <= '2019-08-08 17:10:12' 
   AND p.active = 1 
   AND (p.end_date >= '2019-08-08 17:10:12' OR p.end_date IS NULL)

Collectives™ on Stack Overflow

Complex JOIN SQL query in Laravel 5

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