[odb-users] Foreign key - Query executes multiple times on parent table

[Boris Kolpackov]

Lloyd <lloydkl.tech at gmail.com> writes:

> We have two tables connected using foreign key relationship[:]
>
> CREATE TABLE "Parent" (
> "Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
> "F" TEXT NOT NULL,
> "S" INTEGER NOT NULL,
> "TVS" TEXT NOT NULL);
> 
> CREATE TABLE "Child" (
> "Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
> "N" TEXT NOT NULL,
> "IId" INTEGER NOT NULL,
> CONSTRAINT "IId_fk" FOREIGN KEY("IId") REFERENCES "Parent"("Id") ON DELETE
> CASCADE DEFERRABLE INITIALLY DEFERRED
> );
>
> PRAGMA_DB(object)
> class Parent
> {
> private:
>     Parent(){}
>      friend class odb::access;
>      PRAGMA_DB(id auto)
>      C_UINT64 Id;
>      String F;
>      CC_UINT64 S;
>      std::string TVS;
> };
> 
> PRAGMA_DB(object)
> class Child
> {
> private:
>      Child(){}
>      friend class odb::access;
>      PRAGMA_DB(id auto)
>      CC_UINT64 Id;
>      CC_UINT64 IId;
>      std::string N;
> 
>      PRAGMA_DB(not_null)
>      PRAGMA_DB(on_delete(cascade))
>      PRAGMA_DB(index)
>      std::shared_ptr<Parent>IId;
> public:
>    ...
>    inline C_UINT64 GetIId() { return IId; }
> };
>
> 
> When tracing the query execution using ODB tracer, we observe that In
> each iteration a "select query" is executed on the parent table also
> (Isn't it unnecessary?).

The default semantics of loading an object in ODB is to also load all
its relationships (Child::IId in this case). There are various ways
to avoid this (lazy pointers, sections, etc) but they don't really
apply to your case (see below).


> How can I avoid the "select query" execution on the parent table?
>
> result r = DBPtr.query<Child>( query::ID == id);
> for (result::<Child>iterator fdt(r.begin());fdt != r.end(); ++fdt)
> {
>   fdt->GetIId();//Too slow. Executes a select query on parent
> }

You cannot really avoid touching both tables because you need
information from both. But you can make it a single query using
a view:

https://www.codesynthesis.com/products/odb/doc/manual.xhtml#10.1