lcor1979 asked:

Find object with Og using properties of a related object

Tags: og

I have a question on querying object with Og using properties of a related object

Let's say I have this model:

class Forum
  property :name, String
  has_many :posts, Post
end

class Post
  property :title, String
  property :message, String
  belongs_to :forum, Forum
end

I'll like to find in one query a post by specifying post title and forum name.

The SQL I'll like to be generated is : 

select * from ogpost where title = 'my title' and forum_oid = (select oid from ogforum where name = 'forum name')

I know I can do 

f = Forum.find_by_name(name)
Post.find_by_title_and_forum(title,f)

but I'll like to avoid to load the whole object Forum. I also can write the SQL myself but I don't want to hardcode columns and table names.

What I would really love to do is something like : 

Post.find_by_title_and_forum_name(title,forumName)

Is there a way to achieve something like this ?

Regards,

Laurent

(2 attempts)

Kashia answered:

Well, it would require quite more complex rules to parse this additional information 'forum_name' which it would have to resolve to the actual column 'name' on 'ogforum' and use this for a join query, pretty much complex and error prone if you ask me.

If you don't have too much of those special queries but use them constantly, you can just create them yourself:

class Post
  def self.find_by_title_and_forum_name(title, forum_name)
    cond = {:where => "title = '#{title}' AND 
            #{Post.relation(:forum).foreign_key} = (
               SELECT #{Forum.primary_key} 
               FROM #{Forum::OGTABLE} 
               WHERE name = '#{forum_name}')"}
    return Post.find(cond)
  end
end

Not exactly pretty but should get the job done.

I don't know how portable that subquery is though and if one should perhaps use a join...

class Post
  def self.find_by_title_and_forum_name(title, forum_name)
    cond = {:where => "title = '#{title}' AND #{Forum::OGTABLE}.name = '#{forum_name}')", 
            :include => :forum, 
            :select => "#{OGTABLE}.*"}
    return Post.find(cond)
  end
end

The :select isn't actually needed but will cut down the transferred data a bit.

I didn't test either of the methods, they should work though.

Extra for you to test, a real version to be compatible to what you want:

In entity.rb:

def finder(match, args)
  finder = (match.captures.first == 'all_by' ? :find : :find_one)
  
  attrs = match.captures.last.split('_and_')
  
  options = (annotation[:find_options] || {}).dup
  options = args.pop if args.last.is_a?(Hash)
  
  relations_map = {}
  relations.map {|r| relations_map[r.name.to_s] = r}
  
  condition = attrs.zip(args).map do |name, value|
    if relation = relations_map[name]
      field_name = relation.foreign_key
      value = value.send(relation.target_class.primary_key)       
      value = ogmanager.store.quote(value)   
    elsif name =~ /^(#{relations_map.keys.join('|')})_(.*)$/
      r = relations_map[$1]
      tc = r.target_class
      if tc.serializable_attributes.include?($2.to_sym)
        field_name = r.foreign_key
        value = "(SELECT #{tc.primary_key} FROM #{tc::OGTABLE} WHERE #{$2} = '#{value}')"
      end
    else
      anno = ann(name.to_sym)
      field_name = anno[:field] || anno[:name] || name.to_sym
      value = ogmanager.store.quote(value)
    end
    
    options["#{name}_op".to_sym] ||= 'IN' if value.is_a?(Array)
    
    %|#{field_name} #{options.delete("#{name}_op".to_sym) || '='} #{value}|
    
  end.join(' AND ')

  options.merge!(
    :class => self,
    :condition => condition
  )

  return ogmanager.store.send(finder, options)
end

Replace the finder function with this one and test if it works with how you use it. A simple test has shown that it works with Post.find_by_title_and_forum_name(). It probably has a few more problems which I can't see at the moment. It might even break existing code...

Answer to Question for inheriting

If you don't like messing with the code from Nitro directly, you can also put the finder code into a file to your liking and just load it.

I don't really get yet why your method doesn't work, but you can go the other way around and just monkey patch (I use this as a term for a good thing) it into Og.

module Og
  module EntityMixing
    class_extension do
      def finder(match, args)
        # etc
      end
    end
  end
end

That way you can avoid using a Superclass (which would prevent you being able to use Og for STI).

Hope that helps.

Seems that this method doesn't work (see post below). Just patch the original file, I'll see that this method gets included for Nitro 0.40.

if tc.serializable_attributes.include?($2.to_sym)

Please explain why this method didn't really work for you.

Glad to hear it's ok otherwise!

Rating: 5

lcor1979 answered:

Thank you Kashia, your latest method works very well.

I just had to replace 

if tc.serializable_attributes.include?($2.to_sym)

by

if tc.properties.include?($2.to_sym)

But now I have another question: I tried to put this method in a base class and make my model objects children of this class, but if I do this, Ruby cannot find the method:

class ModelObject

  def self.finder(match, args)
    finder = (match.captures.first == 'all_by' ? :find : :find_one)
    
    attrs = match.captures.last.split('_and_')
    
    options = (annotation[:find_options] || {}).dup
    options = args.pop if args.last.is_a?(Hash)
    
    relations_map = {}
    relations.map {|r| relations_map[r.name.to_s] = r}
  
    condition = attrs.zip(args).map do |name, value|
      if relation = relations_map[name]
        field_name = relation.foreign_key
        value = value.send(relation.target_class.primary_key)       
        value = ogmanager.store.quote(value)   
      elsif name =~ /^(#{relations_map.keys.join('|')})_(.*)$/
        r = relations_map[$1]
        tc = r.target_class
        puts tc.inspect
  
        if tc.properties.include?($2.to_sym)
          field_name = r.foreign_key
          value = "(SELECT #{tc.primary_key} FROM #{tc::OGTABLE} WHERE #{$2} = '#{value}')"
        end
      else
        anno = ann(name.to_sym)
        field_name = anno[:field] || anno[:name] || name.to_sym
        value = ogmanager.store.quote(value)
      end
      
      options["#{name}_op".to_sym] ||= 'IN' if value.is_a?(Array)
      
      %|#{field_name} #{options.delete("#{name}_op".to_sym) || '='} #{value}|
      
    end.join(' AND ')
  
    options.merge!(
      :class => self,
      :condition => condition
    )
  
    return ogmanager.store.send(finder, options)
  end

end

class Post < ModelObject
  property :title, String
  property :message, String
  belongs_to :forum, Forum
end

Am I missing something ?

UPDATE:

I have tried to use the monkey patch, but it don't works ... I have included the module redefinitation at the start of my model file, but when I run the file it is the "normal" entity finder that is called ...

For the serializable_attributes, I have an error (method not found) if I use it, if I use properties, I have no error.

Rating: 0