Kashia is proud to present you this little tip:

Read-Only Join Classes

Tags: class database join model og performance sql view

Usecase:

You have heavily normalized your data in your database, but the speed degrades when actually viewing the data.

Models

class User
  property :name, String
  
  has_many ForumEntry
end

class ForumEntry
  property :body, String
  
  belongs_to User
end

This already shows, that whenever you want to display one ForumEntry, you also have to make another query to the DB in case you want to display the username.

View

<html>
  <head><title>Forum</title></head>
  <body>
    <ul>
    <?r ForumEntry.all.each do |entry| ?>
      <li>From: #{entry.user.name}<br />#{entry.body}</li>
    <?r end ?>
    </ul>
  </body>
</html>

Very common code, but like before: very inefficient. And that Problem grows when there are more relations.

The Read-Only Idea

If you're now thinking: "What if we could join all the data before we get it via Og?", you're on the right track!

Lets first create what we actually want: A ForumEntry with filled in name:

Some SQL

To make that piece reusable, we will create a VIEW in the DB, so we can flexible query a virtual table in the database.

CREATE VIEW ogforumentry_ro AS
SELECT ogforumentry.*, user.name AS username FROM ogforumentry, oguser
WHERE ogforumentry.user_oid = oguser.oid

Easy query, just a join on 2 tables from which we select the additional username as well as the normal forum-entry.

Some Model Again

So, we got the data, how to tell Og where to find the data? Very easy, just write what you just wrote in SQL:

class ForumEntry_RO
  property :body, String
  property :username
  
  def body=(*args); raise; end
  def username=(*args); raise; end
end

The methods below are just to show, that if you would edit this OgModel, it would throw errors back at you. This would even throw errors, when you didn't specify this methods, just because you haven't told your database backend how to handle UPDATE's to this VIEW (some DBs might not even be able to handle that).

At Last, The View

<html>
  <head><title>Forum</title></head>
  <body>
    <ul>
    <?r ForumEntry_RO.all.each do |entry| ?>
      <li>From: #{entry.username}<br />#{entry.body}</li>
    <?r end ?>
    </ul>
  </body>
</html>

And lookey here, only a single query for thousands of entries with usernames :)