Re: How to Handle ltree path Data Type

On Fri, Apr 5, 2013 at 7:47 PM, Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com
> wrote:

> 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
>

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. :-/

Thanks again!
Don
--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/
<https://www.xing.com/profile/Don_Parris><http://www.linkedin.com/in/dcparris>
GPG Key ID: F5E179BE