Query by responsible_id

[phildionne]

Let's say we have something like:

Logidze.with_responsible(user.id) do
  Post.create
end

It would be really useful to be able to find records by their responsible_id:

Post.where(log_data: { responsible_id: user.id })

Otherwise to be able to do this you have to add a real responsible_id column on each of the models and duplicate the with_responsible logic with a slight twist.

Since log_data is JSONB, I think something like this (pseudo-code) is doable, but it requires to know the structure of log_data:

Post.where("(log_data -> 'h' @> '[r ->> ?]')", user.id)

[palkan]

Thanks for the suggestion. The feature seems to be useful.

to find records by their responsible_id:

Should we take into account the latest responsible_id? Or we want to return all records with which this particular user has interacted at least once (i.e., if there is at least one log record with this id)?

Note, we added a general meta information to logs in #79, the format has changed a bit: now responsible info is stored in meta (m) hash by _r key.

[phildionne]

Thinking about it, the holy grail would be to query by any meta field, or attribute, not just _r.

From a quick reading it seems like querying those jsonb fields would be inefficient for the lack of an index, which I think can be added on per-field basis. Would have to check.

I know this is going further than the initial PR topic, but this is just a suggestion. Wouldn't it be cool to expose a similar API than https://github.com/palkan/pgrel ?

I'm thinking of something like:

# Retrieve all versions where meta 'responsible_id' is any of 1 or 2
Model.with_log_data.log_meta('r').any(1, 2)

# Retrieve all versions where attribute 'name' changed
Model.with_log_data.log_attributes.key(:name)

# Retrieve all versions where attribute 'name' was set to 'value'
Model.with_log_data.log_attributes.path(name: 'value')

And for any of these queries, allow to order by version or timestamp:

# Retrieve latest version where responsible is 1
Model.with_log_data.log_meta(r: 1).log_order(:v).first

# Retrieve latest version from timestamp where responsible is 1
Model.with_log_data.log_meta(r: 1).log_order(:ts).first

@palkan thoughts?

[palkan]

What do you mean by "Retrieve all versions"?

For example, given the following setup:

record_a = nil
record_b = nil

Logidze.with_responsible(1) do
  record_a = Model.create(name: "a")
  record_b = Model.create(type: "test")
end

Logidze.with_responsible(2) do
  record_a.update!(name: "b")
end

Logidze.with_responsible(3) do
  record_a.update!(name: "a")
  record_b.update!(type: "not-test")
end

What should the following calls return?

#=> should this return record_a three times (different snapshot?) or only the current state of record_a?
Model.with_log_data.log_attributes.key(:name) 

# Should this return all the records "touched" by user 2 in their current state of what?
Model.with_log_data.log_meta(r: 2)

Wouldn't it be cool to expose a similar API than https://github.com/palkan/pgrel ?

I was thinking about using this gem under the hood (and probably, extending it functionality a bit), instead of re-inventing it.

[phildionne]

How about:

# This returns an ActiveRecord::Relation with each record at their latest version, and the corresponding log_data:
Model.with_log_data.log_attributes.key(:name) 
[
  {
    id: "...",
    name: "a",
    log_data: [
      {
        version: 1,  
        time: "...",
        attributes: {
          name: "a"
        },
        meta: {
          responsible: 1
        }
      },
      {
        version: 2,  
        time: "...",
        attributes: {
          name: "b"
        },
        meta: {
          responsible: 2
        }
      },
      {
        version: 3,  
        time: "...",
        attributes: {
          name: "a"
        },
        meta: {
          responsible: 3
        }
      }
    ]
  }
]

Model.with_log_data.log_attributes.key(:name).log_order(:version).log_limit(1)
[
  {
    id: "...",
    name: "a",
    log_data: [
      {
        version: 3,  
        time: "...",
        attributes: {
          name: "a"
        },
        meta: {
          responsible: 3
        }
      }
    ]
  }
]

Model.with_log_data.log_meta(responsible: 2)
[
  {
    id: "...",
    name: "a",
    log_data: [
      {
        version: 2,  
        time: "...",
        attributes: {
          name: "b"
        },
        meta: {
          responsible: 2
        }
      }
    ]
  }
]

It's may not be super performant, but it allows to do something like:

Model.with_log_data.log_meta(responsible: 2).each do |model|
  model.at(version: model.log_data.sort_by(&:version).first.fetch(:version))
end

I was thinking about using this gem under the hood (and probably, extending it functionality a bit), instead of re-inventing it.

Agreed