Helper for queries on Version object fields

[JacobEvelyn]

Would there be interest in adding a method to the concern that allows queries to be made more safely/easily on the object serialized field?

I'm thinking something like:

VersionClass.where_object(name: "Foo", other_field: 42)

which translates into:

VersionClass.where("object LIKE '%name: Foo\n%'").
  where("object LIKE '%other_field: 42\n%'")

I end up making these queries fairly frequently, and I find them easy to mess up (for instance, forgetting the \n or %s) and I think a method to automate this would be helpful. I'm more than happy to submit a pull request for this change if it's something others would want as well.

[batter]

I like this idea. I'm sure if you have a lot of versions in your table, this search is probably pretty slow, but still a nice feature to have. A PR would be be great, cheers.

[bradleypriest]

If you do, can you try and make it storage specific. I'm using the PG json type so the query would actually be different than what you have above.

[JacobEvelyn]

Yep, definitely.

[batter]

Good call on that @bradleypriest. There is a class method on the PaperTrail::Version class that can tell you whether the object column is of type json or not.

[JacobEvelyn]

Awesome, thanks guys. I'll try to get this out by the end of the week.

[JacobEvelyn]

So just to clarify, my understanding is that this should have the ability to perform a string (LIKE) query on Yaml data, a LIKE query on JSON, and native Postgres JSON queries?

[batter]

Yea I think that sounds about right. You can probably first do a check to see if it's using the Postgres JSON, then if it's not, check the serializer in usage by calling PaperTrail.serializer.

[JacobEvelyn]

@bradleypriest any help on a native JSON query would be much appreciated. I haven't been able to figure out how to get basic field comparisons working in Postgres 9.3 without resorting to using ->> to perform string comparisons, and being unable to differentiate between a JSON null and an empty string. 9.4 seems like it may offer more support but I wasn't able to install it successfully on my machine.

If you could, SQL examples (for querying JSON on all different JSON data types) would be wonderful!