CODEWITHSUNDEEP
node.jspostgresql
olv53
olv53

Reputation: 73

How to insert new rows to a junction table Postgres

I have a many to many relationship set up with with services and service_categories. Each has a table, and there is a third table to handle to relationship (junction table) called service_service_categories. I have created them like this:

CREATE TABLE services(
  service_id SERIAL,
  name VARCHAR(255),
  summary VARCHAR(255),
  profileImage VARCHAR(255),
  userAgeGroup VARCHAR(255),
  userType TEXT,
  additionalNeeds TEXT[],
  experience TEXT,
  location POINT,
  price NUMERIC,
  PRIMARY KEY (id),
  UNIQUE (name)
);

CREATE TABLE service_categories(
  service_category_id SERIAL,
  name TEXT,
  description VARCHAR(255),
  PRIMARY KEY (id),
  UNIQUE (name)
);

CREATE TABLE service_service_categories(
  service_id INT NOT NULL,
  service_category_id INT NOT NULL,
  PRIMARY KEY (service_id, service_category_id),
  FOREIGN KEY (service_id) REFERENCES services(service_id) ON UPDATE CASCADE,
  FOREIGN KEY (service_category_id) REFERENCES service_categories(service_category_id) ON UPDATE CASCADE
);

Now, in my application I would like to add a service_category to a service from a select list for example, at the same time as I create or update a service. In my node js I have this post route set up:

// Create a service
router.post('/', async( req, res) => {
  try {
    console.log(req.body);
    const { name, summary } = req.body;
    const newService = await pool.query(
      'INSERT INTO services(name,summary) VALUES($1,$2) RETURNING *',
      [name, summary]
    );
    res.json(newService);
  } catch (err) {
    console.log(err.message);
  }
})

How should I change this code to also add a row to the service_service_categories table, when the new service ahas not been created yet, so has no serial number created?

If any one could talk me through the approach for this I would be grateful. Thanks.

Upvotes: 0

Views: 681

Answers (2)

olv53
olv53

Reputation: 73

I managed to get it working to a point with multiple inserts and changing the schema a bit on services table. In the service table I added a column: category_id INT:

ALTER TABLE services
ADD COLUMN category_id INT;

Then in my node query I did this and it worked:

const newService = await pool.query(
      `
      with ins1 AS
      (
       INSERT INTO services (name,summary,category_id)
           VALUES ($1,$2,$3) RETURNING service_id, category_id
      ),
      ins2 AS
      (
       INSERT INTO service_service_categories (service_id,service_category_id) SELECT service_id, category_id FROM ins1
      )
      select * from ins1
      `,
      [name, summary, category_id]
    );

Ideally I want to have multiple categories so the category_id column on service table, would become category_ids INT[]. and it would be an array of ids.

How would I put the second insert into a foreach (interger in the array), so it creates a new service_service_categories row for each id in the array?

Upvotes: 0

thorn
thorn

Reputation: 34

You can do this in the database by adding a trigger to the services table to insert a row into the service_service_categories that fires on row insert. The "NEW" keyword in the trigger function represents the row that was just inserted, so you can access the serial ID value.

https://www.postgresqltutorial.com/postgresql-triggers/

Something like this:

CREATE TRIGGER insert_new_service_trigger 
   AFTER INSERT
   ON services
   FOR EACH ROW
   EXECUTE PROCEDURE insert_new_service();

Then your trigger function looks something like this (noting that the trigger function needs to be created before the trigger itself):

CREATE OR REPLACE FUNCTION insert_new_service()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
    -- check to see if service_id has been created
    IF NEW.service_id NOT IN (SELECT service_id FROM service_service_categories) THEN
         INSERT INTO service_service_categories(service_id)
         VALUES(NEW.service_id);
    END IF;

    RETURN NEW;
END;
$$;

However in your example data structure, it doesn't seem like there's a good way to link the service_categories.service_category_id serial value to this new row - you may need to change it a bit to accommodate

Upvotes: 0

Related Questions