> 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
Thanks Daniele,
Yes, your suggestion works much better. My query above found the first record in the table, along with its children, but no other records after that. In other words, it found the first row, or first few rows - and only those rows. However, you are correct that I really would like to be able to search for a given word, regardless of its location in the path and see the full path in the result set - along with other rows that share that part of the path label.
While I do understand a fair chunk (most?) of the ltree documentation, I was missing the need for this element: {'query': '*.%s.*' % search_term}.
I'm not sure I would have figured that out any time soon. :-/