Solved needing updated values from executed query row
-
@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 (ofm_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 yourQTableView
is linked to aQSqlQuery
I don't see how you can "edit" any cell in the row, becauseQSqlQuery
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 changeCountryID
inUsers
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, aQTableView
bound to aQSqlQuery
is read-only, as you cannot edit the result set. If you already use some intermediate you haven't said so. What type is yourQSqlQuery::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
SELECT
s don't define row return order without it. Especially when you're doingJOIN
s. 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 anORDER 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! -
@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? -
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.
-
@JonB
just a table model, why?
do you mean if it's editable or not? if so, then yes it is editable -
@user4592357
So how is it editable if it is aQSqlQuery
model? -
@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 -
@user4592357
That was in my question #3 earlier :) OK, now I understand.