I’ve been experimenting with Christopher Coenraet’s example code for a simple ORM. His code provides a simple way to load data from a table and return an ArrayCollection of typed objects. (This post won’t make much sense if you haven’t looked through his example.)

However, I found myself needing to return an ArrayCollection of objects related to another object by a “has many” relationship.  I’ve set my tables up using ruby on rails — which is a great tool for helping you get your AIR application sqlite db populated and running — so in my application the columns follow the RoR convention: since a “project” has many “scenarios” the scenarios table has a “project_id” column to represent the foreign key relationship.

So how do I get all scenarios for one project id?

Here’s my first attempt at within Coenraet’s EntityManager class to give me all the related tables:

public function findByForeignKey(c:Class, foreignKeyColumnName:String, foreignKeyID:uint):ArrayCollection
{
    if (foreignKeyColumnName=="") throw new Error("foreignKeyColumnName cannot be empty")
    if (foreignKeyID<1) throw new Error("foreignKey must be greater than 0")

    if (!_map[c]) loadMetadata(c);
    var stmt:SQLStatement = _map[c].findByForeignKeyStmt
    var identity:Object = _map[c].identity;

    stmt.parameters[":foreignKeyColumnName"] = foreignKeyColumnName
    stmt.parameters[":foreignKeyID"] = foreignKeyID
    stmt.execute()
    var result:Array = stmt.getResult().data;
    if (result==null) return null
    return typeArray(result, c);
}

Farther down in the EntityManager class, here’s the code that adds the sql statement along with the others…

var findByForeignKeySQL:String = "SELECT * FROM " + table + " WHERE :foreign_key_column_name=:foreign_key_id"

stmt = new SQLStatement()
stmt.sqlConnection = sqlConnection
stmt.text = findByForeignKeySQL
_map[c].findByForeignKeyStmt = stmt

Then, I use this method where I want to load all scenarios for certain project…

     var em:EntityManager.getInstance()
     scenariosAC = db.findByForeignKey(ScenarioVO, "project_id", project.id)

Ok. So this should all work. But…it doesn’t. It turns out that I can’t use parameter replacement within a SQLStatement for anything other than SQL values! So in my statement

var findByForeignKeySQL:String = "SELECT * FROM " + table + " WHERE :foreignKeyColumnName=:foreignKeyID"

that foreignKeyColumName doesn’t get replaced. So, I switched up the method to create a new SQLStatement each time it’s called…which is ugly, but in my application I won’t be using this too much so it’s not a big deal.

public function findByForeignKey(c:Class, foreignKeyColumnName:String, foreignKeyID:uint):ArrayCollection
{
    if (foreignKeyColumnName=="") throw new Error("foreignKeyColumnName cannot be empty")
    if (foreignKeyID<1) throw new Error("foreignKey must be greater than 0")

    if (!_map[c]) loadMetadata(c);
    var stmt:SQLStatement = new SQLStatement()
    stmt.text = "SELECT * FROM " + _map[c].table + " WHERE " + foreignKeyColumnName + "=" + foreignKeyID
    stmt.sqlConnection = sqlConnection
    stmt.execute()

    stmt.parameters[":foreignKeyColumnName"] = foreignKeyColumnName
    stmt.parameters[":foreignKeyID"] = foreignKeyID
    stmt.execute()
    var result:Array = stmt.getResult().data;
    if (result==null) return null
    return typeArray(result, c);
}

And there you go. A simple method to get your “has many” relationships.