Help with nested query
Added by Sean Lynch over 13 years ago
I have the following sql statement:
select id
from (select * from entry where user_id = <currently logged in user user id>)
where not exists (select * from entry_tags where entry_tags.entry_id = id)
where user_id needs to be bound to the currently logged in user at runtime.
There is a many to many relationship between entry and tag and entry_tags is the association table.
The statement returns all the entries for the current user that don't have any tags.
I'm confused how to implement this query in Wt::Dbo. Specifically how do I use where() and bind() in a nested query like this.
Replies (3)
RE: Help with nested query - Added by Koen Deforche over 13 years ago
Hey,
The where(), orderBy(), etc... are purely convenience methods. The bind() method will simply bind values to placeholders in the order they are found in the query. The following will work:
session.query<long long>("select id from (select * from entry where user_id = ?) where not exists (select * from entry_tags where entry_tags.entry_id = id)").bind(userId);
Regards,
koen
RE: Help with nested query - Added by Sean Lynch over 13 years ago
Ahh perfect. That's nice. I guess I didn't really understand that from the documentation.
RE: Help with nested query - Added by Koen Deforche over 13 years ago
Hey,
I see how it can be confusing, I'm improving the documentation to indicate that there is remove the false sense of "magic" here.
Regards,
koen