How to Query Table Too Much for Many Knex

Question

I’m trying to do a query in a relation many to many, but it’s not going well as I expected...

the method I did was like this:

const getAll = async (req, resp) => {
            knex({ pc: 'product_category' })
                .join('products as p', 'pc.product_id', '=', 'p.id')
                .join('categories as c', 'pc.category_id', '=', 'c.id')
                .select('p.*', 'c.id as categoryId', 'c.name as categories')                
                .then(products => resp.json(products))
                .catch(e => resp.status(500).json(e));      
        
    }

but it’s not going the way I expected, with duplicate values....

what I expected was something like this:

[
  {
    "id": 7,
    "name": "HD Sansung 500GB",
    "quantity": 20,
    "price": 195,
    "categoryId": 1,
    "categories": [
       
       { 
         id: 1,
        name: "Informática"
       },
       { 
         id: 2,
        name: "tecnologia"
       }
]
  },
  {
    "id": 10,
    "name": "Caixa de Som Vox Clube S4000",
    "quantity": 45,
    "price": 65.5,
    "categoryId": 1,
    "categories": [
       
       { 
         id: 1,
        name: "Informática"
       },
       { 
         id: 2,
        name: "tecnologia"
       }
]

thank you in advance

Answer 1

I achieved the desired result with the following method:

const getAll = (req, resp) => {
        knex({ pc: 'product_category' })
            .join('products as p', 'pc.product_id', '=', 'p.id')
            .join('categories as c', 'pc.category_id', '=', 'c.id')
            .select('p.*', 'c.id as categoryId', 'c.name as category')                
            .then(products => {
                products = products.map((p, i, array) => productWithCategories(p, array));
                products = withoutDuplicate(products);

                return resp.json(products);  
            })
            .catch(e => resp.status(500).json(e));

        const productWithCategories = (p, products) => {
            const product = { ...p };
            product.categories = [];
            products.map((v) => {
                if(v.id == product.id) {
                    product.categories.push({ id: v.categoryId, name: v.category });
                }
            });

            delete product.categoryId;
            delete product.category;
            return product;
        }  
        
        const withoutDuplicate = withDuplicate => {
            const ids = [];
            const $arr = [];

            withDuplicate.forEach(p => {
                if(!ids.includes(p.id)) {
                    $arr.push(p);
                    ids.push(p.id);
                }
            })
            return $arr;
        }
    }

I just don’t know if there’s any way to refactor it...