Mysql Query With Like Operator and order by keyword giving empty records in spring boot api

Question

native Query I Wrote in My Repository

If i remove order by condition in the below query , it is giving some records but i want it in some order so i added order by condition then it not showing records only . but the same mysql query with order by condition is giving some records in mysql workbench.

  @Query(value = "SELECT l.id AS id,l.first_name AS firstName,l.last_name AS lastName,"
  + "    l.email AS email,l.phone AS phone,l.place AS place,l.course_id AS courseId,"
  + "    c.name AS courseName,l.source AS source,l.enquiry_for AS enquiryFor,"
  + "    l.appointment_date AS appointmentDate,l.description AS description,"
  + "    l.discount AS discount,l.status AS status,l.assignee_id AS assigneeId,"
  + "    ase.first_name AS assigneeFirstName,ase.last_name AS assigneeLastName,"
  + "    l.assignor_id AS assignorId,asr.first_name AS assignorFirstName,"
  + "    asr.last_name AS assignorLastName,l.active AS active,l.created_date AS createdDate,"
  + "    l.updated_date AS updatedDate,(SELECT comments   FROM reviews where  created_date IN"
  + "    (SELECT MAX(created_date) from reviews where lead_id=l.id)) AS latestComment FROM "
  + "    leads l JOIN users AS ase ON l.assignee_id = ase.id JOIN users AS asr ON "
  + "   l.assignor_id = asr.id JOIN courses AS c ON l.course_id =c.id WHERE l.status!='Draft'"
  + "    AND (l.first_name LIKE '%:keyword%' OR l.last_name LIKE '%:keyword%' OR l.phone LIKE"
  + "   '%:keyword%') ORDER BY -l.appointment_date DESC,l.created_date ASC", nativeQuery=true)
  List<Leads> searchLeadsForAdmin(@Param("keyword") String searchKeyword);

i don't know Where am i going wrong , attached image below mysql query result came in workbench.

Answer 1

I found the answer to my question. It was a concatenation issue. In the second last line of my query after 'l.phone LIKE' I added the value in the next line.

+ "    AND l.first_name LIKE %:keyword% OR l.last_name LIKE %:keyword% OR  "
+ "    l.phone LIKE %:keyword%  ORDER BY -l.appointment_date DESC,"
+ "    l.created_date ASC", nativeQuery = true)

The above query works.