I would like to append the result of a left join to an entity field. In SQL it would be this query:
SELECT * FROM thethreads t
LEFT JOIN thread_votes tv
ON t.idthread=tv.thread
AND tv.from_user like "test2";
Then I'd have a column in my Thethread entity I can populate with the left join information. I don't know how to do that.
The rest of the information is to illustrate what I want to do :
At the moment I've those two entities called "Thethread" and "ThreadVote". Each "TheThread" has a bunch of "ThreadVote" (which correspond to up votes or down votes from users, an user can have only one thread vote by thread (-1 or +1, like on StackOverflow). Anyway, I want to know if an user has already voted so I can paint the up vote or down vote arrow (like stackoverflow again). What I'm doing at the moment is inefficient: I'm getting a TheThread List and then I'm checking within that list if a vote by the current user exists. I would like to have everything done within the EJB where I make the JPA queries. I would like something like this :
private static final String SELECT_NEWEST_THREADS = "Select t From Thethread t LEFT JOIN ThreadVote tv ON t.idthread=tv.thread AND tv.user1 like :currentUser ORDER BY t.datePosted";
@Override
public List<Thethread> giveNewestThread(int amount, int page,
String currentUser) {
Query query = em.createQuery(SELECT_NEWEST_THREADS);
query.setParameter("currentUser", currentUser);
query.setMaxResults(amount);
List<Object[]> temp = query.getResultList();
List<Thethread> threadList = new ArrayList<Thethread>();
for (Object[] o : temp) {
Thethread thread = (Thethread) o[0];
thread.setThreadcurrentUserVote((ThreadVote) o[1]);
threadList.add(thread);
}
return threadList;
}
This gives me an Exception :
Exception Description: Object comparisons can only be used with OneToOneMappings. Other mapping comparisons must be done through query keys or direct attribute level comparisons.
Mapping: [org.eclipse.persistence.mappings.DirectToFieldMapping[idthread-->thethreads.IDTHREAD]]
with the entities :
@Entity
@Table(name = "thethreads")
@NamedQuery(name = "Thethread.findAll", query = "SELECT t FROM Thethread t")
public class Thethread implements Serializable {
private static final long serialVersionUID = 1L;
@Id
private int idthread;
private String content;
private int downvotes;
private int upvotes;
// bi-directional many-to-one association to User
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "posted_by")
private User user;
// bi-directional many-to-one association to ThreadVote
@OneToMany(mappedBy = "thethread")
private List<ThreadVote> threadVotes;
@Transient
privateThreadVote currentUserVote;
//...
}
ThreadVote entity:
@Entity
@Table(name = "thread_votes")
@NamedQuery(name = "ThreadVote.findAll", query = "SELECT t FROM ThreadVote t")
public class ThreadVote implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name = "id_votes_thread")
private int idVotesThread;
private int vote;
// bi-directional many-to-one association to Thethread
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "thread")
private Thethread thethread;
// bi-directional many-to-one association to User
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "from_user")
private User user1;
// bi-directional many-to-one association to User
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "to_user")
private User user2;
//...
}
I other words I would like to remove this ForumThread class and have everything queried and get the result in the entity Thethread.
via
Chebli Mohamed
