The title is indeed terrible but I have no idea what to put. I am working on a Bill of Materials app and I’m starting out with the database layout and the REST API to interact with the database.

I currently have four tables but the query I want to write involves three of them

CREATE TABLE `components` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `description` text DEFAULT NULL,
  `price` float unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `products` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `description` text DEFAULT NULL,
  `tax_code` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`),
  KEY `name_idx` (`tax_code`),
  CONSTRAINT `name` FOREIGN KEY (`tax_code`) REFERENCES `tax_codes` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `product_components` (
  `product_id` int(10) unsigned NOT NULL,
  `component_id` int(10) unsigned NOT NULL,
  `count` int(10) unsigned NOT NULL,
  PRIMARY KEY (`product_id`,`component_id`),
  KEY `fk_component_id_idx` (`component_id`),
  CONSTRAINT `fk_component_id` FOREIGN KEY (`component_id`) REFERENCES `components` (`id`),
  CONSTRAINT `fk_product_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Now what I want to do is list all the products and for each product calculate the cost of all the components that product needs. So if a product needs 4 doodads that cost $1 and 7 whatzits that cost $2 the cost of the product would be $18 (41 + 72). I know I’d need some JOINs but I have no idea what I’d need.

  • maynarkh@feddit.nlEnglish
    5·
    1 year ago
    SELECT p.name AS product_name, SUM(pc.count * c.price) AS cost
FROM products p
JOIN product_components pc ON p.id = pc.product_id
JOIN components c ON pc.component_id = c.id
GROUP BY p.id;

    Try this, I take no responsibility for it though.

  • CountVon@sh.itjust.worksEnglish
    4·
    1 year ago

    Probably something like this (syntax may be incorrect for MySQL, I work mainly with Oracle):

    select p.name, sum(price) from products p, product_components pc, products p where p.id = pc.product_id and pc.component_id = c.id group by p.name;

  • SlothStyle@lemmy.worldEnglish
    2·
    1 year ago

    That sounds kind of like putting business logic in your data layer. I generally try to avoid that but you could do something like

    SELECT ( :Doodads_amt * case when id=:doodad_id then price else 0 end ) + (:whatzits_amt * case when id=:whitzits_id then price else 0 end ) from components where component id in (:doodad_id, :whatzits_id);

    That’s probably not exactly it but you can work with the case statements to filter the data you need. I can’t remember off the top of my head what you want in your else statement

    The words with a colon at the front are bind variables.

    What might be easier is to just query for the price of each item then multiply that price by an amount in your API.

    Let me know if you have questions.