Error in your SQL syntax 'WHERE c.name LIKE '%%''-CodePudding

I'm looking to produce the below table with search and pagination by joining 4 tables - categories, subcategories, childCategories and products.

The backend code to acheive the above is:

const [rows, fields] = await mysqlConnPool.promise().execute(
      `SELECT c.id, c.name, COUNT(DISTINCT(s.id)) AS subCategories, 
      COUNT(DISTINCT(ch.id)) AS childCategories, COUNT(p.id) AS products 
      FROM categories c
      LEFT JOIN subCategories s ON c.id = s.categoryId 
      LEFT JOIN childCategories ch ON s.id = ch.subCategoryId 
      LEFT JOIN products p ON ch.id = p.childCategoryId GROUP BY c.id
      ORDER BY c.id ASC LIMIT ${size} OFFSET ${page * size}`
    );

However, when I try to implement the search functionality, it throws a Mysql syntax error. Please help to identify the error and point the correct syntax:

const [rows, fields] = await mysqlConnPool.promise().execute(
      `SELECT c.id, c.name, COUNT(DISTINCT(s.id)) AS subCategories, 
      COUNT(DISTINCT(ch.id)) AS childCategories, COUNT(p.id) AS products 
      FROM categories c
      LEFT JOIN subCategories s ON c.id = s.categoryId 
      LEFT JOIN childCategories ch ON s.id = ch.subCategoryId 
      LEFT JOIN products p ON ch.id = p.childCategoryId GROUP BY c.id
      ORDER BY c.id ASC LIMIT ${size} OFFSET ${page * size}
      WHERE c.name LIKE '%${search}%'`
    );

But it throws the following error:

CodePudding user response：

Figured out the right syntax, posting if helpful to someone else:

const [rows, fields] = await mysqlConnPool.promise().execute(
      `SELECT c.id, c.name, COUNT(DISTINCT(s.id)) AS subCategories, 
      COUNT(DISTINCT(ch.id)) AS childCategories, COUNT(p.id) AS products 
      FROM categories c 
      LEFT JOIN subCategories s ON c.id = s.categoryId 
      LEFT JOIN childCategories ch ON s.id = ch.subCategoryId 
      LEFT JOIN products p ON ch.id = p.childCategoryId
      WHERE c.name LIKE '%${search}%' GROUP BY c.id
      ORDER BY c.id ASC LIMIT ${size} OFFSET ${page * size}`
    );

WHERE clause needs to come before GROUP BY

CodePudding user response：

Please use the below updated query.

const [rows, fields] = await mysqlConnPool.promise().execute(
      `SELECT c.id, c.name, COUNT(DISTINCT(s.id)) AS subCategories, 
      COUNT(DISTINCT(ch.id)) AS childCategories, COUNT(p.id) AS products 
      FROM categories c 
      LEFT JOIN subCategories s ON c.id = s.categoryId 
      LEFT JOIN childCategories ch ON s.id = ch.subCategoryId 
      LEFT JOIN products p ON ch.id = p.childCategoryId
      WHERE c.name LIKE '%${search}%' GROUP BY c.id
      ORDER BY c.id ASC LIMIT ${size} OFFSET ${page * size}`