SQL Query Organization

[dk]

True, you are right! def much more readable. But i do have an other question.
I can see the massive benefit of arranging the queries the way you explained. But how about inner joins? or a query from multible tables?

For example i designed the database in the way that a user has a user_status. so i created a table called user_status_tbl. with an ID and a Description
The user_tbl contains one column called user_status which referes to the user_status_tbl.

How can i implement that without having to programm the SQLQuery in C++ and also take advantave of the simplicity and speed of an SQLQuery?

Thanks again my friend, ou are so helpful!

[stampede]

Great, i'm glad I can help

But how about inner joins? or a query from multible tables?

For example i designed the database in the way that a user has a user_status. so i created a table called user_status_tbl. with an ID and a Description
The user_tbl contains one column called user_status which referes to the user_status_tbl.

How can i implement that without having to programm the SQLQuery in C++ and also take advantave of the simplicity and speed of an SQLQuery?

You can still use SQL code to do cross-table selections and return this data in a data structure, for example:

// extended version of User class
class UserExt{
public:
    UserExt(QString name, QString address, QString description...) 
        :_name(name)
        ,_address(address)
        ,_description(description) ... {
    }
    QString name() const{ return _name; }
    QString address() const{ return _address; }
    QString description() const{ return _description; }
    // .. other access methods
private:
    QString _name;
    QString _address;
    QString _description;
    ...
};
 
// it is just a pseudocode but i think you get the idea
User myClass::GetUser(int userId)
{
   QSqlQuery q;
   q.prepare("SELECT u.name,u.address,st.description FROM User as u, user_status_tbl as st, othertable as ot, ... WHERE u.ID = :userId AND u.id=st.id AND ot.id=st.id, AND ...;");
   q.bindValue(":id",userId);
   if (q.next()) {
      return UserExt(q.value("name").toString(), q.value("address").toString(), q.value("description").toString()...);
   }
   throw new NoSuchUserException; //.. or another way of reporting an error
}

// extended version of User class
class UserExt{
public:
    UserExt(QString name, QString address, QString description...) 
        :_name(name)
        ,_address(address)
        ,_description(description) ... {
    }
    QString name() const{ return _name; }
    QString address() const{ return _address; }
    QString description() const{ return _description; }
    // .. other access methods
private:
    QString _name;
    QString _address;
    QString _description;
    ...
};
 
// it is just a pseudocode but i think you get the idea
User myClass::GetUser(int userId)
{
   QSqlQuery q;
   q.prepare("SELECT u.name,u.address,st.description FROM User as u, user_status_tbl as st, othertable as ot, ... WHERE u.ID = :userId AND u.id=st.id AND ot.id=st.id, AND ...;");
   q.bindValue(":id",userId);
   if (q.next()) {
      return UserExt(q.value("name").toString(), q.value("address").toString(), q.value("description").toString()...);
   }
   throw new NoSuchUserException; //.. or another way of reporting an error
}

To copy to clipboard, switch view to plain text mode 

[dk]

Hello stampede,

very good idea with putting each query in an object. Much easier for further processing! Especialy the more complex queryies become more unified towards the actual application, fantastic!
Thanks you very much for all your help and effort, i wish i could pay a beer for you my friend