Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you have been placed in read-only mode . Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

i'm using QtSql with SQLite database. i have a query which joins number of tables/views, and returns more than 200.000 rows.
suppose a value in one of those tables changes, and i need some values from some row, e.g. n = 7 th row.
what i currently do is call exec() on the query again. but that is slow.
what can i do better?

@user4592357
Ohhh. Then you'd have to see whether SQLite (I don't use it) offers a "row number" function for a column (I know MySQL & MSSQL do). [OK, here you go for SQLite: https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-row_number/ ] And you put that into your WHERE condition.

But this sounds awful. It'll doubtless be just as slow as the whole thing, and relies on the ordering or the result set rows.

You're not really supposed to be asking a (relational) database for "give me the 7th row of output from some join with 200,00 rows", so why are you? E.g. you're supposed to have a primary key value you're interested in.

@user4592357
No, that's going to be way too heavy on a join returning that many rows. I meant PKs in the original tables.

Why are you asking for a particular row number to be re-read anyway?

Also, what are you doing with the 200,000 rows returned by your query? I hope you need them all, because that's a large number. For example, you're not offering the user to see them all or page through them are you?

@JonB
i have a table view to display it.

i execute the query at the beginning (before showing) once, then model's data() retrieves rows as needed.
i know i should be using canFetchMore() here but unfortunately i'm using some library which provides some more capabilities that this app needs. i don't know what are the costs to modifying the library.

the table has context menu for rows, and the user can change data from menu item. and i need some column id values from the row which invoked the context menu in order to construct the update query...

my question would rather be correct to formulate like this: how do i update the nth row of the select statement? i know it doesn't make sense but that's what i need... :(

i'm not sure, but will QSqlTableModel be useful?

@user4592357 said in needing updated values from executed query row :
You are aware that whatever your canFetchMore() issue is, it is not good.

my question would rather be correct to formulate like this: how do i update the nth row of the select statement? i know it doesn't make sense but that's what i need... :(

It does not make sense. For a couple of reasons. Firstly, you said the rows are the result of some joining and using views etc. across tables. So you aren't actually intending to update that row, are you? It isn't a row in table. Secondly, I don't see how you know how "row 7" relates to the output from your query. How do you even know if you ran that query again it would come out again as the same row number? Especially as you say your data is changing. You say you can deal with this by issuing the exec() again but it's slow, I don't understand how that addresses whatever you do when you "update the 7th row".

What you are supposed to do is be able to identify a row not by its number but by the values in (a combination of) columns. If your 7th row, if col3 + col4 holds a UK from tableA and col8 folds a UK from tableB, etc., and that relates to what you want to update, you can use some kind of "UPDATE tableA/tableB WHERE tableA.colx = " + joinview[6].col3 + ... .

You need to think out, or at least clearly explain, what you mean by "update the 7th row of a result set produced using joins and views".

If you really want a row number column in your query output use the ROW_NUMBER() function. I do not see how this would help you, however.

OK, I see you said:

the table has context menu for rows, and the user can change data from menu item. and i need some column id values from the row which invoked the context menu in order to construct the update query...

In some shape or form, you need to make it so your context menu gets a column value you actually want to use in your subsequent update statement. You should not be re-executing the original SELECT to discover what was in the 7th row! You will retrieve the values via the QSqlQuery::data() method. I have a feeling this might be the nub of the issue?

@JonB
yes, i don't want to update the specific row. since i already have an executed query with results (rows), when i update one particular row, the record in query for that row becomes invalid, since it contains old values.

the number of returned rows for query never changes, so i always know that the nth row corresponds to that record. i.e. new data isn't added, only attributes (metadata) of the record are changed. so in my table i get fixed number of items.

What you are supposed to do is be able to identify a row not by its number but by the values in (a combination of) columns. If your 7th row, if col3 + col4 holds a UK from tableA and col8 folds a UK from tableB, etc., and that relates to what you want to update, you can use some kind of "UPDATE tableA/tableB WHERE tableA.colx = " + joinview[6].col3 + ....
that's how i update. but since my table view takes values from already executed query to display, it displays the old data, and not the updated one.
	
@user4592357

I'm sorry, but I don't understand your situation.  I don't know what you're trying to do or what your issue is.  You may want to explain more clearly.  What I do know, however, is that if
the table has context menu for rows, and the user can change data from menu item. and i need some column id values from the row which invoked the context menu in order to construct the update query...
implies you have to re-issue a query in order to get some values out of the 7th row from a query already executed then something is wrong.
	
@JonB

ok let me try to explain on an example.

say this is the n=7th row from result (table view displays only first 3 columns):
Name    Surname     Country     FirstID     SecondID        ThirdID
say current country was "Ukraine", and when user modifies the country to, say "Poland", i take the values of FirstID, SecondID, ThirdID from executed query's 7th row's record, and do appropriate update in the database tables for the user.
this is some part of data():
QVariant data(index) const
    m_pQuery->seek(index.row());
    return m_pQuery.value("Country");
now, since the m_pQuery query is already executed, it returns the old country for 7th row (of m_pQuery).
what if i keep a map<int, QSqlRecord *>, and when nth row is updated, i execute the query again, adding below contraints, and then saving the new record pointer in the map? this should work, right?
query_text += " AND FirstID = :first_id AND SecondID = :second_id AND ThirdID = :third_id";
	
@user4592357

There is still much that I do not understand from your description, but the short answer may be "yes".  You are now identifying the row in question by (a combination of) unique key values instead of row number, which is the approach I suggested.
The following are some of the bits I still don't understand, but you may not need to answer if you already have your solution!
You issue some SQL join-view query to produce a result set with 200k rows.
You present (some of) those rows in a QTableView.
You are presumably doing that via QSqlQuery.  If your QTableView is linked to a QSqlQuery I don't see how you can "edit" any cell in the row, because QSqlQuery result set is read-only and won't allow you to edit?  I'll ignore that for now.
From that alteration what you actually update is: somehow you figure what table's row to update from what the user updates (e.g. presumably one involved in the original view-join?).
You send that update to the database.
Now you want to re-display just that row?  No other row will have been affected by the update??  Will changing, say, the country cause the database query to return different values for other columns in that particular row, so that you need to re-fetch it from the database?  Or, will only the country have changed, so you don't really need to fetch anything anew from the database?
Also, I take it that your proposed AND ... constraints uniquely identify just one row in the result set?
You say that you know the order records are returned is "fixed", so that row number 7 is always row number 7.  However, if it's fixed you must be using ORDER BY in each & every view/join you use to ensure this, and the result set must be uniquely/unambiguously ordered.  In that case, wouldn't changing the country, say, alter the order of the result set so that what was row number 7 will now have potentially moved?
	
@JonB

sorry if it still wasn't clear.

when user selects table row and updates country, a couple of tables in database are updated, say  i change CountryID in Users table to point to the new country.

now, my problem is #3 from your list. since i'm reading row data from already executed query, the query doesn't have the updated data, so i'm still displaying the old data. i need to somehow "update" the query to display the latest data.
#6: no other row will be affected. i'm only changing the country for that specific user. no countries/users are added in database tables, so i don't get more rows in table. and other columns aren't affected either.
#7: yes, the AND... identifies unique row from result set
#8: i'm not using ORDER BY in any view. to be honest i can't see why would the result set be ordered differently each time...
	
@user4592357

Becoming clearer.
I still don't understand how you are editing in a QTableView.  Unless my recollection is incorrect, a QTableView bound to a QSqlQuery is read-only, as you cannot edit the result set.  If you already use some intermediate you haven't said so.  What type is your QSqlQuery::model()QTableView::model()?
#8: i'm not using ORDER BY in any view. to be honest i can't see why would the result set be ordered differently each time...
Good luck!  You would use it because SQL SELECTs don't define row return order without it.  Especially when you're doing JOINs.  Maybe SQLite does define its order.  Or maybe you are fine on stating in your situation it always returns same order.
Nope.  https://www.sqlite.org/lang_select.html:

If a SELECT statement that returns more than one row does not have an ORDER BY clause, the order in which the rows are returned is undefined.
I can only say you would never rely on same ordering with a SQL server.  For example, optimizers or plan caches could change at any time, including between calls.  I guess with SQLite being a simpler beast you are relying on it not doing anything like that.
The (lack of) ORDER BY is only an issue when  you were talking about re-retrieving "the 7th row".  If you are now indeed doing things by unique keys then all the better so this does not arise!
what do you mean by query bound to table?

they're not bound. in that sense.

since i cannot modify the query result set, I'm thinking of keeping a row to record pointer map, and when some row value is edited, i will get the new record pointer and replace it in map (since row number won't be affected by that update).

that means data() will take the data from this map. should i fill the map once and delete the query object? or fill the map lazily? but in later case I'll be using twice as much memory right?
what do you mean by query bound to table?
Darn, sorry, I meant what type is your QTableView::model(), I have corrected my post.
You have talked about
i have a table view to display it.
I have said
If your QTableView is linked to a QSqlQuery I don't see how you can "edit" any cell in the row, because QSqlQuery result set is read-only and won't allow you to edit?
So I am lost.