1992

What would be the best way to implement to check if the Post has been liked by the User using JPA an

Question:

Ok, it can sound stupid but I'm newbie at MySQL so I really have no idea if it's a case for Joins or something else.

So I have 3 tables, POST, USER, LIKES. Basically they have:

POST post_id | caption | media | ... USER user_id | name | email | ... LIKES like_id | post_id | user_id | ...

And the Objects in Java are:

@Entity @Table(name="POST") public class PostMW{ @Id @Column(name="id", nullable=false) @GeneratedValue(strategy=GenerationType.AUTO) private Long id; @OneToOne(cascade = CascadeType.ALL) @JoinColumn(name="media_id", nullable=false, unique=true) private MediaMW media; .... @Transient private Boolean liked; }

And LIKES:

@Entity @Table(name="LIKES") public class LikeMW { @Id @Column(name="id", nullable=false) @GeneratedValue(strategy=GenerationType.AUTO) private Long id; @ManyToOne(cascade = CascadeType.ALL) @JoinColumn(name = "post_id", nullable=false) @JsonBackReference private PostMW post; @ManyToOne(cascade = CascadeType.ALL) @JoinColumn(name="user_id", nullable=false) private UserMW user; }

An USER can request a list of POST so I need to set in the property "liked" for EACH POST if that USER has liked it.

So far I did:

public class FeedServiceImpl implements FeedService{ private PostDao<PostMW, Long> postDao; ... @Override public List<PostMW> getFeed(int page, UserMW user) { List<PostMW> result; int offset = page * ConstUtil.FEED_POSTS_PER_PAGE; postDao.openCurrentSession(); result = postDao.list(offset, ConstUtil.FEED_POSTS_PER_PAGE); for(PostMW post : result){ //TODO //post.setLiked(postDao.postLikedByUser(post, user)); //PS // FOR EVERY POST I'M EXECUTING THE QUERY BELOW //@NamedQuery(name="isLiked", query="SELECT COUNT(likes.id) FROM //LikeMW likes WHERE likes.post.id = :postID AND likes.user.id = :userID"), } postDao.closeCurrentSession(); return result; } ... }

I don't even know if it works cause I really don't like the idea to retrieve for example 100 posts and inside a loop, make 100 requests in a row just to check if the user liked that post or not so I didn't even try.

So, what should I do? Any suggestion would be very appreciate!!

Thanks!

<strong>EDIT:</strong>

Trying this query at moment:

List<Object> result = entityManager .createQuery("SELECT post FROM PostMW post LEFT OUTER JOIN post.hasLiked liked ON post.id = liked.post_id AND liked.user_id =:userID") .setParameter("userID", 1) .setFirstResult(offset).setMaxResults(itemsPerPage) .getResultList();

Updated my objects. Now my <strong>PostMW</strong> object has

@Entity @Table(name="POST") public class PostMW{ ... @ManyToMany(cascade = CascadeType.ALL) @JoinTable(name="LIKES", joinColumns=@JoinColumn(name="post_id"), inverseJoinColumns=@JoinColumn(name="user_id")) private List<UserMW> hasLiked; ... }

And <strong>UserMW</strong> has

@Entity @Table(name="USER") public class UserMW { ... @Id @Column(name="id", nullable=false) @GeneratedValue(strategy=GenerationType.AUTO) private Long id; @Column(name="name", nullable=false, length=255) private String name; ... }

Getting this exception:

java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Internal problem encountered while compiling [SELECT post FROM PostMW post LEFT OUTER JOIN LikeMW likes ON post.id = likes.post_id AND likes.user_id =:userID]. Internal Exception: java.lang.NullPointerException at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1605) at com.jonathan.myworld.model.dao.impl.PostDaoImpl.list(PostDaoImpl.java:72) at com.jonathan.myworld.service.impl.FeedServiceImpl.getFeed(FeedServiceImpl.java:45) at com.jonathan.myworld.UserWS.login(UserWS.java:151) at com.jonathan.myworld.UserWS.loginWithGoogle(UserWS.java:60) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at com.google.appengine.tools.development.agent.runtime.Runtime.invoke(Runtime.java:130) at com.google.api.server.spi.SystemService.invokeServiceMethod(SystemService.java:363) at com.google.api.server.spi.SystemServiceServlet.execute(SystemServiceServlet.java:113) at com.google.api.server.spi.SystemServiceServlet.doPost(SystemServiceServlet.java:71) at javax.servlet.http.HttpServlet.service(HttpServlet.java:637) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166) at com.google.appengine.api.socket.dev.DevSocketFilter.doFilter(DevSocketFilter.java:74) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at com.google.appengine.tools.development.ResponseRewriterFilter.doFilter(ResponseRewriterFilter.java:128) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at com.google.appengine.tools.development.HeaderVerificationFilter.doFilter(HeaderVerificationFilter.java:34) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at com.google.appengine.api.blobstore.dev.ServeBlobFilter.doFilter(ServeBlobFilter.java:63) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at com.google.apphosting.utils.servlet.TransactionCleanupFilter.doFilter(TransactionCleanupFilter.java:50) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at com.google.appengine.tools.development.StaticFileFilter.doFilter(StaticFileFilter.java:125) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at com.google.appengine.tools.development.DevAppServerModulesFilter.doDirectRequest(DevAppServerModulesFilter.java:366) at com.google.appengine.tools.development.DevAppServerModulesFilter.doDirectModuleRequest(DevAppServerModulesFilter.java:349) at com.google.appengine.tools.development.DevAppServerModulesFilter.doFilter(DevAppServerModulesFilter.java:116) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388) at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216) at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182) at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765) at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418) at com.google.appengine.tools.development.DevAppEngineWebAppContext.handle(DevAppEngineWebAppContext.java:98) at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152) at com.google.appengine.tools.development.JettyContainerService$ApiProxyHandler.handle(JettyContainerService.java:511) at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152) at org.mortbay.jetty.Server.handle(Server.java:326) at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542) at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:938) at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:755) at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218) at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404) at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409) at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582) Caused by: Exception [EclipseLink-0] (Eclipse Persistence Services - 2.5.1.v20130918-f2b9fc5): org.eclipse.persistence.exceptions.JPQLException Exception Description: Internal problem encountered while compiling [SELECT post FROM PostMW post LEFT OUTER JOIN LikeMW likes ON post.id = likes.post_id AND likes.user_id =:userID]. Internal Exception: java.lang.NullPointerException at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildUnexpectedException(HermesParser.java:207) at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:296) at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildQuery(HermesParser.java:163) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:142) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:116) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:102) at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:86) at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1603) ... 49 more Caused by: java.lang.NullPointerException at org.eclipse.persistence.queries.DatabaseQuery.addArgument(DatabaseQuery.java:449) at org.eclipse.persistence.queries.DatabaseQuery.addArgument(DatabaseQuery.java:419) at org.eclipse.persistence.internal.jpa.jpql.HermesParser.addArguments(HermesParser.java:98) at org.eclipse.persistence.internal.jpa.jpql.HermesParser.populateQueryImp(HermesParser.java:287)

Answer1:

you could try to extract the liked posts from the list of posts you've just got before. with a "SELECT ... IN" request) and then fill the boolean isLiked; something like :

... @Override public List<PostMW> getFeed(int page, UserMW user) { List<PostMW> result; int offset = page * ConstUtil.FEED_POSTS_PER_PAGE; postDao.openCurrentSession(); result = postDao.list(offset, ConstUtil.FEED_POSTS_PER_PAGE); List<Long> postIds = new ArrayList(); for(PostMW post : result){ postIds.add(post.getid(); } //then EXtract all liked post from list : Query queryIn = em.createQuery("SELECT p.post.id FROM LikeMW p WHERE p.user = :user AND p.post IN :values"); queryIn.setParameter("values", postIds ); queryIn.setParameter("user", currentUser); List<Object[]> resultIn = queryIn.getResultList(); Set<Long> postIdLiked ) new HashSet<>(); for(Object[] row: resultIn) { postIdLiked.add(row[0]); } //then fill the boolean isLiked for(PostMW post : result){ if (postIdLiked.contains(post.getId()) post.setLiked(true); } postDao.closeCurrentSession(); return result; } ...

Recommend

  • $.when().then() not working with nested ajax calls
  • Zend DB Select with multiple table joins
  • dilemma on the use of inheritance in EF code-first
  • Performance of small sets in Python
  • How to write files in C:\\Windows\\System32 with full permissions
  • django - can't see the uploaded media files from apache
  • UILabel Refresh
  • How to add header line to CSV without loading whole file?
  • Why am I getting a “package could not be registered” deployment error on Windows 10 UWP?
  • Read and Write DOCX file
  • Source file (.py) not compiling (.pyc not updated)
  • How to hide header on specific page in wordpress
  • angularJS library for drawing bar charts with logarithmic scale
  • how to handle large size of update query in mysql with laravel
  • iOS, How to check whether the user likes a facebook page or not?
  • Arrange forum posts by time of latest reply
  • How to show a specific VC , more like navigate to a stack of VC
  • JsonMappingException: Can not deserialize instance of java.lang.Integer out of START_OBJECT token
  • Adding independent aspx/asmx pages into DotNetNuke
  • How to concat Pandas dataframe columns
  • how to get username into sql trigger when multiple users signed on from asp membership
  • NHibernate manually control fetching
  • how do i write assembly code from c#?
  • Using Sax parsing to edit and write XML in VB6
  • Unable to get column index with table.getColumn method using custom table Model
  • Django: Count of Group Elements
  • Can I display google adwords (AdView) in javafx on android
  • How to recover from a Spring Social ExpiredAuthorizationException
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • ILMerge & Keep Assembly Name
  • Can I make an Android app that runs a web view in Chrome 39?
  • Large data - storage and query
  • WOWZA + RTMP + HTML5 Playback?
  • How do I rollback to a specific git commit
  • EntityFramework adding new object to nested object collection
  • Django query for large number of relationships
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • How can I use `wmic` in a Windows PE script?
  • How to push additional view controllers onto NavigationController but keep the TabBar?