Hey there,

We tried to use your well crafted library and run into some problems. We use Diesel_ltree 0.2.5 and PostgresSql version 9.6, so binary protocol support isn 't there yet. After satisfying the compiler and try to insert some model into the database the Db gives us this error message: "ERROR: no binary output function available for type ltree".

Do you have any clue why this error is shown?

One solution could be to update the postgres version 13.x and use your library with binary protocol support for postgres, which seems to work perfectly fine, but unfortunately that will takes us some time to do.

If you can provide any fast and easy idea how to solve this we would appreciate it.

Best regards
XanVu

As best I can tell, these lines can just be replaced with the sql_function! macro like so:

sql_function!(lquery, lquery_t, (x: Text) -> Lquery);
sql_function!(ltxtquery, ltxtquery_t, (x: Text) -> Ltxtquery);

which would allow the macro to be removed entirely cutting roughly 50 lines of code.

I'm trying to implement query like this:

SELECT *
FROM my_tree mt
WHERE  mt."path" @> ANY
    (
        SELECT mt2."path"
        FROM my_tree mt2
        WHERE mt2."path" <@ 'root.archaea.thermoprotei' 
    )

(example is based on test scheme of ltree_diesel, please ignore it's meaningless by itself in this case)

The main thing is I need ability to pass a subquery into contains_any() operator for path, which is correct SQL, but I'm failing to do so with diesel.

Here is rust code which fails to compile:

    let mt2 = diesel::alias!(my_tree as mt2);
    let subquery = mt2
        .filter(
            mt2.field(my_tree::path)
                .contained_by(text2ltree("root.archaea.thermoprotei")),
        )
        .select(mt2.field(my_tree::path))
        .into_boxed();

    let results = my_tree::table
        .select((my_tree::id, ltree2text(my_tree::path)))
        .filter(my_tree::path.contains_any(subquery)) // <-- Here I need to add subquery
        .order(my_tree::id)
        .load::<MyTree>(&mut connection)
        .unwrap()
        .into_iter()
        .map(|t| t.path)
        .collect::<Vec<_>>();

Similar thing working fine in diesel with regular types, for example, if subquery selects from 'id', it's working correctly with something like .filter(my_tree::id.eq_any(subquery))

I've tried to dive into details of implementation to create pull request but it seems I'm too incompetent in how to make it right.

Hello,

I have a project which uses diesel 1.4.8 and diesel_tree 0.2.4, and I want to updated them to the latest versions. However, I'm blocked by the following compilation issue:

#[derive(Debug, Insertable)]
   |                 ^^^^^^^^^^ the trait `diesel::Expression` is not implemented for `diesel_ltree::Ltree`

Any idea what is wrong here, and if there is anything I can do to fix this?

Hey there,

we updated our database up to version 13 and tried to use the latest version of diesel_ltree (0.2.6).
We discovered that we couldn't insert objects with ltree into the database. We receivied an error from postgres stating:
"2022-05-23 15:34:19.036 UTC [256] ERROR: unsupported ltree version number 52".

After digging into the postgres code we found this function: ltree_recv().

{
     StringInfo  buf = (StringInfo) PG_GETARG_POINTER(0);
     int         version = pq_getmsgint(buf, 1);
     char       *str;
     int         nbytes;
     lquery     *res;
  
     if (version != 1)
         elog(ERROR, "unsupported lquery version number %d", version);
  
     str = pq_getmsgtext(buf, buf->len - buf->cursor, &nbytes);
     res = parse_lquery(str);
     pfree(str);
  
     PG_RETURN_POINTER(res);
 }
 

For further information: https://doxygen.postgresql.org/ltree__io_8c.html#a21da31d0896fe92cf987e97b703f18d8

That shows that you need to add a version before receiving the actual message.
So we added an version to your ToSql function:

impl diesel::serialize::ToSql<crate::sql_types::Ltree, Pg> for Ltree {
        fn to_sql<W: std::io::Write>(
            &self,
            out: &mut diesel::serialize::Output<W, Pg>,
        ) ->
 diesel::serialize::Result {
            let version_number: [u8;1] = [1];
            let msg = [&version_number, self.0.as_bytes()].concat();
            out.write_all(&msg)?;
            Ok(diesel::serialize::IsNull::No)
        }
        

That seems to work for us, but we are unsure if that is a stable solution. 
Can you confirm that this change doesnt break anything else and if it is stable create an updated version of it?

Best Regards
Xanvu

I can't find examples of that in the tests, or those words.

Pending something like diesel-rs/diesel#1264, we can add support for the operators that take arrays of ltrees/lqueries.

Hi there,

thank you very much for this well working library to support postgres ltree in diesel!
Judging from your recent commits, the binary protocol seems to be supported now for ltree?
If so, are you considering to bring a new version to cargo.io for it?

Thanks a lot!
Cap Fim

Currently blocked by libpq of all things:

resultFormat

Specify zero to obtain results in text format, or one to obtain results in binary format. (There is not currently a provision to obtain different result columns in different formats, although that is possible in the underlying protocol.)

Ideally we'd request text format just for ltree, since requesting binary produces the following from Postgres:

DatabaseError(__Unknown, "no binary output function available for type ltree")

As best I can tell, the majority of the operators in this crate are redundant with those defined by Diesel. It looks like all that's missing is a few impl SingleValue for Ltree {} etc

It doesn't look like this is currently causing anything to blow up, as you aren't using any features that could cause the oids to be actually used (pretty much only bind params which require a ToSql impl). That said, these impls are still very wrong. Types from extensions do not have stable OIDs, and you need to be using http://docs.diesel.rs/diesel/pg/struct.PgMetadataLookup.html#method.lookup_type to get the OIDs dynamically at runtime.

I have a table with an ltree column, but when I try to make it Insertable it tells me Insertable isn't implemented for Ltree.

the trait `diesel::Expression` is not implemented for `diesel_ltree::types::Ltree`

Looking at your tests I see you used a String instead of Ltree, but you didn't declare it Insertable since that would instead give this error:

^^^^^^^^^^ the trait `diesel::Expression` is not implemented for `std::string::String`
= note: required because of the requirements on the impl of `diesel::Expression` for `&'insert std::string::String`
= note: required because of the requirements on the impl of `diesel::expression::AsExpression<diesel_ltree::types::Ltree>` for `&'insert std::string::String`

So I'm not sure how I'm supposed to derive Insertable on a table with a column of Ltree, here's my code:

// ...
use diesel_ltree::Ltree;

#[derive(Identifiable, Queryable)]
#[table_name = "objects"]
pub struct Object {
    pub id: Uuid,
    pub path: Ltree,
    // other fields..
}

#[derive(Insertable)]
//       ^^^^^^^^^^ the trait `diesel::Expression` is not implemented for `diesel_ltree::types::Ltree`
#[table_name = "objects"]
pub struct NewObject {
    pub path: Ltree, // <-- it doesn't like this, Ltree isn't Insertable
    // other fields..
}

What am I doing wrong? Isn't this the way inserting is supposed to be?