needing updated values from executed query row

[user4592357]

@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";

[JonB]

@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!

1. You issue some SQL join-view query to produce a result set with 200k rows.
2. You present (some of) those rows in a QTableView.
3. 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.
4. 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?).
5. You send that update to the database.
6. 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?
7. Also, I take it that your proposed AND ... constraints uniquely identify just one row in the result set?
8. 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?

[user4592357]

@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...

[JonB]

@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.

EDIT
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!

[user4592357]

@JonB
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?

[JonB]

@user4592357

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.

[user4592357]

@JonB
just a table model, why?
do you mean if it's editable or not? if so, then yes it is editable

[JonB]

@user4592357
So how is it editable if it is a QSqlQuery model?

[user4592357]

@JonB
it's not a sql query model. as i said I'm using someone's library. so i just reimplement a method which is called from data(), and i use the query as "model" in that way

[JonB]

@user4592357
That was in my question #3 earlier :) OK, now I understand.