Complex query in QSqlQuery

[xgoan]

The SQL works in HeidiSQL and in mysql command.

I have been searching and I found that QSqlQuery only works with single queries, no way for a list of them

[jacek]

I have been searching and I found that QSqlQuery only works with single queries, no way for a list of them

Can't you issue two queries? The @m variable should be tied to the session. And you can always rewrite your queries into a single one.

Have you considered using sequences?

[xgoan]

And you can always rewrite your queries into a single one.

How could I do this in one query?

Have you considered using sequences?

I dont' know about sequences

[jacek]

How could I do this in one query?

I've overlooked the @m:= part.

Anyway the idea is to self cross join on the result set and count all rows that should be before the current row.

I.e. something like:

SELECT a.x, count(*) FROM result AS a, result AS b WHERE a.x <= b.x GROUP BY a.x ORDER BY a.x

SELECT a.x, count(*) FROM result AS a, result AS b WHERE a.x <= b.x GROUP BY a.x ORDER BY a.x

To copy to clipboard, switch view to plain text mode 

of course this isn't quite efficient and will work only if a.x is unique.

I dont' know about sequences

Sequence is an object that generates IDs. Usually it doesn't guarantee that IDs will be consecutive, esp. when you roll back transactions, but usually it doesn't matter. Although AFAIR in MySQL sequences are only in form of auto_increment fields.

If you are going to insert that data back to the database, you can just simply forget about the id field.

[wysota]

You can implement a sequence using triggers. MySQL supports them since 5.0

[xgoan]

Thanks
I will try this query but I think that will be better use 2 QSqlQuery, one for take the count of registers and the other to get the selected registers with C++ code because it seems that SQL vars doesn't work with QSqlQuery .

Anyway it's a pity that QSqlQuery doesn't support queries like these

[wysota]

Just a side note - I'd use a different query (it may be invalid but I mean the general idea):

SELECT
          MAX(cardid)+1 AS 'id',
          t.date,
          o.name,
          t.amount
      FROM `transactions` AS t
      INNER JOIN `operations` AS o ON t.operation=o.id
      WHERE
          operation>=1000 && operation<2000 GROUP BY cardid
      ORDER BY date;

SELECT
          MAX(cardid)+1 AS 'id',
          t.date,
          o.name,
          t.amount
      FROM `transactions` AS t
      INNER JOIN `operations` AS o ON t.operation=o.id
      WHERE
          operation>=1000 && operation<2000 GROUP BY cardid
      ORDER BY date;

To copy to clipboard, switch view to plain text mode 

MAX is safer than COUNT.

But honestly that's a perfect usecase for triggers - you can have a trigger that will calculate the id itself and modify the query on the fly.

[xgoan]

Just a side note - I'd use a different query (it may be invalid but I mean the general idea):

SELECT
          MAX(cardid)+1 AS 'id',
          t.date,
          o.name,
          t.amount
      FROM `transactions` AS t
      INNER JOIN `operations` AS o ON t.operation=o.id
      WHERE
          operation>=1000 && operation<2000 GROUP BY cardid
      ORDER BY date;

SELECT
          MAX(cardid)+1 AS 'id',
          t.date,
          o.name,
          t.amount
      FROM `transactions` AS t
      INNER JOIN `operations` AS o ON t.operation=o.id
      WHERE
          operation>=1000 && operation<2000 GROUP BY cardid
      ORDER BY date;

To copy to clipboard, switch view to plain text mode 

MAX is safer than COUNT.

But honestly that's a perfect usecase for triggers - you can have a trigger that will calculate the id itself and modify the query on the fly.

It's not a valid query:

mysql> DESCRIBE `transactions`;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| cardid    | char(16)     | NO   | PRI |         |       |
| date      | datetime     | NO   | PRI |         |       |
| operation | int(11)      | NO   |     |         |       |
| amount    | decimal(4,2) | NO   |     | 0.00    |       |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> DESCRIBE `transactions`;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| cardid    | char(16)     | NO   | PRI |         |       |
| date      | datetime     | NO   | PRI |         |       |
| operation | int(11)      | NO   |     |         |       |
| amount    | decimal(4,2) | NO   |     | 0.00    |       |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

To copy to clipboard, switch view to plain text mode 

mysql> [B]SELECT * FROM transactions LIMIT 1;[/B]
+------------------+---------------------+-----------+--------+
| cardid           | date                | operation | amount |
+------------------+---------------------+-----------+--------+
| %07032008125843_ | 2008-03-11 14:50:32 |      1000 |   5.00 |
+------------------+---------------------+-----------+--------+
1 row in set (0.00 sec)

mysql> [B]SELECT * FROM transactions LIMIT 1;[/B]
+------------------+---------------------+-----------+--------+
| cardid           | date                | operation | amount |
+------------------+---------------------+-----------+--------+
| %07032008125843_ | 2008-03-11 14:50:32 |      1000 |   5.00 |
+------------------+---------------------+-----------+--------+
1 row in set (0.00 sec)

To copy to clipboard, switch view to plain text mode 

As you can see I can't use MAX(cardid) because it's not correlative number. And the primary key it's done with cardid+date

[wysota]

So what do you need the variable for? I'd really implement a sequence using stored procedures if I were you. It's best to keep the logic in the database and not the client application. More security and less data transfer (thus better speed).

[mm78]

I have been searching and I found that QSqlQuery only works with single queries, no way for a list of them

Check out the new QSqlQuery::nextResult() function in Qt 4.4:
http://doc.trolltech.com/4.4beta/qsq...tml#nextResult

[xgoan]

Check out the new QSqlQuery::nextResult() function in Qt 4.4:
http://doc.trolltech.com/4.4beta/qsq...tml#nextResult

This could help only if it supports the SQL vars. Anyway I'm using Qt 4.3.4