Beginner Node.js:  Many-to-Many Relationships
Tags: node, database, knex, javascript
Published: 2019-12-16 04:58:51

For my third (and first solo) project at Flatiron School, I wanted to explore the language of flowers. Being a self-described old lady and lover of Victorian romances, I knew that flowers have meanings, and it would be fun to figure out a way to put together messages with those flowers. 

Flatiron School mostly focuses on Ruby on Rails for backend development, and I readily admit this project would have been easier had I stuck with that. However, to get the info on flowers I wanted without having to hardcode everything, I wanted to try web-scraping, and the first tutorial I found used Node.js, Axios, and Cheerio. So I set off on the adventure of learning Node.

## Database Set-up

First things first, in order to set up a database, you need to figure out the relationships you'll need to build. Since I was working in thin vertical slices, I knew that I would need a table for "flowers", to host the information on flowers and their meanings I was scraping. I built all the Knex queries and the full CRUD routes for the flowers table.

Once that was complete, and the front end connected, the next step was to create a "bouquets" table. This is where collections of flowers, and the "messages" that those flower collections convey, would be stored. Once those were done, the only thing left was to figure out how flowers and bouquets were connected. What it became is a many-to-many relationship, with a "bouquets-flowers" join connecting flowers to their bouquets.  

![DB Visualization](https://thepracticaldev.s3.amazonaws.com/i/8cut7fbykt9s5lqryvbk.png)

Once the join table was created, the next coding problem was figuring out how to get the flowers that belonged to a certain bouquet to show up in the bouquets get. 

## Many-to-many Query

Here's the code for the query I wrote that connects to the bouquets table, maps through the bouquets, and attaches the flowers that are in that bouquet using the join table.

```javascript
const connection = require('./knexfile')[process.env.NODE_ENV || 'development']
const database = require('knex')(connection)

getFlowersInBouquets(){
        return database('bouquets')
            .then(bouquets => {
                const promises = bouquets.map(bouquet => {
                    return database('bouquets-flowers')
                        .join('flowers', 'flowers.id', 'bouquets-flowers.flower_id')
                        .where('bouquet_id', bouquet.id)
                        .then(flowers => {
                            bouquet.flowers = flowers
                            return bouquet
                        })
                })
                return Promise.all(promises)
            })
    }
```

In each iteration of the map, a variable called "promises" is created, in which the flower_id that's stored in the join with the bouquet_id of the bouquet being mapped over is attached to the bouquet itself. When this query is run using a GET method, what's returned is all the bouquets, each with the information in its columns, such as id, and name, but also with the flower_ids that belong to the bouquet. This makes it much easier to fetch all relevant data at once in order to have the bouquet show up on the front end. 

## Sources and Project Links

Web-scraping tutorial using Node.js, Axios, and Cheerio: [How to Perform Web Scraping](https://blog.bitsrc.io/https-blog-bitsrc-io-how-to-perform-web-scraping-using-node-js-5a96203cb7cb)
Another web-scraping tutorial I used together with the first one: [Web Scraping with Node.js](https://levelup.gitconnected.com/web-scraping-with-node-js-c93dcf76fe2b)

Github repo for my floral messaging project: [Talk Floral To Me](https://github.com/mezzolite/talk-floral-to-me-project)