Re: How to Handle ltree path Data Type

On Fri, Apr 5, 2013 at 11:51 PM, Don Parris <parrisdc@gmail.com> wrote:

> I realized that when I looked at the documentation, and changed it.  As I
> mentioned, I did get the search partially working.  However, I now wonder if
> I am posing my user-generated query in the wrong way:
>
> search_term = input('Search for Category: ')  # get input from user
> cur = con.cursor()
> cur.execute("""SELECT * FROM category WHERE path ~ %(term)s;""",  # Run
> select query against the user's search term
>     {'term': search_term})
>
> If I run the above query, using the very first item in the category table as
> a search term, I will get a result.  If I use any other term below that, I
> get no result at all.  This closely mirrors my search queries against other
> tables, but apparently does not work quite the same in this case.  This is
> why I thought maybe it had to do with the ltree data type.

You are probably confusing text ~ text (regexp espression) with ltree
~ lquery, which are indeed two different operators. If you want to
search the ltrees with the label 'term' in any position you must match
the lquery '*.term.*'. Try:

    cur.execute("""SELECT * FROM category WHERE path ~ %(query)s;""",
        {'query': '*.%s.*' % search_term})

-- Daniele