Re: How to Handle ltree path Data Type

On Fri, Apr 5, 2013 at 11:51 PM, Don Parris <parrisdc(at)gmail(dot)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