django raw query percent sign problem

Question

I trying to Django raw sql query in like function, but results is empty. I try mysql client tool this query and get many records. how to solution this problem?

my query:

  SELECT s.* , s.id as pk 
    FROM d_status as s, 
         (select post_id 
            from p_useractions 
           where from_user_id in 
                 (select to_user_id 
                    from p_fallowers 
                   where from_user_id = 1)
         ) as a 
   WHERE s.from_user_id = 1 OR 
         s.text like '%@Mustafa Yontar2123%' OR 
         s.id = a.post_id 
GROUP BY s.id 
ORDER BY s.last_update 
   LIMIT 25

Answer 1

Try replacing each % with %%. So, the relevant part in your example would look like this: '%%@Mustafa Yontar2123%%'.

Answer 2

Quoting from the documentation for MySQL:

With LIKE you can use the following two wildcard characters in the pattern.

    Character  Description
    %          Matches any number of characters, even zero characters
    _          Matches exactly one character

To test for literal instances of a wildcard character, precede it by the 
escape character. If you do not specify the ESCAPE character, \ is assumed.

So your pattern needs to be '\%@Mustafa Yontar2123\%'. The Python code for the query will look like this:

query = r"""SELECT s.* , s.id as pk FROM d_status as s, 
                   (SELECT post_id FROM p_useractions WHERE from_user_id in
                     (SELECT to_user_id FROM p_fallowers WHERE from_user_id = 1)) as a
            WHERE s.from_user_id = 1 or
                  s.text like '\%@Mustafa Yontar2123\%' or
                  s.id = a.post_id
            GROUP BY s.id
            ORDER BY s.last_update
            LIMIT 25"""

The documentation for PostgreSQL and SQLite mention the same thing.