Any support for: join .... on .... and ...

[ghost]

Hi

I'm attempting to do something like this:

SELECT 
    Table1.IDField, Table2.IDField
FROM 
    Table1
LEFT OUTER JOIN Table2 
    ON Table1.PersonID = Table2.PersonID
    AND Table2.Category = 'Foo'
WHERE 
    Table1.IDField = '12345'

I've able to do the following but I was wondering if there is a better way within the class?

$this->db->join('table prefix', table.id = join_table.id AND join_prefix.id = 1', 'LEFT');

Thanks!

[ghost]

I've added the following which seems to work well

Update of buildJoin function:

/**
     * Abstraction method that will build an JOIN part of the query
     */
    protected function _buildJoin () {
        if (empty ($this->_join))
            return;

        foreach ($this->_join as $data) {
            list ($joinType,  $joinTable, $joinCondition) = $data;

            if (is_object ($joinTable))
                $joinStr = $this->_buildPair ("", $joinTable);
            else
                $joinStr = $joinTable;

            $this->_query .= " " . $joinType. " JOIN " . $joinStr ." on " . $joinCondition;

            // Add join and query
            if (!empty($this->_joinAnd) && isset($this->_joinAnd[$joinStr])) {
                foreach($this->_joinAnd[$joinStr] as $join_and_cond) {
                    list ($concat, $varName, $operator, $val) = $join_and_cond;
                    $this->_query .= " " . $concat ." " . $varName;

                    switch (strtolower ($operator)) {
                        case 'not in':
                        case 'in':
                            $comparison = ' ' . $operator. ' (';
                            if (is_object ($val)) {
                                $comparison .= $this->_buildPair ("", $val);
                            } else {
                                foreach ($val as $v) {
                                    $comparison .= ' ?,';
                                    $this->_bindParam ($v);
                                }
                            }
                            $this->_query .= rtrim($comparison, ',').' ) ';
                            break;
                        case 'not between':
                        case 'between':
                            $this->_query .= " $operator ? AND ? ";
                            $this->_bindParams ($val);
                            break;
                        case 'not exists':
                        case 'exists':
                            $this->_query.= $operator . $this->_buildPair ("", $val);
                            break;
                        default:
                            if (is_array ($val))
                                $this->_bindParams ($val);
                            else if ($val === null)
                                $this->_query .= $operator . " NULL";
                            else if ($val != 'DBNULL' || $val == '0')
                                $this->_query .= $this->_buildPair ($operator, $val);
                    }
                }
            }
        }
    }

New joinWhere function:

public function joinWhere($whereJoin, $whereProp, $whereValue = 'DBNULL', $operator = '=', $cond = 'AND')
    {
        $this->_joinAnd[$whereJoin][] = Array ($cond, $whereProp, $operator, $whereValue);
        return $this;
    }

And the array to store the joins:

protected $_joinAnd = array();

[avbdr]

Nice patch, thanks.

1. please add joinOrWhere () method as well to maintain same api as where()/orWhere().
2. please make a copypasted code from buildWhere() as a separate shared function and reuse them in both places.
3. Please add couple tests into tests/mysqlidbtests.php and couple lines to readme
4. Publish change as a pull request, so i will merge it with your name.

Alex

[ghost]

Cheers. Will do this at the weekend