2

I have a problem in PHP when I use ltree from PostgreSQL. I do this in SQL:

SELECT * FROM tabla t
WHERE t.parent_path <@ (
    select array_agg(t1.parent_path)
    from tabla t1
    where t1.id in (1000035, 1000045, 1000055, 1000065)
)

I run the query in phpmyadmin and it works fine, but when I copy and paste, in PHP it does not work. I did a little research and I found that in PHP I need to replace the "<@" and "@>" for "OPERATOR(public.<)" and "OPERATOR(public.>)" respectively.

I test it in php with simple ltree query like:

$sql = "SELECT * FROM tabla t
        WHERE t.parent_path OPERATOR(public.<) (
            select t1.parent_path
            from tabla t1
            where t1.id in = 1000035
        )";
$conn = Zend_Registry::get('conexion');
$respdata = $conn->execute($sql);
return $respdata->fetchAll(PDO::FETCH_ASSOC);

and it works fine, but the thing is that in the real query that I want to execute I have in the left side an ltree an in the other side an ltree[], and when I use "OPERATOR(public.<)" it gives me this error:

"Undefined function: 7 ERROR: operator does not exist: public.ltree public.< public.ltree[]"

This is what I´m trying to do...

$sql = "SELECT * FROM tabla t
        WHERE t.parent_path OPERATOR(public.<) (
            select array_agg(t1.parent_path)
            from tabla t1
               WHERE t1.id IN (1000035, 1000045, 1000055, 1000065)
        )";
$conn = Zend_Registry::get('conexion');
$respdata = $conn->execute($sql);
return $respdata->fetchAll(PDO::FETCH_ASSOC);

Any advise.... tks. Sorry if my English is not good.

Abderrahim Soubai-Elidrisi
  • 5,025
  • 1
  • 26
  • 36
Adrián Fernández Martínez
  • 94
  • 7
  • Did you write the right syntax in you question for `$sql`? – Rohit Rasela Dec 13 '18 at 14:12
  • Ups sorry, i put an extra quotation marks after OPERATOR(public.<), that should **not** been there. result of a copy and paste. I edited olready. – Adrián Fernández Martínez Dec 13 '18 at 14:37
  • 2
    I'm not sure why you'd want to replace `<@` with `<`, since those are different operators (`<@` generally means "contains"; `<` generally means "less than"). Nor do I have any idea why running a query from PHP would require you to make that change. What happens if you run the original query? And can you link to or include more details of the advice to change over to this different operator? – IMSoP Dec 13 '18 at 15:07
  • Well, actually your question give´s me the rigth answer. I was wrong about change the "<@" for OPERATOR(public.<), when i put it rigth it works perfectly. The rigth way is to put instead OPERATOR(public.<) put the rigth operator that will be OPERATOR(public.<@) shust like IMSoP said – Adrián Fernández Martínez Dec 13 '18 at 15:17

1 Answers1

0

Well, thaks to IMSoP i found the answer.

Change the 

OPERATOR(public.<)

for 

OPERATOR(public.<@)

Tks a lot.

Adrián Fernández Martínez
  • 94
  • 7