I'm looking to display users friends who haven't preformed certain actions. E.g borrowed a book, bought a book.
When a user buys/reserves to the book a row is recorded, So i need a hql query where it finds the friends of a user that excludes friends that have bought/reserved a book
There are no associations with these tables.
Code:
class UserFriend
User user
User friend
int status // "accepted, pending, requested"
Code:
class BooksBought
Book book
User user
int status // "reserved, bought"
The following two attempts don't really work, duplicates are picked up after entries are recorded in the BooksBought table.
Code:
UserFriend.exectueQuery("SELECT uf.friend FROM UserFriend uf, BooksBought bb WHERE uf.user =:user AND uf.status =:accepted AND bb.book =:book AND bb.user != uf.friend", [accepted:1, book:book, user:user])
user2_.data...............
from
user_friend userfriend0_
inner join
user user2_
on userfriend0_.friend_id=user2_.id,
books_bought books_bought1_
where
userfriend0_.user_id=?
and userfriend0_.status=?
books_bought1_.book_id=?
and eventrsvp1_.user_id<>userfriend0_.friend_id
Using "not exists" with the resulting sql,
Code:
UserFriend.exectueQuery("SELECT uf.friend FROM UserFriend uf, BooksBought bb WHERE uf.user =:user AND uf.status =:accepted AND not exists (from bb where bb.book =:book AND bb.user = uf.friend)", [accepted:1, book:book, user:user])
user2_.data...............
from
user_friend userfriend0_
inner join
user user2_
on userfriend0_.friend_id=user2_.id,
books_bought books_bought1_
where
userfriend0_.user_id=?
and userfriend0_.status=?
and not (exists (select
books_bought1_.id
from
books_bought books_bought1_
where
books_bought1_.book_id=?
and books_bought1_.user_id=userfriend0_.friend_id))
They don't work and just stop working when entries are recorded, any ideas?
Login
FAQ
Search