How to combine data in sql join query?

Question

SQL

I have 2 tables related to themselves many-to-many, for example, categories and products
3 table links between them, when I write a join query, then products with 2 or more categories are duplicated 2 or more times, respectively, how to solve this problem? Separately select products and categories, and then map them?

const _products = await knex('products')
  .select(
    'products.name as productName',
    'products.id as productId',
    'categories.id as categoryId',
    'products.url as productUrl',
    'categories.url as categoryUrl',
    'categories.name as categoryName'
  )
  .leftJoin(
    'categories_products',
    'categories_products.product_id',
    '=',
    'products.id'
  )
  .leftJoin(
    'categories',
    'categories.id',
    '=',
    'categories_products.category_id'
  )

Answer:

[
  {
            "productName": "natus",
            "productId": 1,
            "categoryId": 6,
            "productUrl": "http://june.com-natus",
            "categoryUrl": "caps",
            "categoryName": "Шапки"
        },
        {
            "productName": "natus",
            "productId": 1,
            "categoryId": 4,
            "productUrl": "http://june.com-natus",
            "categoryUrl": "jackets",
            "categoryName": "Куртки"
        },
]

It should turn out like this, but for this I separately select categories and products and then collect them in a cycle:

[
{
            "id": 1,
            "name": "natus",
            "url": "http://june.com-natus",
            "description": "Ipsam id dolores et suscipit illo. Magnam eveniet enim laborum omnis sint iste quo deserunt omnis. Exercitationem eum beatae atque animi qui iusto eos ipsam sint. Omnis consequatur suscipit beatae dolores illo rem soluta voluptatem sit. Aliquam et sit repellendus.\n \rModi distinctio suscipit tempora est eius vel fuga dolore. Voluptates alias vel qui iste nihil voluptatem quas error culpa. Facere illum sunt qui.\n \rNon in ut aut blanditiis nam. Ad qui aut aut voluptate nulla. Enim quia impedit ipsam perferendis expedita magnam nisi qui eligendi. Esse tempore est exercitationem alias omnis voluptatem non tenetur rem. Ut porro animi molestiae. Eveniet ducimus et voluptatem dolores hic eum.",
            "imageUrl": "http://lorempixel.com/640/480/abstract",
            "enabled": 1,
            "price": "404.97",
            "categories": [
                {
                    "id": 6,
                    "category_id": 6,
                    "product_id": 1,
                    "name": "Шапки",
                    "url": "caps",
                    "enabled": 1
                },
                {
                    "id": 4,
                    "category_id": 4,
                    "product_id": 1,
                    "name": "Куртки",
                    "url": "jackets",
                    "enabled": 1
                }
            ],
            "options": []
        },
]

And the same question when linking to the 1st
Request:

const _category = knex('categories')
      .select(
        'categories.name as categoriesName',
        'categories.url as categoriesUrl',
        'products.name as productName',
        'products.url as productUrl'
      )
      .leftJoin(
        'categories_products',
        'categories_products.category_id',
        '=',
        'categories.id'
      )
      .leftJoin(
        'products',
        'products.id',
        '=',
        'categories_products.product_id'
      )
      .where('categories.id', id)

Answer:

[
        {
            "categoriesName": "Куртки",
            "categoriesUrl": "jackets",
            "productName": "dolores",
            "productUrl": "https://dominic.com-dolores"
        },
        {
            "categoriesName": "Куртки",
            "categoriesUrl": "jackets",
            "productName": "dolores",
            "productUrl": "http://crawford.name-dolores"
        },
        {
            "categoriesName": "Куртки",
            "categoriesUrl": "jackets",
            "productName": "natus",
            "productUrl": "http://june.com-natus"
        }
]

Answer 1

Try to execute directly such request on basis, whether there will be duplications.
select pr.id as productId,
pr.name as productName,
pr.url as productUrl,
ct.id as categoryId,
ct.url as categoryUrl
ct.name as categoryName
from products as pr
inner join categories_products as ctp
on ctp.product_id = pr .id
inner join categories as ct
on ct.id = ctp.category_id
There may be more than one entry in the categories_products table by product_id.

Answer 2

Your request worked correctly. You described the situation, but did not ask the question, what exactly do you want to get as a result? For example, in the first case, 2 records are produced, one is superfluous, so add a restriction to where in the query for those fields where the extra line is. And then, why immediately join, and not where?