[odb-users] Querying entity's container

[Adnan RIHAN]

I need to find an entity based of one of it’s container’s data.

Let me explain, I’m on a CashRegister app.

I have the Product entity, which contains a Containers of String (QSet<QString>, unique in the table) to store the barcodes.
I have an Offer entity, which stores a Product and a Price.

I need to be able to find an Offer, using a barcode. So basically, except if it’s easily possible, I think I need to find a Product based on a barcode but I don’t see how to build the “odb::query<Product>” to find the Product which contains “barcode” in its container.

Is there a way, or do I need a workaround? Or suggestions?
Thanks
--
Cordialement, Adnan RIHAN.
Directeur-Gérant de Eolis-Software, société de services informatiques.

GPG: 5675-62BA (https://keybase.io/max13/key.asc)
-> Si vous n'utilisez pas GPG mais souhaitez quand même m’envoyer un e-mail chiffré: (https://encrypt.to/0x567562BA).

[Boris Kolpackov]

Hi Adnan,

Post by Adnan RIHAN
I need to find an entity based of one of it’s container’s data.
I have the Product entity, which contains a Containers of String
(QSet<QString>, unique in the table) to store the barcodes.

Support for containers in queries is the next major feature on
the TODO list.

In the meantime:

If we are talking about containers of object pointers (not your
case, I know), then a fairly straightforward workaround is to
use an object loading view (Section 10.2, "Object Loading Views"
in the ODB manual has an example of this specifically).

If it is a container of values (like your case), then things are
a bit harder and we have to resort to a fairly hackish workaround.
In a nutshell, the idea is to define a class that is an "image"
of the container table and then JOIN it into an object loading
view, just like in the above case.

Here is an example. Say we have this product class:

#pragma db object
struct product
{
public:
#pragma db id auto
unsigned long id_;

std::set<std::string> barcodes_;
};

If we generate the database schema for it, the container table will
look like this (using SQLite):

CREATE TABLE "product_barcodes" (
"object_id" INTEGER NOT NULL,
"value" TEXT NOT NULL,
CONSTRAINT ...);

Next we create a special "image" object that matches this table:

#pragma db object table("product_barcodes") no_id abstract
struct product_barcode
{
person* object_id;
std::string value;
};

A couple of things to note about this object:

1. It uses the same table name as the container table (product_barcodes).
In a way, we are overlaying this object on the container table.

2. object_id and value member names are not arbitrary, they should
match the table columns (or you can use db column() pragma to
assign them explicitly).

Once this is done, we create an object loading view that JOIN's
product and its container:

#pragma db view object(product) object(product_barcode = barcode) \
query(distinct)
struct product_view
{
std::chared_ptr<product> p;
};

Now we can use this view to load a product that matches a barcode:

typedef odb::query<product_view> query;

db.query<product_view> (query::barcode::value == "123456");

Boris

[Adnan RIHAN]

Hi Boris,

typedef odb::query query;
db.query (query::barcode::value == "123456");

Thanks a lot for that help. But I had compiler issues.

Here is how I had to implement it (I only show what seems relevant):
Product.cpp
// *SP means: QSharedPointer<*>
class Product : public BaseModel
{
    private:
        CategorySP      m_category;
        QSet<QString>   m_barcodes;
};

typedef QSharedPointer<Product> ProductSP;

struct product_barcode
{
    ProductSP   product_id; # <-- shared_ptr instead of ptr directly
    QString     barcode;
};

struct product_view
{
    ProductSP   p;
};

# ifdef     ODB_COMPILER
#   pragma  db  model                           version(1, 1, open)
#   pragma  db  object(Product)                 table("products")
#   pragma  db  member(Product::m_category)     not_null                    column("category_id")
#   pragma  db  member(Product::m_barcodes)     table("product_barcodes")   id_column("product_id") value_column("barcode")

#   pragma  db  object(product_barcode)         table("product_barcodes")   no_id   abstract
#   pragma  db  member(product_barcode::product_id) column("product_id")
#   pragma  db  member(product_barcode::barcode)    column("barcode")
#   pragma  db  view(product_view)              object(Product) object(product_barcode = barcode)   query(distinct)
# endif
// END

And this is the compiler error I had:
/Users/Max13/Dev/System/LGC/Desktop/apps/LGC/src/Models/Product/Product-odb.cxx:1790:13: error: cannot cast from type 'typename object_traits<Product>::pointer_type' (aka 'QSharedPointer< ::Product>') to pointer type 'ptr_traits::pointer_type' (aka 'Product *')
        v = ptr_traits::pointer_type (
            ^~~~~~~~~~~~~~~~~~~~~~~~~~
1 error generated.

So I thought it was just a typo. It’s good thanks.
===================
I was using « query() » before 2.4.0 update. Now I know query_one exists, I wanted to give a try. But then, I have a more complicated compiler issue:
In file included from /Users/Max13/Dev/System/LGC/Desktop/apps/LGC/src/CashRegister/CashRegisterWidget.cpp:5:
In file included from /usr/local/include/odb/qt/list.hxx:8:
In file included from /usr/local/include/odb/qt/containers/list.hxx:22:
In file included from /usr/local/include/odb/vector-impl.hxx:14:
In file included from /usr/local/include/odb/transaction.hxx:263:
In file included from /usr/local/include/odb/transaction.ixx:5:
In file included from /usr/local/include/odb/connection.hxx:19:
In file included from /usr/local/include/odb/prepared-query.hxx:12:
In file included from /usr/local/include/odb/result.hxx:242:
/usr/local/include/odb/result.txx:19:20: error: no viable conversion from 'typename view_traits<product_view>::pointer_type' (aka 'QSharedPointer< ::product_view>') to 'pointer_type' (aka 'product_view *')
      pointer_type o (i.load ());
                   ^  ~~~~~~~~~
/usr/local/include/odb/database.ixx:837:14: note: in instantiation of member function 'odb::result<product_view>::one' requested here
    return r.one ();
             ^
/usr/local/include/odb/database.ixx:647:12: note: in instantiation of function template specialization 'odb::database::query_one_<product_view, 5, odb::query<product_view, odb::sqlite::query_base> >' requested here
    return query_one_<T, id_common> (q);
           ^
/Users/Max13/Dev/System/LGC/Desktop/apps/LGC/src/Controllers/EntityManager/EntityManager.hpp:247:39: note: in instantiation of function template specialization 'odb::database::query_one<product_view>' requested here
                one.reset(this->m_db->query_one<T>(query));
                                      ^
/Users/Max13/Dev/System/LGC/Desktop/apps/LGC/src/CashRegister/CashRegisterWidget.cpp:156:72: note: in instantiation of function template specialization 'EntityManager::findOne2<product_view>' requested here
    QSharedPointer<product_view>    pv(MasterController::inst()->em()->findOne2<product_view>(odb::query<product_view>::barcode::barcode == scan));
                                                                       ^
/Users/Max13/Qt/5.4/clang_64/lib/QtCore.framework/Headers/qsharedpointer_impl.h:299:12: note: candidate function
    inline operator RestrictedBool() const { return isNull() ? 0 : &QSharedPointer::value; }
           ^
1 error generated.

It may be another thread, but I didn’t try it with another call. It seems that « query_one » returns a raw pointer, while it tries to assign it from a shared_ptr. The error doesn’t change if I set the product_view::p to raw pointer.

Is it a bug?
--
Cordialement, Adnan RIHAN.
Directeur-Gérant de Eolis-Software, société de services informatiques.

GPG: 5675-62BA (https://keybase.io/max13/key.asc)
-> Si vous n'utilisez pas GPG mais souhaitez quand même m’envoyer un e-mail chiffré: (https://encrypt.to/0x567562BA).

[Boris Kolpackov]

Hi Adnan,

Both issues that you are observing stem from incompatible pointers.
If you make all your objects/view use QSharedPointer (e.g., by
enabling the Qt profile), then you should use QSharedPointer in
relationships (the first issue) or when getting a result from
calls like query_one() (second issue).

Boris

[Adnan RIHAN]

Hi,

Post by Boris Kolpackov
then you should use QSharedPointer in
relationships (the first issue) or when getting a result from
calls like query_one() (second issue).

This is what I do. But the issue seems not being the return of my function, but the return inside result.cxx

/usr/local/include/odb/result.txx:19:20: error: no viable conversion from 'typename view_traits<product_view>::pointer_type' (aka 'QSharedPointer< ::product_view>') to 'pointer_type' (aka 'product_view *') 
      pointer_type o (i.load ());
                   ^  ~~~~~~~~~

In my methods, I always use QSharedPointer. The thing is that I’m trying with query_one() but the same call with query() is working (then I return the first result if it’s not null).

Post by Boris Kolpackov
QSharedPointer<product_view>   one(new product_view);
 try {
     odb::result<product_view>   r(this->m_db->query<product_view>(
         odb::query<product_view>::barcode::barcode == scan
     ));
     typename odb::result<product_view>::iterator   i(r.begin());
 
     if (i != r.end()) {
         *one = *i;
     }
 } catch (const odb::exception &e) {}
 return (one);
 QSharedPointer<product_view>   one;
 try {
     one.reset(this->m_db->query_one<product_view>(
         odb::query<product_view>::barcode::barcode == scan
     ));
 } catch (const odb::exception &e) {}
 return (one);

In the two functions, the template is « product_view » and the return type is a QSharedPointer<T>
--
Cordialement, Adnan RIHAN.
Directeur-Gérant de Eolis-Software, société de services informatiques.

GPG: 5675-62BA (https://keybase.io/max13/key.asc)
-> Si vous n'utilisez pas GPG mais souhaitez quand même m’envoyer un e-mail chiffré: (https://encrypt.to/0x567562BA).

[Boris Kolpackov]

Hi Adnan,

Post by Adnan RIHAN
/usr/local/include/odb/result.txx:19:20: error: no viable conversion
from 'typename view_traits<product_view>::pointer_type' (aka
'QSharedPointer< ::product_view>') to 'pointer_type' (aka 'product_view *')

Ok, this is a bug and here is the fix:

http://scm.codesynthesis.com/?p=odb/libodb.git;a=commit;h=b119086e8c5835695cd851da8ad1393218aa29df

Could you try it and let me know if there are any issues?

Boris

[Adnan RIHAN]

Hi Boris,

Post by Boris Kolpackov
Could you try it and let me know if there are any issues?

Almost good (now it’s in database.hxx):

In file included from /usr/local/include/odb/database.hxx:631:
/usr/local/include/odb/database.ixx:837:12: error: no viable conversion from 'pointer_type' (aka 'QSharedPointer< ::offer_view>') to 'typename object_traits<offer_view>::pointer_type' (aka 'offer_view *')
    return r.one ();
           ^~~~~~~~
/usr/local/include/odb/database.ixx:647:12: note: in instantiation of function template specialization 'odb::database::query_one_<offer_view, 5, odb::query<offer_view, odb::sqlite::query_base> >' requested here
    return query_one_<T, id_common> (q);
           ^
/Users/Max13/Dev/System/LGC/Desktop/apps/LGC/src/Controllers/EntityManager/EntityManager.hpp:247:39: note: in instantiation of function template specialization 'odb::database::query_one<offer_view>' requested here
                one.reset(this->m_db->query_one<T>(query));
                                      ^
/Users/Max13/Dev/System/LGC/Desktop/apps/LGC/src/CashRegister/CashRegisterWidget.cpp:162:42: note: in instantiation of function template specialization 'EntityManager::findOne2<offer_view>' requested here
    ov = MasterController::inst()->em()->findOne2<offer_view>(
                                         ^
--
Cordialement, Adnan RIHAN.
Directeur-Gérant de Eolis-Software, société de services informatiques.

GPG: 5675-62BA (https://keybase.io/max13/key.asc)
-> Si vous n'utilisez pas GPG mais souhaitez quand même m’envoyer un e-mail chiffré: (https://encrypt.to/0x567562BA).

[Boris Kolpackov]

Hi Adnan,
Uh, oh:

http://scm.codesynthesis.com/?p=odb/libodb.git;a=commit;h=ee4d942916d347ac65f53969941b0fb100760611
http://scm.codesynthesis.com/?p=odb/libodb-sqlite.git;a=commit;h=27a578709046a81bb0efc0027bfc74318615447e

Let me know if it still doesn't work.

Thanks,
Boris

[Adnan RIHAN]

Hi Boris,

Post by Boris Kolpackov
Let me know if it still doesn't work.

YOU DID IT !

It works. I’ve also updated the homebrew packages, added the patches and pushed.
--
Cordialement, Adnan RIHAN.
Directeur-Gérant de Eolis-Software, société de services informatiques.

GPG: 5675-62BA (https://keybase.io/max13/key.asc)
-> Si vous n'utilisez pas GPG mais souhaitez quand même m’envoyer un e-mail chiffré: (https://encrypt.to/0x567562BA).