Qt and SQLite: filter using the elements of an array

Question

I'm currently working on a project and I'm quite new to Qt and SQLITE. I currently have an integer array and would like to use it to obtain data linked to the elements in the array. For example, if the data is 61616, it will output Sarah.

query.prepare("SELECT name,memberType FROM members WHERE id = :id");
for(index = 0; index < dateIndex; index++)
{
    id = idAr[index];
    query.addBindValue(id);
    query.exec();
}

model2->setQuery(query);
ui->tableView_10->setModel(model2);

I know this is wrong. Can someone explain how to properly do this?

Update:

std::vector<int> idA={3, 7, 15, 16, 19, 30};
QSqlQuery qry(QString("SELECT name, memberType FROM members WHERE id IN (?%1)")
                .arg(QString(", ?").repeated(idA.size()-1)));
for(int i = 0; i < idA.size(); i++)
{
    int id2 = idA[i];
    qry.addBindValue(id2);
}
qry.exec();
model2->setQuery(qry);
ui->tableView_10->setModel(model2);

However, it still does not output to the table.

I mainly want to get all the data from SQLite that has that specific id and output it into a table in QT. There's currently 8 elements in the array and I want to output all 8 into a table with the respective data. — sharr, Mar 31 '18 at 21:14
So this is the folder. Sorry about the bad coding. New to Qt and Sqlite. The problem occurs in mainwindow.cpp under pushbotton 9 clicked https://drive.google.com/open?id=1eJA90fPaI16HoojzzWBKkJ5l1m-d0SiC — sharr, Apr 01 '18 at 23:33

eyllanesc · Accepted Answer · 2018-04-01T23:52:20.463

In this case the sentence you have to use is IN:

SELECT name, memberType FROM members WHERE id IN (2, 4, 5, 6)

In this case we will use the string concatenation:

std::vector<int> idA = {3, 7, 15, 16, 19, 30};

QStringList ids_string;
for(const int & val : idA) 
    ids_string << QString::number(val);

QSqlQuery query(QString("SELECT name, memberType FROM members WHERE id IN (%1)")
                .arg(ids_string.join(",")));
model.setQuery(query);

Example:

#include <QApplication>
#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlQueryModel>
#include <QTableView>

static bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (!db.open()) {
        qDebug()<<"Unable to establish a database connection";
        return false;
    }
    QSqlQuery query;
    query.exec("CREATE TABLE IF NOT EXISTS members (id INTEGER PRIMARY KEY AUTOINCREMENT, "
               "name VARCHAR(20), memberType VARCHAR(20))");
    for(int i=1; i<40; i++)
        query.exec(QString("insert into members(name, memberType) values('name%1', 'memberType%2')").arg(i).arg(i));
    return true;
}

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    if(!createConnection())
        return -1;

    QTableView w;
    QSqlQueryModel model;
    std::vector<int> ids = {3, 7, 15, 16, 19, 30};
    QStringList ids_string;
    for(const int & val : ids) ids_string<<QString::number(val);
    QSqlQuery query(QString("SELECT name, memberType FROM members WHERE id IN (%1)")
                    .arg(ids_string.join(",")));
    model.setQuery(query);
    w.setModel(&model);
    w.show();
    return a.exec();
}

Another solution:

int std::vector<int> idAr={3, 7, 15, 16, 19, 30};
int dateIndex = 6;
QSqlQuery query(QString("SELECT name, memberType FROM members WHERE id IN (?%1)")
                .arg(QString(", ?").repeated(idAr.size()-1)));
for(const int & id: idAr)
    query.addBindValue(id);
query.exec();

Thanks for all this! To be honest, I don't know what most of this means but I will do my best to figure it out piece by piece. — sharr, Mar 31 '18 at 22:08
@sharr The problem is in the ids that you are using, they do not exist, if you check the database with some database viewer you will see the following: https://imgur.com/a/W72lz, and clearly the ids are large numbers, so that you observe some data changes to `std::vector idA = {12121, 12899, 16161, 44444};` — eyllanesc, Apr 01 '18 at 23:32
Oh. So if I were to use the idAr above the test that I did with your solution, would it work? That idAr has all the ids that I need stored there. — sharr, Apr 01 '18 at 23:37
@sharr I just executed your code with that change and get the following: https://imgur.com/a/pCq2x — eyllanesc, Apr 01 '18 at 23:39
Great! Thank you so much! I got it to work now I just need to do some minor tweaks. — sharr, Apr 01 '18 at 23:44

Qt and SQLite: filter using the elements of an array

1 Answers1