Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. QSqlRelation fails on a NULL in foreign key column
Forum Updated to NodeBB v4.3 + New Features

QSqlRelation fails on a NULL in foreign key column

Scheduled Pinned Locked Moved General and Desktop
12 Posts 5 Posters 5.2k Views 2 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • G Offline
    G Offline
    galrub
    wrote on 20 Jun 2011, 16:52 last edited by
    #1

    Hello all,
    I have a table which has two foreign keys, in every row, one is use and the other stays NULL (sqlite).
    when not using QSqlRelation the table shows on the QTableView, so it's a QSqlRelation issue.

    any idea?

    Thanks,
    G

    1 Reply Last reply
    0
    • E Offline
      E Offline
      Eddy
      wrote on 20 Jun 2011, 17:23 last edited by
      #2

      Can you show us your table structure with the definition of your columns?

      Qt Certified Specialist
      www.edalsolutions.be

      1 Reply Last reply
      0
      • G Offline
        G Offline
        galrub
        wrote on 20 Jun 2011, 18:06 last edited by
        #3

        @
        CREATE TABLE card (
        card_idx integer PRIMARY KEY AUTOINCREMENT NOT NULL,
        pincode numeric(4),
        pincode2 numeric(6),
        card_type integer,
        vehicle_idx integer,
        driver_idx integer,
        /* Foreign keys */
        FOREIGN KEY (driver_idx)
        REFERENCES driver(idx)
        ON DELETE SET NULL
        ON UPDATE CASCADE,
        FOREIGN KEY (vehicle_idx)
        REFERENCES vehicle(idx)
        ON DELETE SET NULL
        ON UPDATE CASCADE
        );
        @

        the columns are vehicle_idx and driver_idx... ether a vhicle_idx or a driver_idx are set, so every row in the db has one of these values set to null.

        ThX

        1 Reply Last reply
        0
        • E Offline
          E Offline
          Eddy
          wrote on 20 Jun 2011, 18:24 last edited by
          #4

          Can you show us the relevant code where you use QSqlRelation?

          Qt Certified Specialist
          www.edalsolutions.be

          1 Reply Last reply
          0
          • G Offline
            G Offline
            galrub
            wrote on 20 Jun 2011, 19:02 last edited by
            #5

            ok:
            @
            QSqlRelationalTableModel * model = new QSqlRelationalTableModel(parent, db);
            model ->setTable("card");
            model->setEditStrategy(QSqlTableModel::OnRowChange);
            uint idx = model ->fieldIndex("driver_idx");
            model->setRelation(model ->fieldIndex("vehicle_idx"), QSqlRelation("vehicle", "idx", "plate_number"));
            --model->setRelation(model ->fieldIndex("driver_idx"), QSqlRelation("driver", "idx", "last_name"));

            model->setHeaderData(1, Qt::Horizontal, tr("Pin"));
            model->setHeaderData(2, Qt::Horizontal, tr("Pin2"));
            model->setHeaderData(3, Qt::Horizontal, tr("Type"));
            model->setHeaderData(4, Qt::Horizontal, tr("Vehicle"));
            model->setHeaderData(5, Qt::Horizontal, tr("Driver"));
            model ->select();--
            //.....
            QTableView * view = ui.cardTab.....
            table ->hideColumn(model ->fieldIndex("card_idx"));
            table ->setItemDelegateForColumn(4, new QSqlRelationalDelegate(table));
            table ->setItemDelegateForColumn(5, new QSqlRelationalDelegate(table));
            @

            and, just to make it clear again, if there are values for the both foreign keys then there is no problem, only when one is NULL. if I dont use relation on ether of these columns the table will show empty cells.

            THX,
            G

            1 Reply Last reply
            0
            • E Offline
              E Offline
              Eddy
              wrote on 21 Jun 2011, 14:50 last edited by
              #6

              bq. if there are values for the both foreign keys then there is no problem, only when one is NULL. if I dont use relation on ether of these columns the table will show empty cells.

              The relation needs something to refer to.
              What you can do is add an empty choice using " " or "-" or "none". AFAIK the QSqlRelation needs some value to look it up in the other table. If you don't provide any it can't do anything.

              Could you try this?

              Qt Certified Specialist
              www.edalsolutions.be

              1 Reply Last reply
              0
              • G Offline
                G Offline
                galrub
                wrote on 21 Jun 2011, 15:26 last edited by
                #7

                bq. What you can do is add an empty choice using “ “ or “-” or “none”. AFAIK the QSqlRelation needs some value to look it up in the other table. If you don’t provide any it can’t do anything.

                sadly I cannot do this because of other constraints,

                I will try doing a subclass to the delegate and redo the paint() func to ignore null (0) values.

                Thx

                1 Reply Last reply
                0
                • E Offline
                  E Offline
                  Eddy
                  wrote on 21 Jun 2011, 18:35 last edited by
                  #8

                  bq. sadly I cannot do this because of other constraints,

                  What are those other constraints?

                  Qt Certified Specialist
                  www.edalsolutions.be

                  1 Reply Last reply
                  0
                  • G Offline
                    G Offline
                    galrub
                    wrote on 22 Jun 2011, 07:48 last edited by
                    #9

                    well, now I am working with a local embedded Sqlite3 database, (the plan is that) in the near future the program will connect to the company server (MSSQL) and to that sever there are enterprise servers connected, each one with his own 'requirements' (J2EE, .ASP.NET)...

                    so, as u can see, problems, but, we are considering changing this table soon, since there is a type column, we can have only one driver/vehicle column, until then, will have to do a workaround.

                    PS: I'm the maintenance guy for all of these servers (excluding the MSSQL), so, it's basically up to me... LOL

                    1 Reply Last reply
                    0
                    • A Offline
                      A Offline
                      Atli
                      wrote on 31 Jan 2017, 10:02 last edited by Atli
                      #10

                      I found this post when I was trying to find a solution to a similar issue today but as it didn't have an acceptable answer to my problem I found another one which I thought I would share.

                      Making the model left join the tables means that any row with null foreign keys will be loaded. Using a foreign key delegate will however make you choose a non null option on edit.

                      To do this simply do:

                      model->setJoinMode((QSqlRelationalTableModel::JoinMode) 1);
                      

                      The 1 is the value of the enum for Left Join, 0 is the default (Inner Join). Hope this help someones.

                      V 1 Reply Last reply 3 Apr 2019, 13:29
                      1
                      • SGaistS Offline
                        SGaistS Offline
                        SGaist
                        Lifetime Qt Champion
                        wrote on 31 Jan 2017, 10:08 last edited by
                        #11

                        Hi and welcome to devnet,

                        Thanks for sharing.

                        However why not just use: QSqlRelationalTableModel::LeftJoin ? Doing a C cast on an int like that just makes the code unclear and will break easily if the JoinMode enum changes for whatever reason.

                        Interested in AI ? www.idiap.ch
                        Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

                        1 Reply Last reply
                        1
                        • A Atli
                          31 Jan 2017, 10:02

                          I found this post when I was trying to find a solution to a similar issue today but as it didn't have an acceptable answer to my problem I found another one which I thought I would share.

                          Making the model left join the tables means that any row with null foreign keys will be loaded. Using a foreign key delegate will however make you choose a non null option on edit.

                          To do this simply do:

                          model->setJoinMode((QSqlRelationalTableModel::JoinMode) 1);
                          

                          The 1 is the value of the enum for Left Join, 0 is the default (Inner Join). Hope this help someones.

                          V Offline
                          V Offline
                          Vikinn
                          wrote on 3 Apr 2019, 13:29 last edited by
                          #12

                          @Atli said in QSqlRelation fails on a NULL in foreign key column:

                          I found this post when I was trying to find a solution to a similar issue today but as it didn't have an acceptable answer to my problem I found another one which I thought I would share.

                          Making the model left join the tables means that any row with null foreign keys will be loaded. Using a foreign key delegate will however make you choose a non null option on edit.

                          To do this simply do:

                          model->setJoinMode((QSqlRelationalTableModel::JoinMode) 1);
                          

                          The 1 is the value of the enum for Left Join, 0 is the default (Inner Join). Hope this help someones.

                          IT IS TRUELY WORK! THANK YOU BOTH!
                          THIS IS MY SULOTION:

                          model->setJoinMode(QSqlRelationalTableModel::LeftJoin);
                          
                          1 Reply Last reply
                          1

                          • Login

                          • Login or register to search.
                          • First post
                            Last post
                          0
                          • Categories
                          • Recent
                          • Tags
                          • Popular
                          • Users
                          • Groups
                          • Search
                          • Get Qt Extensions
                          • Unsolved