Closure table

[mangel.murti]

id,ancestor,descendant,lvl,tier_id
1,  11,       11,       0,   0
2,  11,       65,       1,   1
3,  65,       65,       0,   0
5,  11,       66,       1,   2
6,  66,       66,       0,   0

i am using closure table model. i placed tier_id cloumn there.
let say new entry save in above table then tier_id should be placed like 1,2,3
if any previous ancestor exist then it should check in table and if tier_id reaches 3 then it will store 3 and if any new ancestor entry occure then it will store 1,2,3 accordingly.

for achiveing i made a function generate_tier($node_id) and passing $node_id to it. but when i am seeing result array it will incrementing previous tier_id.

when there is only one entry in table.
Array
(
    [0] => Array
        (
            [name] => Shri ram
            [affiliate_id] => 597612
            [id] => 65
            [parent] => 11
            [level] => 1
            [tier_id] => 1
        )
)
after second record entry. id--> 65 tier_id-->2
Array
(
    [0] => Array
        (
            [name] => Shri ram
            [affiliate_id] => 597612
            [id] => 65
            [parent] => 11
            [level] => 1
            [tier_id] => 2
        )

    [1] => Array
        (
            [name] => Ravi
            [affiliate_id] => 831375
            [id] => 66
            [parent] => 11
            [level] => 1
            [tier_id] => 1
        )

)


/**
     * Add a node (as last child).
     * 
     * @param  int    node id
     * @param  int    target id
     * @return boolean
     */
    public function add($node_id, $target_id = 0) {

        $sql = 'SELECT ancestor, ' . $node_id . ', lvl+1
                FROM ' . $this->closure_table . '
                WHERE descendant = ' . $target_id . '
                UNION 
                SELECT ' . $node_id . ',' . $node_id . ',0';

        //original $query = 'INSERT INTO '.$this->closure_table.' (ancestor, descendant,lvl) ('.$sql.')';
        $query = 'INSERT INTO ' . $this->closure_table . ' (ancestor, descendant,lvl) ' . $sql . '';
        $result = $this->db->query($query);
        return $result;
    }


    public function generate_tier($node_id) {

        $sql = 'select id,tier_id from cbf_affiliate_chain_closers where ancestor=' . $node_id . ' and descendant <> ' . $node_id . '';
        $query = $this->db->query($sql);
        $result_arr=$query->result();

        if (count($result_arr) > 0) {
            foreach ($result_arr as $row) {
                $previous_tier = $row->tier_id;
                if ($previous_tier == 3) {
                    $data = array(
                        'tier_id' => 3,
                    );
                } else {

                    $data = array(
                        'tier_id' => $row->tier_id + 1,
                    );
                }
                $this->db->where('id', $row->id);
                $this->db->update($this->closure_table, $data);
            }
        } 
    }

[mattster]

I think what you're doing here is either weird on unclear.

So my understanding is, you add a row (ID = 1, TIER = 1, PARENT = 0) then when you add another row the TIER should increment (ID = 2, TIER = 2, PARENT = 1). This should happen again (ID = 3, TIER = 3, PARENT = 2), and then reset and start again? (ID = 4, TIER = 1, PARENT = 3)

Either way, it's difficult to see why you aren't just forever incrementing the TIER.

it will incrementing previous tier_id.

Thats because you're passing the previous tier_id into the update? Just use $node_id.

Hopefully that clears things up.

[mangel.murti]

Thanks mattster for you reply.
Acctully i am working with a three tier affiliate system. so i choose closures table model.
i have a second table where all % is storing , like tier_1-->3% , tier_2 -->1% tier_3-->0.2%
if i forever incrementing the TIER then how will i distribute % to affiliate?
so if tier_id reaches 3 then other new entry for tier_id column will always goes 3.
i am just reading your suggetion.

Not sure about any of this. Please take the time to explain CLEARLY exactly what you're trying to do and give sample data. I don't think your first few posts are sufficient.

[mangel.murti]

Refer someone A = you will receive 5% of earnings generated by A, every month
A refers B = you will receive 2% of earnings generated by B, every month
B refers C = you will receive 0.2% of earnings generated by C, every month

i want to achive above. so is this correct way Or what i have to do to achive above.
i am storing referrals in closure table like i already mention above. 
Application wants only process to level 3. so i thought that i will make a column like tier_id
and store tier_id as 1,2,3,3,3 for new user. before storing data to closures table , i will check in table that if 
there is any ancentor exits then new user tier_id will store accordingly.
i don't know if i am write or wrong , so for getting write answer disccussing with you all.

OK, not sure if you need lvl field. When inserting you could count the ancestor - the tier level will be least(3, (count of ancestor)). You could also make a compound PK from ancestor and descendant - so perhaps you don't need an id field. Just:

ancestor | descendant | tier

INSERT INTO closure SET ancestor = ?, descendant = ?, tier = LEAST(3, (SELECT COUNT(*) FROM (SELECT * FROM closure) AS tblalias WHERE ancestor = ?))

The ? placeholders can be used with PDO for example:

$stmt->execute(array($ancestor,$descendant,$ancestor));

Or you could bind the values individually with bindValue() before running the execute().

BTW - change each occurrence of closure in the SQL to the name of your table. tblalias could be anything really, it's required for the queries in this case. You also may think it's a but verbose, but MySQL does not allow direct access to inner query on the same tables for UPDATE, INSERT and DELETE, hence the need for the 'extra' query part.