Hello,

yes, this is possible.

But first, I would like to imrove your SQL query to make it more efficient.
SELECT Doc.id, Doc.name, Doc.status FROM User *LEFT JOIN* Doc ON User.id =
Doc.user_id WHERE User.name LIKE "%ar%";
Using comma-join (cross-join) may result in all-to-all coupling before
applying join condition.

I removed User.name from SELECT because you need only list of docs, not
user (see below if you need both).

To implement this in Wt:

typedef dbo:ptr<Doc> DocPtr;
typedef DocPtr Result;
typedef dbo::Query<Result> Q;
Q query = session.query<Result>("SELECT *D* FROM User U LEFT JOIN Doc D ON
U.id = D.user_id");
query.where("U.name LIKE '%ar%'"); // you can use ? and .bind method as
usually

Note that "SELECT D" is expanded to "SELECT D.id, D.version, D.name,
D.status" by Wt. Very useful feature to write less. Query object can be
converted into dbo collection.

If you want get pairs "Doc - User":

typedef dbo:ptr<Doc> DocPtr;
typedef dbo:ptr<User>UserPtr;
typedef *boost::tuple<DocPtr, UserPtr> Result*;
typedef dbo::Query<Result> Q;
Q query = session.query<Result>("SELECT D*, U* FROM User U LEFT JOIN Doc D
ON U.id = D.user_id");
query.where("U.name LIKE '%ar%'");

Difference in Result type, now it is boost::tuple of all types selected.
You can use WString, int and other types too:
typedef boost::tuple<DocPtr, WString> Result;
Q query = session.query<Result>("SELECT D, *U.name* FROM User U LEFT JOIN
Doc D ON U.id = D.user_id");

Regards,
Boris Nagaev


On Mon, Mar 10, 2014 at 1:25 PM, Muhammad Nasser Al-Noimi <[email protected]>
wrote:
> Hi,
>
> Suppose I have two tables related in one-to-many relation:
>
> User: (Master table)
> - id
> - name (string)
> - docs (collection)
>
> Doc: (Details)
> - id
> - name (string)
> - status (string)
> - user (pointer to User)
>
> I want to get all docs for users that have name contains "ar" for example.
> SQL must be something like this:
>
> SELECT name, status, User.name FROM Doc, User WHERE User.id = Doc.user_id
> AND User.name LIKE "%ar%"
>
> is there any way to get the result of this query?
>
> Thanks
>
>
------------------------------------------------------------------------------
> Learn Graph Databases - Download FREE O'Reilly Book
> "Graph Databases" is the definitive new guide to graph databases and their
> applications. Written by three acclaimed leaders in the field,
> this first edition is now available. Download your free book today!
> http://p.sf.net/sfu/13534_NeoTech
> _______________________________________________
> witty-interest mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/witty-interest
>
------------------------------------------------------------------------------
Learn Graph Databases - Download FREE O'Reilly Book
"Graph Databases" is the definitive new guide to graph databases and their
applications. Written by three acclaimed leaders in the field,
this first edition is now available. Download your free book today!
http://p.sf.net/sfu/13534_NeoTech
_______________________________________________
witty-interest mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/witty-interest

Reply via email to