Support closure tables?

[IllyaMoskvin]

I was wondering if there is a possibility that PHP ActiveRecord might support closure tables. Essentially, it's a way to represent trees that's flexible and computationally cheap, once you get into non-trivial queries. Here is a Ruby Active Record implementation that illustrates the concept simply: bkuhlmann/lineage. See also this SlideShare and this StackOverflow question for pros and cons.

The basic idea is to use a separate table to store links from each parent node to each child node (including children of sub-nodes to an unlimited depth), as well as to itself, along with the current depth, where depth=0 is itself, depth=1 are immediate children, etc.

I've found the closure table pattern to be super-helpful for deep-retrieval of children nodes from any arbitrary parent node. Without a closure table, you're limited to retrieving one level at a time through each sub-branch of the tree until you run out of children. With a closure table, you're looking at two or three queries, tops.

Part of the problem for me is that MySQL does not support WITH RECURSIVE, so closure tables are the next best option. Unfortunately, like many developers, I don't have a choice in which DBMS we can use...

I think expanding the through syntax and logic might be the best option. Maybe there is a way to define an association with a closure table in place already, but I'm not sure that depth is accounted for. I feel like this might be related to issue #354, since adding the ability to filter associations would also allow one to filter by depth. I'm a bit out of my depth here. What are your thoughts on this situation?

P.S. What I'm doing currently is to query a relationships table by hand to generate a list of ids, but I feel like that goes against the spirit of ActiveRecord. Here's some sample code:

$parent_id = 3;
$max_depth = 3;

$result = Relationship::find('all', array(
    'conditions' => array( '`parent_id` = ? AND `depth` < ?', $parent_id, $max_depth ),
    'select' => '`child_id`'
));

// There is probably a way to do this part more efficiently...
$child_ids = array();
foreach( $result as $row ) {
    $child_ids[] = $row->child_id;
}

$children = Category::find( 'all', array( 
    'conditions' => array( '`id` in(?)', $child_ids )
));

[twoixter]

Hi @IllyaMoskvin , sorry to be so late. I'm sure you came to a solution time ago, but I'm commenting on this issue just in case.

I'm not sure this is a feature worth adding to the core of AR. I mean, this is more a case of a particular algorithm implementation. Even the gem you mentioned of the "lineage" for Ruby AR does so by extending the Ruby AR library, not by modifying the core.

You can easily extend PHP-AR by extending \ActiveRecord\Model thus creating your own, say \ClosuredModel. This new \ClosuredModel could add/override methods and relations to simulate a closured model. I'm thinking of new methods called get_children and the like (remember, all methods beginning with get_* becomes "getters", like in $this->children is really a call to $this->get_children()).

I will illustrate this with a similar problem we had in our app. We needed to have some "soft" models, in which a delete operation does not delete the row but touch a deleted_at column. We took the above path and created a SoftModel by extending \ActiveRecord\Model:

/**
 * Example "SoftModel". Code redacted for brevity and to protect the guilty :-)
 */
class SoftModel extends \ActiveRecord\Model {

    /**
     * CONST to declare the ActiveRecord condition for a Model to be active
     */
    const ACTIVE_CONDITIONS = "deleted_at IS NULL";

    /**
     * Deletes records matching conditions in $options
     * Overrides the \ActiveRecord\Model::delete_all and performs an UPDATE
     * operation setting the "deleted_at" field to the current timestamp.
     *
     * If the table for the model does NOT contain a column named "deleted_at"
     * this method performs the original operations for the base model.
     * (Physically deleting all records with DELETE FROM sql)
     *
     * See original \ActiveRecord\Model::delete_all for more information
     *
     * @params array $options
     * return integer Number of rows affected
     */
    public static function delete_all($options = [])
    {
        // Perform the original "delete_all" operation if the table does not
        // contain a "deleted_at" column
        if (static::use_hard_delete()) return parent::delete_all($options);

        /** ... here comes the code to soft delete ... **/
    }

    /**
     * Deletes this model from the database and returns true if successful.
     * For our models with a "deleted_at" column, this means updating
     * the "deleted_at" column to NOW()
     *
     * @return boolean
     */
    public function delete()
    {
        if (static::use_hard_delete()) return parent::delete();

        /** ... here comes the code to soft delete ... **/
        return true;
    }

    /**
     * Returns all active models in the database. Active models are those
     * having a "deleted_at" column with null values.
     */
    public static function all(/* ... */)
    {
        // Perform the original "all" method if the table does not contain
        // a "deleted_at" column
        if (static::use_hard_delete()) return call_user_func_array("parent::all", func_get_args());

        $conditions = func_get_args();
        return parent::find("all", static::merge_active_conditions(reset($conditions)));
    }

    /**
     * Counts all active models in the database. Active models are those
     * having a "deleted_at" column with null values.
     */
    public static function count(/* ... */)
    {
        // Perform the original "count" method if the table does not contain
        // a "deleted_at" column
        if (static::use_hard_delete()) return call_user_func_array("parent::count", func_get_args());

        $conditions = func_get_args();
        return parent::count(static::merge_active_conditions(reset($conditions)));
    }

    /**
     * Returns true if we must use a "hard delete" for the table.
     * (It does not contain a "deleted_at" column)
     *
     * @return boolean      TRUE if this table is hard deletable
     */
    protected static function use_hard_delete()
    {
        return !array_key_exists("deleted_at", static::table()->columns);
    }
}

I've redacted the code to hide non-relevant parts, but I think you get the idea. For models we need a soft delete approach we inherit from \SoftModel instead of \ActiveRecord\Model and all semantics remains the same: count, find, etc just works.