CODEWITHSUNDEEP
mysqlqt
ineptant
ineptant

Reputation: 59

Mysql query doesn't work in qt

I have written a pretty complex mysql query and test it in mysql workbench, it works very well. But when I use this query in my qt program, it doesn't work.

For better resolution I have 3 tables (products, categories, products_categories) and I want to fetch 3 category with parentId = 5 and 2 product of each category. here is my function:

void DbQueries::getFullCategories(int parent_id, int offset, int limit, int plimit)
{
    ...

    QSqlQuery q;
    q.prepare("set @num := 0, @cid := '';"
              "SELECT e.ProductId, e.title, e.price, e.rate, e.rateAvg, e.imgUrl, d.cid, d.ctitle"
              "FROM products e,"
                  "(SELECT ProductId, @num := if(@cid = categoryId, @num + 1, 1) as qty, @cid := categoryId as cid, title as ctitle"
                  "FROM ("
                      "SELECT b.ProductId, b.categoryId, a.title"
                      "FROM products_categories b, (SELECT catId, title FROM categories WHERE parentId = :parent_id LIMIT :limit OFFSET :offset) as a"
                      "WHERE b.categoryId = a.catId"
                      "ORDER BY b.categoryId"
                      ") as c"
                  ") as d"
              "WHERE e.ProductId = d.ProductId AND d.qty <= :plimit"
              "ORDER BY d.cid;");
    q.bindValue(":parent_id", parent_id);
    q.bindValue(":limit", limit);
    q.bindValue(":offset", offset);
    q.bindValue(":plimit", plimit);
    if(!q.exec())
        qDebug() << q.lastError().text();

    const QSqlRecord &r = q.record();
    qDebug() << r.count();

    ...
}

With this query, after exec() I get no error but there is no record fetched in my QSqlRecord. I tried to put \ before := and I got Unknown escape sequence '\:'.

And when I reduced my query to this:

QSqlQuery q;
q.prepare("SELECT b.ProductId, b.categoryId, a.title"
          "FROM products_categories b, (SELECT catId, title FROM categories WHERE parentId = :parent_id LIMIT :limit OFFSET :offset) as a"
          "WHERE b.categoryId = a.catId"
          "ORDER BY b.categoryId");

I got mysql error like: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b, (SELECT catId, title FROM categories WHERE parentId = 5 LIMIT 3 OFFSE' at line 1 QMYSQL: Unable to execute query"

Upvotes: 0

Views: 124

Answers (1)

Turbo J
Turbo J

Reputation: 7691

This query has some important white space missing:

      "SELECT b.ProductId, b.categoryId, a.title"
      "FROM products_categories b, (SELECT catId, title FROM categories WHERE parentId = :parent_id LIMIT :limit OFFSET :offset) as a"
      "WHERE b.categoryId = a.catId"
      "ORDER BY b.categoryId"

Fix is really simple:

      "SELECT b.ProductId, b.categoryId, a.title"
      " FROM products_categories b, (SELECT catId, title FROM categories WHERE parentId = :parent_id LIMIT :limit OFFSET :offset) as a"
      " WHERE b.categoryId = a.catId"
      " ORDER BY b.categoryId"
      -^-

Upvotes: 1

Related Questions