ORM get joined table row count

Question

I'm currently using Witty and DBO with SQLite3 inside a project, and now I need to join a related table to get rows count only.

Tables

CREATE TABLE "rfnode" (
  "mac" varchar(16) not null,
  "zone" integer not null,
  "subZone" integer not null,
  "unit" text not null,
  "pwm" integer not null,
  "led" integer not null,
  "network" integer not null,
  "lastContact" text,
  "ioConfiguration" integer not null,
  primary key ("mac")
)

CREATE TABLE "nodemeasure" (
  "id" integer primary key autoincrement,
  "mac" text not null,
  "type" integer not null,
  "date" text,
  "batchDate" text,
  "value" real not null
)

Typically the SQL query is :

SELECT
    rn.*,
    COUNT(nm.id) AS measuresCount
FROM rfnode rn
LEFT JOIN nodemeasure nm
    ON nm.mac = rn.mac
GROUP BY rn.mac

Now the problem is that I can't figure out how to query and get the result by using the Wt/DBO library.

First attempt

class RFNode
{
    public:
        std::string mac, unit;
        int zone = 0, subZone = 0, pwm = 0, led = 0, network = 0, ioConfiguration = 0, measuresCount = 0;
        Wt::WDateTime lastContact;

        RFNode()
        {
            mac             = "";
            zone            = 0;
            subZone         = 0;
            unit            = "";
            pwm             = 8;
            led             = 8;
            network         = 0;
            ioConfiguration = 0;
            measuresCount   = 0;
        }

        RFNode(std::string p_sMAC, int p_nZone, int p_nSubZone, std::string p_sUnit, int p_nPWM, int p_nLED, int p_nNetwork, Wt::WDateTime p_oLastContact, int p_nIOConfiguration, int p_nMeasuresCount = 0)
        {
            mac             = p_sMAC;
            zone            = p_nZone;
            subZone         = p_nSubZone;
            unit            = p_sUnit;
            pwm             = p_nPWM;
            led             = p_nLED;
            network         = p_nNetwork;
            lastContact     = p_oLastContact;
            ioConfiguration = p_nIOConfiguration;
            measuresCount   = p_nMeasuresCount;
        }

        template<class Action>
        void persist(Action& a)
        {
            dbo::id(a,      mac,            "mac",16);
            dbo::field(a,   zone,           "zone");
            dbo::field(a,   subZone,        "subZone");
            dbo::field(a,   unit,           "unit");
            dbo::field(a,   pwm,            "pwm");
            dbo::field(a,   led,            "led");
            dbo::field(a,   network,        "network");
            dbo::field(a,   lastContact,    "lastContact");
            dbo::field(a,   ioConfiguration,"ioConfiguration");
            dbo::field(a,   measuresCount,  "measuresCount");
        };
};

with the query :

Wt::Dbo::collection<Wt::Dbo::ptr<RFNode>> lTemp = m_oSession.query<Wt::Dbo::ptr<RFNode>>("SELECT rn.*, COUNT(nm.id) AS measuresCount FROM rfnode rn LEFT JOIN nodemeasure nm ON nm.mac = rn.mac").resultList();

is leads to Session::query(): too many aliases for result.

Second attempt

Same class RFNode used, except field measuresCount removed.

typedef Wt::Dbo::ptr_tuple<RFNode, int>::type RFNodeJoin;
        typedef Wt::Dbo::collection<RFNodeJoin> RFNodeJoinC;

        RFNodeJoinC lTemp = m_oSession.query<RFNodeJoin>("SELECT rn.*, COUNT(nm.id) AS measuresCount FROM rfnode rn LEFT JOIN nodemeasure nm ON nm.mac = rn.mac GROUP BY rn.mac").resultList();

        std::vector<RFNodeJoin> lTransition(lTemp.begin(),lTemp.end());

but it doesn't compile, resulting error : error: request for member ‘persist’ in ‘obj’, which is of non-class type ‘int’. I suppose I need to map one class per table, but since I want a count and no existing fields, I don't think a int class will do the job.

Answer 1

Finally by trial and error I found the solution...

First, the RFNode class doesn't have the field measuresCount anymore.

The request and iteration are made as follow :

Wt::Dbo::collection<boost::tuple<Wt::Dbo::ptr<RFNode>, int>> lTemp = m_oSession.query<boost::tuple<Wt::Dbo::ptr<RFNode>, int>>("SELECT rn, COUNT(nm.id) FROM rfnode rn LEFT JOIN nodemeasure nm ON nm.mac = rn.mac GROUP BY rn.mac").resultList();

for(Wt::Dbo::collection<boost::tuple<Wt::Dbo::ptr<RFNode>, int>>::const_iterator i = lTemp.begin();i != lTemp.end();++i)
{
    Wt::Dbo::ptr<RFNode> pRFNode;
    int nNodeMeasureCount;

    boost::tie(pRFNode,nNodeMeasureCount) = *i;

    lNodes.push_back(
            RFNode(
                    pRFNode->mac,
                    pRFNode->zone,
                    pRFNode->subZone,
                    pRFNode->unit,
                    pRFNode->pwm,
                    pRFNode->led,
                    pRFNode->network,
                    pRFNode->lastContact,
                    pRFNode->ioConfiguration,
                    nNodeMeasureCount
            )
    );
}

Hope it will help someone someday!