QSqlRelationalTableModel setRelation() unable to select()

[Armin]

Hi,
I'm writing a small application with relations between two tables using SQLLITE. The second Table has a relation to the first one, which works fine with model and tableview, but a query to the second table only works if do not use setrelation(). I mnimzed the code as an example and inserted a fixed query that shoud show one line in the second table (and does that if I comment out the line colorModel->setRelation(color_Other, QSqlRelation("other", "id", "name"));.

What did I wrong with that? Thanks for any hint in advance
Armin

mainwindow.cpp

#include <QtGui>
#include <QtSql>
#include "mainwindow.h"
MainWindow::MainWindow()
{
  createOtherDock();
  createColorDock();
}
//---------------------------------------------------------------------------
void MainWindow::createOtherDock()
{
  QDockWidget *dock = new QDockWidget(tr("Other"), this);
 
  otherModel = new QSqlRelationalTableModel(this);
  otherModel->setTable("other");
  otherModel->select();
 
  otherView = new QTableView(dock);
  otherView->setModel(otherModel);
 
  dock->setWidget(otherView);
  addDockWidget(Qt::RightDockWidgetArea, dock);
}
//---------------------------------------------------------------------------
void MainWindow::createColorDock()
{
  QDockWidget *dock = new QDockWidget(tr("Color"), this);
 
  colorModel = new QSqlRelationalTableModel(this);
  colorModel->setTable("color");
// the following is the line that mixes up the query
  colorModel->setRelation(color_Other, QSqlRelation("other", "id", "name"));
  colorModel->select();
 
  colorView = new QTableView(dock);
  colorView->setModel(colorModel);
 
  dock->setWidget(colorView);
  addDockWidget(Qt::RightDockWidgetArea, dock);
}
//---------------------------------------------------------------------------
void MainWindow::filterColorView()
{
  colorModel->setFilter("name like 'Bl%'");
  colorModel->select();
  colorView->horizontalHeader()->setVisible(colorModel->rowCount() > 0);
}

#include <QtGui>
#include <QtSql>
#include "mainwindow.h"
MainWindow::MainWindow()
{
  createOtherDock();
  createColorDock();
}
//---------------------------------------------------------------------------
void MainWindow::createOtherDock()
{
  QDockWidget *dock = new QDockWidget(tr("Other"), this);

  otherModel = new QSqlRelationalTableModel(this);
  otherModel->setTable("other");
  otherModel->select();

  otherView = new QTableView(dock);
  otherView->setModel(otherModel);

  dock->setWidget(otherView);
  addDockWidget(Qt::RightDockWidgetArea, dock);
}
//---------------------------------------------------------------------------
void MainWindow::createColorDock()
{
  QDockWidget *dock = new QDockWidget(tr("Color"), this);

  colorModel = new QSqlRelationalTableModel(this);
  colorModel->setTable("color");
// the following is the line that mixes up the query
  colorModel->setRelation(color_Other, QSqlRelation("other", "id", "name"));
  colorModel->select();

  colorView = new QTableView(dock);
  colorView->setModel(colorModel);

  dock->setWidget(colorView);
  addDockWidget(Qt::RightDockWidgetArea, dock);
}
//---------------------------------------------------------------------------
void MainWindow::filterColorView()
{
  colorModel->setFilter("name like 'Bl%'");
  colorModel->select();
  colorView->horizontalHeader()->setVisible(colorModel->rowCount() > 0);
}

To copy to clipboard, switch view to plain text mode 

mainwindow.h

#ifndef MAINWINDOW_H
#define MAINWINDOW_H
 
#include <QtGui>
#include <QtSql>
 
enum {
  other_Id = 0,
  other_Name = 1,
};
enum {
  color_Id = 0,
  color_Name = 1,
  color_Other = 2,
};
 
class MainWindow : public QMainWindow
{
  Q_OBJECT
 
public:
  MainWindow();
  void filterColorView();
private:
  void createOtherDock();
  void createColorDock();
 
  QSqlRelationalTableModel *otherModel;
  QSqlRelationalTableModel *colorModel;
 
  QTableView *otherView;
  QTableView *colorView;
 
};
 
#endif

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QtGui>
#include <QtSql>

enum {
  other_Id = 0,
  other_Name = 1,
};
enum {
  color_Id = 0,
  color_Name = 1,
  color_Other = 2,
};

class MainWindow : public QMainWindow
{
  Q_OBJECT

public:
  MainWindow();
  void filterColorView();
private:
  void createOtherDock();
  void createColorDock();

  QSqlRelationalTableModel *otherModel;
  QSqlRelationalTableModel *colorModel;

  QTableView *otherView;
  QTableView *colorView;

};

#endif

To copy to clipboard, switch view to plain text mode 

And for convenience if someone wants to compile it, main.cpp:

#include <QApplication>
#include <QtGui>
#include <QtSql>
 
#include "mainwindow.h"
//---------------------------------------------------------------------------
bool Connect()
{
  QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
  db.setDatabaseName("data.dat");
  if (!db.open())
  {
      QMessageBox::warning(0, QObject::tr("Error"), db.lastError().text());
      return (false);
  }
  return (true);
}
//---------------------------------------------------------------------------
void createDataBase()
{
  QSqlQuery query;
  query.exec("DROP TABLE other");
  query.exec("DROP TABLE color");
 
  query.exec("CREATE TABLE other ("
             "id INTEGER PRIMARY KEY AUTOINCREMENT, "
             "name VARCHAR(10) NOT NULL)");
 
  query.exec("CREATE TABLE color ("
             "id INTEGER PRIMARY KEY AUTOINCREMENT, "
             "name VARCHAR(40) NOT NULL, "
             "otherid INTEGER NOT NULL, "
             "FOREIGN KEY (otherid) REFERENCES other)");
 
  query.prepare("INSERT INTO other (id, name) "
                  "VALUES (:id, :name)");
  query.bindValue(":id", 1);
  query.bindValue(":name", "A");
  query.exec();
  query.bindValue(":id", 2);
  query.bindValue(":name", "B");
  query.exec();
 
  query.prepare("INSERT INTO color (id, name, otherid) "
                 "VALUES (:id, :name, :otherid)");
  query.bindValue(":id", 1);
  query.bindValue(":name", "Red");
  query.bindValue(":otherid", 1);
  query.exec();
  query.bindValue(":id", 2);
  query.bindValue(":name", "Green");
  query.bindValue(":otherid", 2);
  query.exec();
  query.bindValue(":id", 3);
  query.bindValue(":name", "Blue");
  query.bindValue(":otherid", 1);
  query.exec();
}
//---------------------------------------------------------------------------
 
int main(int argc, char *argv[])
{
  QApplication app(argc, argv);
 
  bool existingData = QFile::exists("data.dat");
  if (!Connect())
  {
    return(1);
  }
  if (!existingData)
  {
    createDataBase();
  }
  MainWindow Window;
  Window.show();
  Window.filterColorView();
  return app.exec();
}

#include <QApplication>
#include <QtGui>
#include <QtSql>

#include "mainwindow.h"
//---------------------------------------------------------------------------
bool Connect()
{
  QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
  db.setDatabaseName("data.dat");
  if (!db.open())
  {
      QMessageBox::warning(0, QObject::tr("Error"), db.lastError().text());
      return (false);
  }
  return (true);
}
//---------------------------------------------------------------------------
void createDataBase()
{
  QSqlQuery query;
  query.exec("DROP TABLE other");
  query.exec("DROP TABLE color");

  query.exec("CREATE TABLE other ("
             "id INTEGER PRIMARY KEY AUTOINCREMENT, "
             "name VARCHAR(10) NOT NULL)");

  query.exec("CREATE TABLE color ("
             "id INTEGER PRIMARY KEY AUTOINCREMENT, "
             "name VARCHAR(40) NOT NULL, "
             "otherid INTEGER NOT NULL, "
             "FOREIGN KEY (otherid) REFERENCES other)");

  query.prepare("INSERT INTO other (id, name) "
                  "VALUES (:id, :name)");
  query.bindValue(":id", 1);
  query.bindValue(":name", "A");
  query.exec();
  query.bindValue(":id", 2);
  query.bindValue(":name", "B");
  query.exec();

  query.prepare("INSERT INTO color (id, name, otherid) "
                 "VALUES (:id, :name, :otherid)");
  query.bindValue(":id", 1);
  query.bindValue(":name", "Red");
  query.bindValue(":otherid", 1);
  query.exec();
  query.bindValue(":id", 2);
  query.bindValue(":name", "Green");
  query.bindValue(":otherid", 2);
  query.exec();
  query.bindValue(":id", 3);
  query.bindValue(":name", "Blue");
  query.bindValue(":otherid", 1);
  query.exec();
}
//---------------------------------------------------------------------------

int main(int argc, char *argv[])
{
  QApplication app(argc, argv);

  bool existingData = QFile::exists("data.dat");
  if (!Connect())
  {
    return(1);
  }
  if (!existingData)
  {
    createDataBase();
  }
  MainWindow Window;
  Window.show();
  Window.filterColorView();
  return app.exec();
}

To copy to clipboard, switch view to plain text mode 

[norobro]

Use "table_name.column_name" in your filter.

[Armin]

Thanks a lot norobro, now it works

[kenez]

Thank you very much norobro, your post was very helpful!