Qt and Relational Databases

? Offline

Hello Forum.

I've had Qt on and off my computer for the last several years, mainly just had it to have it, a dust collector. I'm heavy into website dev, using a lot of JQuery, PHP and MariaDB. I also use Lazarus (Object Pascal) to create desktop apps as I go along. For the past couple months, I've been working on an app for my girlfriend. She has a bakery and just wants a little something for recipes, ingredients, inventory, profit/loss. I started this in Lazarus. The data-aware components make it a breeze to use. But at the same time, I tinkered with the same in Qt. And since my app is all about relational databases, that's what I'm here to throw your way.

I found a small Qt relational database tutorial online. It combined the master table and a detail table. The output was less than desirable. The master table has recipe IDs and names. The detail table has ingredients and amounts for the various recipes, tied together by the recipe IDs. But when I ran the Qt relational database app, one table view listed the recipe names (multiple times) and the ingredient/amounts.

What I'd like to have is a form with three views: recipe names, ingredients/amounts, and notes. As I scroll in the first view of names, I want the ingredients/amounts to be reflected in view two and the note in view three. I can't find anything like this to use as a guide.

I've found a slew of Qt tutorials on youtube. But only a handful on databases. Most business apps today will utilize a database in one way or another. It may be that Qt is limited in this regard, limited in ways that Delphi and Lazarus aren't. I hope that's not the case. But I find such a tiny amount of useful information on using databases with Qt.

Can someone pass me some good links? Good book titles?

JKSH

@landslyde said in Qt and Relational Databases:

I've found a slew of Qt tutorials on youtube. But only a handful on databases. Most business apps today will utilize a database in one way or another. It may be that Qt is limited in this regard, limited in ways that Delphi and Lazarus aren't. I hope that's not the case. But I find such a tiny amount of useful information on using databases with Qt.

What you'd find most helpful right now is probably a general tutorial on SQL. The tutorial doesn't need to be Qt-based.

Work out the queries that give you what you want first. Then, pass those queries into the relevant Qt classes.

It combined the master table and a detail table... The master table has recipe IDs and names. The detail table has ingredients and amounts for the various recipes, tied together by the recipe IDs. But when I ran the Qt relational database app, one table view listed the recipe names (multiple times) and the ingredient/amounts.

That database schema sounds OK to me (but there are other ways to organize the data). You probably need to fine-tune the query.

What were the queries that you tried?

? Offline

@JKSH
I can write good queries. But the relational database Qt tutorial I saw didn't use them. Not even one. Hence part of my confusion on how all of this is suppose to work. Let me provide the short code to show you what I'm talking about:

#include "mainwindow.h"
#include "ui_mainwindow.h"

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("zyx.db");

    if(!db.open())
        qDebug() << "Failed to connect to database";
    else
        qDebug() << "Successfully connected to database";

    model = new QSqlRelationalTableModel(this);
    model->setTable("recipe_names");
    model->setRelation(0,QSqlRelation("recipe_ingredients",
                                      "name_id","ingredient, amount"));
    model->select();

    ui->tableView->setModel(model);
}

MainWindow::~MainWindow()
{
    delete ui;
}

I'd rather write queries, use joins. But this is what the tutorial showed me, and it's all Greek to me. Not one piece of explanation as to why that code uses no queries. Qt seems like a robust way to implement C++, and I know down deep that working with relational databases isn't as puzzling as it seems to me right now. But, as you can see with the code I provided, what do I have to go on?

My idea is like this: three table views on a form. One being the master, recipe names, in this case. Views two and three providing ingredients/amounts and notes. I have this working in Lazarus. Took me all of 15 minutes. But I'd like to be able to do it with Qt.

My data is well organized in the db tables, so that's not an issue. I just need to know how to access my tables in a relational manner. Again, that tutorial code lacks a lot to be desired.

Christian Ehrlicher

I think you should take a look at http://doc.qt.io/qt-5/qsqlquerymodel.html or even write your own data model class.

KillerSmath

@landslyde Hi there.

If i'm not wrong, the QSqlRelation just replace one by one column as (name_id -> ingredient) using the foreign key to relation the tables.
So, if you are trying to select data of complex tables (request more then one column), i suggest you to use QSqlQueryModel and set a complex query to select all information that you need.

Below is an example with a similiar database tables:

create table recipes ( 
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name  varchar(40) UNIQUE NOT NULL
);

create table recipes_details (
  id_recipe INTEGER REFERENCES recipes(id), /* foreign key to recipes */
  ingredient varchar(30) NOT NULL,
  amount int NOT NULL
);

INSERT INTO recipes (name) VALUES 
('Recipe A'),
('Recipe B'),
('Recipe C');

INSERT INTO recipes_details (id_recipe, ingredient, amount) VALUES
(1, 'Ingredient 1', 3),
(1, 'Ingredient 2', 5),
(2, 'Ingredient 1', 5),
(3, 'Ingredient 1', 10);

You could to use a query like that to select the other columns by foreign key:

SELECT R.name AS 'Recipe', RD.ingredient AS 'Ingredient', RD.amount as 'Amount' 
FROM recipes AS R 
INNER JOIN recipes_details AS RD ON R.id = RD.id_recipe

Result:

References:
QT - QSqlQueryModel Documentation
Inner Join Explanation

? Offline

@KillerSmath & @Christian-Ehrlicher
I have to use multiple views. Remember: when I select a recipe name in view-1, the ingredients will be shown in view-2, and the recipe note in view-3. That's my problem, controlling the view window data. So it wld be like this:

For View-1:

SELECT * FROM recipe_names ORDER BY name;

Now, as I use the mousewheel or arrow keys, I can scroll down the names in View-1. For each selected name, View-2 displays the recipe's ingredients, and View-3 displays the note. So I guess I'm looking for a cell-selected event to trigger the queries for Views-2 and -3. This is easy for me in Lazarus (Object Pascal), but I'm not cluing in to the Qt way. But I do think this is what I'm looking for. You follow me?

** I just looked at the documentation you provided: QT - QSqlQueryModel Documentation. Using that, I think I can work it out from here. Thanks for the heads up on that.

KillerSmath

Understood. I used the same idea in a personal project.
Basically, you could to use a listView to show the recipes and connect the click signal of listView to slot in mainwindow or lambda function.
When the signal is emitted, you capture the name of recipe on cell and execute a personal query to update the tableView of ingredients of specific recipe :)

? Offline

Exactly. And now for the fun part: tying her inventory to the recipes =)

Thanks for your help. Much appreciated.