I am facing simple problem with searching entities by some (sub)string, which they might contain. E.g. I have users with u_name as "rags","mechrags","meragsch" and I will enter to search window "rags" and I used the following query for searching strings containing "rags"
SELECT u FROM User_personal u WHERE u_name LIKE'%rags%'
I want to order the results returned by the above query on the basis of first occurence of search string "rags" in the string so the result will be rags, meragsch, mechrags (in this order).
I am thinking of using the LOCATE function for the above result as follows
SELECT u FROM User_personal u WHERE u_name LIKE'%rags%' order by
LOCATE(u.u_name,'rags')
but the result is better than the previous one but if there are 4 users "ragsch","rags","meragsch","mechrags" the answer
should be "rags","ragsch","meragsch","mechrags" but the ouput is
"ragsch","rags","meragsch","mechrags" i.e database order
I am thinking to use subqueries where we order first on length and then the results if ordered on first occurence of "rags" the result may come but the query is not running
select user from User_personal user where exists (select u from User_personal u where u.u_name like'%rags%' order by LENGTH(u.u_name)) order by LOCATE(user.u_name,'rags')
AS HERE http://docs.oracle.com/html/E13946_04/ejb3_langref.html#ejb3_langref_subquerie
Can anyone suggest a method for getting the correct output?
SELECT u FROM User_personal u WHERE u_name LIKE'%rags%' ORDER BY LOCATE(u.u_name,'rags'), LENGTH(u.u_name)
gives correct output suggested BY OBJECT_DB
now I have a problem with using it in a method as below
public static List<User_personal> search(String str, EntityManager em)
{
String dup ="%"+str+"%";
Query q = ("SELECT u FROM User_personal u WHERE u_name LIKE :dup ORDER
BY LOCATE(u.u_name,'rags'), LENGTH(u.u_name)",User_personal.class)
List<User_personal> result = q.getResultList();
if (!result.isEmpty()) {
return result;
}
return null;}*
if I give str as "rags" I should get all "u" with the required conditions but it is showing an error as below
<h1>HTTP Status 500 - Attempt to execute a query with too few arguments
For checking I gave "rags" directly and got the required output but cant understand the problem in passing the argument.
public static List<User_personal> search(String str,EntityManager em)
{
//String dup ="%"+str+"%";
Query q = ("SELECT u FROM User_personal u WHERE u_name LIKE '%rags%'
ORDER BY LOCATE(u.u_name,'rags'), LENGTH(u.u_name)",User_personal.class)
List<User_personal> result = q.getResultList();
if (!result.isEmpty()) {
return result;
}
return null;}*
gives correct output.
CAN ANYONE SUGGEST CORRECT METHOD FOR USING VARIABLE HERE
got it!!!!!!
public static List<User_personal> search(String str, EntityManager em) {
Query q = em.createQuery("SELECT u FROM User_personal u WHERE
CONCAT(u.fname,u.lname) LIKE :name ORDER BY
LOCATE(CONCAT(u.fname,u.lname),'rags'),
LENGTH(CONCAT(u.fname,u.lname))",
User_personal.class);
q.setParameter("name", "%"+str+"%");
List<User_personal> result = q.getResultList();
if (!result.isEmpty()) {
return result;
}
return null;}
This gives all the "u" with "rags" and also in the order of length and first presence of "rags" too
so can we do it like non-case sensitive like "Rags" or "RAGS" OR "rags" should return same values??
DONE!!!
public static List<User_personal> search(String str, EntityManager em) {
Query q = em.createQuery("SELECT u FROM User_personal u WHERE
lower(CONCAT(u.fname,u.lname)) LIKE lower(:name) ORDER BY
LOCATE(CONCAT(u.fname,u.lname),'rags'),
LENGTH(CONCAT(u.fname,u.lname))",
User_personal.class);
//SELECT u FROM User u WHERE lower(u.username) LIKE :username
q.setParameter("name", "%"+str+"%");
List<User_personal> result = q.getResultList();
if (!result.isEmpty()) {
return result;
}
return null;}
If I give "Rags" it finds "rags" and "Rags" and "RAGS" and thats what I need:)
Any better method is thankfully accepted try for the better methods THANK YOU ALL
