find_or_create in OrientDB?

[Artem Shoobovych]

Hi there!

Lately, I've been playing with OrientDB of 1.7.10 and now 2.0.1. And currently I am stuck with poor SQL syntax or my misunderstanding of it.

What I want is to create an instance of a class if it does not exist. I tried different solutions, like `ifnull`, `first` and `if`. But it seems to work only on existing values.

For example, let us have a class `City` with only one instance - `{"name": "Lisboa"}`. 

These do not work:

    select if(count(select City where name = "Krakow") < 1, null, (insert into City set name = "Krakow" return @rid))


    select ifnull((select @rid from City where name = "Krakow"), (insert into City set name = "Krakow" return @rid))


    select first((select @rid from City where name = "Krakow"), (insert into City set name = "Krakow"))

I believe I need some kind of `exists` operator:

    select if( exists(select from City where name = "Krakow"), (select @rid from City where name = "Krakow"), (insert into City set name = "Krakow"))

Are there any possibility for this except stored functions?

Thanks!

[Luigi Dell'Aquila]

Hi Artem,

have you tried with UPSERT?

Here you can find all the references:

http://www.orientechnologies.com/docs/last/orientdb.wiki/SQL-Update.html

Regards

Luigi

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[pumplerod]

Is there example usage of this?  When I try and use upsert all I get is an error.

I have a class Locale which I only want to add if the specified locale does not already exist...

upsert into Locale set type='state' name='TX' where type='state' name='TX'

The error states that there is no command executor for command request: sql.upsert

[Luigi Dell'Aquila]

Hi 

Upsert is just a keyword in the UPDATE statement, eg.

update V set id = '12', name = 'foo' upsert where id = '12'

you can find all the docs here http://www.orientechnologies.com/docs/last/SQL-Update.html

Luigi

[Patrick Hoeffel]

Luigi,

Good answer for determining if a document exists, but how determine is a class exists (using SQL)?

Thanks,

Patrick

[Luigi Dell'Aquila]

Hi Patrick,

this is the query that gives you the list of existing classes

select expand(classes) from metadata:schema

I hope it helps

Luigi

[Patrick Hoeffel]

Luigi,

That absolutely answers my question, yes! Thanks very much!

Patrick