join tables

InputObjects can join tables. When creating or editing it can fetch all the options from the other table and display them as a drop down menu. When displaying it can do the join (or left join) during the select and efficiently fetch (and format) the output.

Here's an example from another site:



class AgentInput extends SelectInput { // foreign key select

   

    function AgentInput($table="",$field="",$caption="",$alias="",$attributes="") {

        $this->caption = $caption ? $caption : $field;

        $this->table = $table;

        $this->field = $field;

        $this->alias = $alias ? $alias : $field;

        $this->attributes = $attributes;

        $this->showActiveOnly = true;

    }

    function loadAgents($showTerminated = false) { // load on demand

        if($showTerminated) {

            $rs = getQuery("select User.authlevel, Agent.id,Agent.lname,Agent.fname from Agent, User where Agent.userid = User.id order by User.authlevel desc, Agent.lname,Agent.fname");

        } else {

            $rs = getQuery("select User.authlevel, Agent.id,Agent.lname,Agent.fname from Agent, User where Agent.userid = User.id and User.authlevel > 999 order by Agent.lname,Agent.fname");

        }



        $this->options['NULL'] = "None Assigned";

        $termdiv = true;

        while($a = mysql_fetch_array($rs)) {

            if($termdiv && ($a['authlevel'] < 1000)) {

                $this->options[] = "-----terminated-----";

                $termdiv = false;

            }

            $this->options[$a['id']] = ($a['lname'] & $a['fname']) ? $a['lname'].", ".$a['fname'] : $a['fname']." ".$a['lname'];

        }   

    }

    function forUpdate(&$data,$pk='') {

        if( ! $this->options) $this->loadAgents();

        return SelectInput::forUpdate($data,$pk);

    }

    function select(&$qo) {

        $qo->leftJoin($this->table,$this->field,"Agent","id");

        $qo->select("Agent","fname");

        $qo->select("Agent","lname");

        $qo->select($this->table,$this->field);

    }

    function forDisplay(&$data) {

        return $data['fname']." ".$data['lname'];

    }

}


an Agent is a real estate agent (nestseekers.com)

So when you are doing an insert or update, it loads all the agents (joined to their user records even) and presents those in a drop down menu.
If the AgentInput is used multiple times, it only needs to load it once. You could even put it into a global variable if you needed multiple of these
on a single page; it would still only need to load the list once.

For display, it does a left join. So if you haven't yet assigned an agent it displays blank. You could make it return "Not assigned" or something.
In some situations you might want to right join.

There is also the ForeignKeySelect input class which is made for general use. I find that I always prefer to just make a custom input for my situation.
One use is that in this case I could link from the Agent to the Agent's display page.