Discussion:
Natural Left Join support for Zend_Select
(too old to reply)
Daniel Rossi
2007-09-05 18:36:08 UTC
Permalink
Hi, I was wondering if there is support for Natural Left Join support
for Zend Select ? Ive come across a problem, where ive tried to
reduce the sql on join conditions, but when using natural join, i
cant do a left join on one table it claims that the field is ambigous
because its the same field aswell, only natural left join will work
on that table.

Let me know.
Daniel Rossi
2007-09-05 18:46:10 UTC
Permalink
I just added this to zend_db_select, it was easy to add, not sure why
only half of the things are supported ?

const NATURAL_LEFT_JOIN = 'natural left join';

public function joinLeftNatural($name, $cols = '*', $schema = null)
{
return $this->_join(self::NATURAL_LEFT_JOIN, $name, null,
$cols, $schema);
}

I dont really like to break or add things but oh well, maybe I could
extend it ?
Post by Daniel Rossi
Hi, I was wondering if there is support for Natural Left Join
support for Zend Select ? Ive come across a problem, where ive
tried to reduce the sql on join conditions, but when using natural
join, i cant do a left join on one table it claims that the field
is ambigous because its the same field aswell, only natural left
join will work on that table.
Let me know.
Jack Sleight
2007-09-05 20:35:42 UTC
Permalink
I don't know why it's not included I'm afraid, perhaps someone from the
DB team could let us know? You could create a new issue in JIRA for this
to be added.

There is no built in way to change the object returned by
Zend_Db_Adapter::select no, as it's hard coded. You can either extend
the adapter as well, and override the select method, or just create the
select objects without the helper method, i.e.

$select = new My_Db_Select($adapter);
Post by Daniel Rossi
I just added this to zend_db_select, it was easy to add, not sure why
only half of the things are supported ?
const NATURAL_LEFT_JOIN = 'natural left join';
public function joinLeftNatural($name, $cols = '*', $schema = null)
{
return $this->_join(self::NATURAL_LEFT_JOIN, $name, null,
$cols, $schema);
}
I dont really like to break or add things but oh well, maybe I could
extend it ?
--
Jack
Daniel Rossi
2007-09-05 21:17:43 UTC
Permalink
Its not extendable I have to copy over _join because of type checks
and another one as its private, i doubt it was ever thought out to be
extended ?

require_once 'Zend/Db/Select.php';

class Zend_Db_Select_Extended extends Zend_Db_Select
{
const NATURAL_LEFT_JOIN = 'natural left join';

public function __construct(Zend_Db_Adapter_Abstract $adapter)
{
parent::__construct($adapter);
}

/**
* Add a NATURAL LEFT JOIN table and colums to the query.
* A natural join assumes an equi-join across any column(s)
* that appear with the same name in both tables.
* Only natural inner joins are supported by this API,
* even though SQL permits natural outer joins as well.
*
* The $name and $cols parameters follow the same logic
* as described in the from() method.
*
* @param array|string|Zend_Db_Expr $name The table name.
* @param array|string $cols The columns to select from the
joined table.
* @param string $schema The database name to specify, if any.
* @return Zend_Db_Select This Zend_Db_Select object.
*/

public function joinLeftNatural($name, $cols = '*', $schema = null)
{
return $this->_join(self::NATURAL_LEFT_JOIN, $name, null,
$cols, $schema);
}

/**
* Populate the {@link $_parts} 'join' key
*
* Does the dirty work of populating the join key.
*
* The $name and $cols parameters follow the same logic
* as described in the from() method.
*
* @param null|string $type Type of join; inner, left, and null
are currently supported
* @param array|string|Zend_Db_Expr $name Table name
* @param string $cond Join on this condition
* @param array|string $cols The columns to select from the
joined table
* @param string $schema The database name to specify, if any.
* @return Zend_Db_Select This Zend_Db_Select object
* @throws Zend_Db_Select_Exception
*/
protected function _join($type, $name, $cond, $cols, $schema =
null)
{
$joinTypes = array(self::INNER_JOIN, self::LEFT_JOIN,
self::RIGHT_JOIN, self::FULL_JOIN, self::CROSS_JOIN,
self::NATURAL_JOIN, self::NATURAL_LEFT_JOIN);
if (!in_array($type, $joinTypes)) {
/**
* @see Zend_Db_Select_Exception
*/
require_once 'Zend/Db/Select/Exception.php';
throw new Zend_Db_Select_Exception("Invalid join type
'$type'");
}

if (empty($name)) {
$correlationName = $tableName = '';
} else if (is_array($name)) {
// Must be array($correlationName => $tableName) or array
($ident, ...)
foreach ($name as $_correlationName => $_tableName) {
if (is_string($_correlationName)) {
// We assume the key is the correlation name and
value is the table name
$tableName = $_tableName;
$correlationName = $_correlationName;
} else {
// We assume just an array of identifiers, with
no correlation name
$tableName = $name;
$correlationName = $this->_uniqueCorrelation
($tableName);
}
break;
}
} else if ($name instanceof Zend_Db_Expr) {
$tableName = $name;
$correlationName = $this->_uniqueCorrelation('t');
} else if (preg_match('/^(.+)\s+AS\s+(.+)$/i', $name, $m)) {
$tableName = $m[1];
$correlationName = $m[2];
} else {
$tableName = $name;
$correlationName = $this->_uniqueCorrelation($tableName);
}

// Schema from table name overrides schema argument
if (false !== strpos($tableName, '.')) {
list($schema, $tableName) = explode('.', $tableName);
}

if (!empty($correlationName)) {
if (array_key_exists($correlationName, $this->_parts
[self::FROM])) {
/**
* @see Zend_Db_Select_Exception
*/
require_once 'Zend/Db/Select/Exception.php';
throw new Zend_Db_Select_Exception("You cannot
define a correlation name '$correlationName' more than once");
}

$this->_parts[self::FROM][$correlationName] = array(
'joinType' => $type,
'schema' => $schema,
'tableName' => $tableName,
'joinCondition' => $cond
);
}

// add to the columns from this joined table
$this->_tableCols($correlationName, $cols);

return $this;
}

/**
* Generate a unique correlation name
*
* @param string|array $name A qualified identifier.
* @return string A unique correlation name.
*/
private function _uniqueCorrelation($name)
{

if (is_array($name)) {
$c = end($name);
} else {
// Extract just the last name of a qualified table name
$dot = strrpos($name,'.');
$c = ($dot === false) ? $name : substr($name, $dot+1);
}
for ($i = 2; array_key_exists($c, $this->_parts
[self::FROM]); ++$i) {
$c = $name . '_' . (string) $i;
}
return $c;
}

}
Post by Jack Sleight
I don't know why it's not included I'm afraid, perhaps someone from
the DB team could let us know? You could create a new issue in JIRA
for this to be added.
There is no built in way to change the object returned by
Zend_Db_Adapter::select no, as it's hard coded. You can either
extend the adapter as well, and override the select method, or just
create the select objects without the helper method, i.e.
$select = new My_Db_Select($adapter);
Post by Daniel Rossi
I just added this to zend_db_select, it was easy to add, not sure
why only half of the things are supported ?
const NATURAL_LEFT_JOIN = 'natural left join';
public function joinLeftNatural($name, $cols = '*', $schema = null)
{
return $this->_join(self::NATURAL_LEFT_JOIN, $name, null,
$cols, $schema);
}
I dont really like to break or add things but oh well, maybe I
could extend it ?
--
Jack
Ralf Eggert
2007-09-06 06:19:05 UTC
Permalink
Hi Daniel,
Post by Daniel Rossi
Its not extendable I have to copy over _join because of type checks
and another one as its private, i doubt it was ever thought out to be
extended ?
please refer to this discussion:

http://www.nabble.com/Extend-Zend_Db_Select-tf4040746s16154.html

and this issue:

http://framework.zend.com/issues/browse/ZF-1692

Best Regards,

Ralf
Daniel Rossi
2007-09-06 10:09:55 UTC
Permalink
Hi thanks yep thats the one, doesnt look like any work has been done
on it. The API really needs the ability of extending it, changing
private to protected should do that I guess ? However _join does
types checks, so needs the ability of adding custom types constants
or else update the entire class to accept the most possible
configuration of joins :)
Post by Ralf Eggert
Hi Daniel,
Post by Daniel Rossi
Its not extendable I have to copy over _join because of type checks
and another one as its private, i doubt it was ever thought out to be
extended ?
http://www.nabble.com/Extend-Zend_Db_Select-tf4040746s16154.html
http://framework.zend.com/issues/browse/ZF-1692
Best Regards,
Ralf
Bill Karwin
2007-09-06 22:47:03 UTC
Permalink
Natural outer joins are not supported in Zend_Db_Select because they
were thought to be an uncommon use case.

There are at least two very easy workarounds: specify the join
condition in the joinLeft() method, or else skip use of Zend_Db_Select
entirely, and instead write your SQL as a string and execute it with
$db->query().

Zend_Db_Select was never intended to support every permutation of SQL
syntax (example: UNION is not supported at all). It was intended to
make it easy to construct queries procedurally for the 80% most common
cases.

I agree the code for joinLeftNatural() you showed is not a big deal, but
that's only one-third of the required work. There are also unit tests
and documentation to write. Needs to be tested on all supported
databases. Some RDBMS brands may not support natural joins, etc. The
effort for these things has a way of expanding.

I don't know why Zend_Db_Select is not as friendly as it could be for
extending. I can guess that because of the "80%" principle, the answer
is, "you shouldn't be extending it. If you have an uncommon case, then
write your SQL yourself."

We will probably not add this method to Zend_Db_Select. Feel free to
enter a request, but it will be very low priority, since there are such
straightforward workarounds available.

Regards,
Bill Karwin


________________________________

From: Jack Sleight [mailto:jack.sleight-***@public.gmane.org]
Sent: Wednesday, September 05, 2007 1:36 PM
To: Daniel Rossi
Cc: Zend Framework General
Subject: Re: [fw-general] Natural Left Join support for
Zend_Select


I don't know why it's not included I'm afraid, perhaps someone
from the DB team could let us know? You could create a new issue in JIRA
for this to be added.
Daniel Rossi
2007-09-07 11:38:05 UTC
Permalink
Post by Bill Karwin
Natural outer joins are not supported in Zend_Db_Select because
they were thought to be an uncommon use case.
There are at least two very easy workarounds: specify the join
condition in the joinLeft() method, or else skip use of
Zend_Db_Select entirely, and instead write your SQL as a string and
execute it with $db->query().
Zend_Db_Select was never intended to support every permutation of
SQL syntax (example: UNION is not supported at all). It was
intended to make it easy to construct queries procedurally for the
80% most common cases.
I agree the code for joinLeftNatural() you showed is not a big
deal, but that's only one-third of the required work. There are
also unit tests and documentation to write. Needs to be tested on
all supported databases. Some RDBMS brands may not support natural
joins, etc. The effort for these things has a way of expanding.
I don't know why Zend_Db_Select is not as friendly as it could be
for extending. I can guess that because of the "80%" principle,
the answer is, "you shouldn't be extending it. If you have an
uncommon case, then write your SQL yourself."
We will probably not add this method to Zend_Db_Select. Feel free
to enter a request, but it will be very low priority, since there
are such straightforward workarounds available.
Hi bill. thats fine ill just use normal join syntax with join field
statements where it applies. I have the subclass working, but it
could BC break in the future so rather not go down that path.

I do however feel, that packages within an API should be able to be
extended really unless they are final preventing subclassing.

Jack Sleight
2007-09-05 18:46:37 UTC
Permalink
Yes, used the joinNatural method,
http://framework.zend.com/manual/en/zend.db.select.html
Post by Daniel Rossi
Hi, I was wondering if there is support for Natural Left Join support
for Zend Select ? Ive come across a problem, where ive tried to reduce
the sql on join conditions, but when using natural join, i cant do a
left join on one table it claims that the field is ambigous because
its the same field aswell, only natural left join will work on that
table.
Let me know.
--
Jack
Jack Sleight
2007-09-05 18:49:02 UTC
Permalink
Oh, sorry, didn't notice you wanted a natural *left *join. No,
Zend_Db_Select doesn't support it. You will have to write out the SQL
manually, or extend Zend_Db_Select and add a new method (joinLeftNatural).
Post by Daniel Rossi
Hi, I was wondering if there is support for Natural Left Join support
for Zend Select ? Ive come across a problem, where ive tried to reduce
the sql on join conditions, but when using natural join, i cant do a
left join on one table it claims that the field is ambigous because
its the same field aswell, only natural left join will work on that
table.
Let me know.
--
Jack
Daniel Rossi
2007-09-05 19:42:13 UTC
Permalink
Will it ever get added , or just create a subclass ?
Oh, sorry, didn't notice you wanted a natural left join. No,
Zend_Db_Select doesn't support it. You will have to write out the
SQL manually, or extend Zend_Db_Select and add a new method
(joinLeftNatural).
Post by Daniel Rossi
Hi, I was wondering if there is support for Natural Left Join
support for Zend Select ? Ive come across a problem, where ive
tried to reduce the sql on join conditions, but when using natural
join, i cant do a left join on one table it claims that the field
is ambigous because its the same field aswell, only natural left
join will work on that table.
Let me know.
--
Jack
Daniel Rossi
2007-09-05 19:47:19 UTC
Permalink
I did this however, how do i get it added now to $select = $this->db-
select()
class Zend_Db_Select_Extended extends Zend_Db_Select
{
const NATURAL_LEFT_JOIN = 'natural left join';

/**
* Add a NATURAL LEFT JOIN table and colums to the query.
* A natural join assumes an equi-join across any column(s)
* that appear with the same name in both tables.
* Only natural inner joins are supported by this API,
* even though SQL permits natural outer joins as well.
*
* The $name and $cols parameters follow the same logic
* as described in the from() method.
*
* @param array|string|Zend_Db_Expr $name The table name.
* @param array|string $cols The columns to select from the
joined table.
* @param string $schema The database name to specify, if any.
* @return Zend_Db_Select This Zend_Db_Select object.
*/

public function joinLeftNatural($name, $cols = '*', $schema = null)
{
return $this->_join(self::NATURAL_LEFT_JOIN, $name, null,
$cols, $schema);
}

}
Will it ever get added , or just create a subclass ?
Oh, sorry, didn't notice you wanted a natural left join. No,
Zend_Db_Select doesn't support it. You will have to write out the
SQL manually, or extend Zend_Db_Select and add a new method
(joinLeftNatural).
Post by Daniel Rossi
Hi, I was wondering if there is support for Natural Left Join
support for Zend Select ? Ive come across a problem, where ive
tried to reduce the sql on join conditions, but when using
natural join, i cant do a left join on one table it claims that
the field is ambigous because its the same field aswell, only
natural left join will work on that table.
Let me know.
--
Jack
Loading...