need help with ManyToMany query
Added by Cassie Nicol over 11 years ago
I want to do something like
select m.user_id as "user", t.user_id as "target", m.timestamp, m.text
from message as m left join message_user as t on m.id=t.message_id
where room_id=? and ((t.user_id is null) or (t.user_id = ?));
bind(room).bind(user)
Message and User are normal table. message_user is an automatically generated table using a ManyToMany relationship between Message and User. message_user contains the target of the message in the case of directed messages.
but I cannot figure out how to make it work with the Wt::Dbo queries.
I cannot use boost::tuple to build the return because message_user is not a real C type.
typedef boost::tuple<Message, message_user> MessageJoinTargets;
string sql = "select m, t from message m join message t";
MessageJoinTargets response = session.query< MessageJoinTargets >(sql).where("m.room=?").bind(room);
any advice would be helpful.
I have truncated classes listed below.
class User
{
public:
std::string name;
Wt::Dbo::collection< Wt::Dbo::ptr > messages_from;
Wt::Dbo::collection< Wt::Dbo::ptr > messages_to;
template
void persist(Action& a)
{
Wt::Dbo::field(a, name, "name");
Wt::Dbo::hasMany(a, messages_from, Wt::Dbo::ManyToOne, "user");
dbo::hasMany(a, messages_to, dbo::ManyToMany, "message_user");
}
};
class Message {
public:
dbo::ptr room;
dbo::ptr user;
Wt::WDateTime timestamp;
std::string text;
dbo::collection< dbo::ptr > targets;
template
void persist(Action& a)
{
dbo::belongsTo(a, room, "room");
dbo::belongsTo(a, user, "user");
dbo::field(a, timestamp, "timestamp");
dbo::field(a, text, "text");
dbo::hasMany(a, targets, dbo::ManyToMany, "message_user");
}
};
Replies (2)
RE: need help with ManyToMany query - Added by Koen Deforche over 11 years ago
Hey,
The 'result type' of the query is determined only by what is in your select clause:
select m.user_id as "user", t.user_id as "target", m.timestamp, m.text
from message as m left join message_user as t on m.id=t.message_id
where room_id=? and ((t.user_id is null) or (t.user_id = ?));
You thus need 4 fields (m.user_id, t.user_id, m.timestamp, m.text), and the most convenient way to do that is to use a tuple containing the four appropriate types.
Regards,
koen
RE: need help with ManyToMany query - Added by Cassie Nicol over 11 years ago
Thanks Koen,
That seems to fix the problem.
typedef dbo::dbo_traits::IdType TUserId;
//typedef dbo::dbo_traits::IdType TDateTime;
//typedef boost::tuple<TUserId, TUserId, TDateTime, string> ResultTuple;
typedef boost::tuple<TUserId, TUserId, string, string> ResultTuple;
typedef dbo::collection ResultTupleCollection;
typedef vector ResultTupleVector;
string sql = "select"
\" m.user_id, t.user_id, m.timestamp, m.text\"
\" from message m left join message_user t on (m.id=t.message_id)\";
string where = "(m.room_id=?) and ((t.user_id is null) or (t.user_id = ?))";
ResultTupleCollection result = session.query(sql).where(where).bind(room).bind(user);
for(ResultTupleCollection::const_iterator i=result.begin(); i != result.end(); ++i) {
cout << (*i).get<0>()
<< \" \" << (*i).get<1>()
<< \" \" << (*i).get<2>()
<< \" \" << (*i).get<3>()
<< endl;
}
Of note, the IdType of WDateTime was a long, when what actually is in the database was a string, so I had to use string directly. I realize this is because of needing to defer the implementation of the DateTime storage in the database to the backend code.
I also after I figured this all out, realized that I never actually needed the value in the message_user table. It was only used for the where clause. That simplified the code even more since I could just return a collection of messages.
string sql = "select m from message m left join message_user t on (m.id=t.message_id)";
string where = "(m.room_id=?) and ((t.user_id is null) or (t.user_id = ?))";
Messages messages = session.query(sql).where(where).bind(room).bind(user);
for(Messages::const_iterator i=messages.begin(); i != messages.end(); ++i) {
cout << (*i).id()
<< \" \" << (*i)room>name
<< \" \" << (*i)user>name
<< \" \" << (*i)->timestamp.toString()
<< \" \" << (*i)->text
<< endl;
}
I was wondering if there was a way to use generated tables like "message_user" as a C datatype or if using tuple is the only way to get data from them?
thanks for your help!
/cassie