'Twas brillig, and Brenton Alker at 23/06/09 04:22 did gyre and gimble:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
awesome wrote:
I am new in Zend and relational database design.
I have two tables that I would like to join in one so that certain values
from 1st table are replaced with associated values from another table.
This query works:
SELECT a.iditems, a.model,
(SELECT name FROM headings WHERE idheadings = a.heading1) as heading1Name,
(SELECT name FROM headings WHERE idheadings = a.heading2) as heading2Name
from items as a;
I would like to to this kind of query with zend... is it possible?
What you're doing is subselects, not joins. You can do subselects in ZF
by creating them as separate selects then combining them. But I don't
think you actually need them in this case. Guessing based on the query,
I think this SQL is equivalent:
SELECT items.iditems, items.model, h1.name as heading1Name, h2.name as
heading2Name
FROM items
INNER JOIN headings AS h1 ON h1.idheadings = items.heading1
INNER JOIN headings AS h2 ON h2.idheadings = items.heading2
I *think* (not tried) that the first statement will return nuls when no
match is found in the subselect, so this is more like a left join rahter
than an inner join.
Logic still applies, but just s/inner/left/.
Which can be done in ZF like:
$table = new Model_DbTable_Items();
$select = $table->getAdapter()->select()
->from('items', array('iditems','model'))
->joinInner(array('h1' => 'headings'), 'h1.idheadings =
items.heading1', array('heading1Name' => 'name'))
->joinInner(array('h2' => 'headings'), 'h1.idheadings =
items.heading2', array('heading2Name' => 'name')))
->where('iditems = ?', '1');
$rows = $table->fetchAll($select);
(Untested)
- --
Brenton Alker
PHP Developer - Brisbane, Australia
http://blog.tekerson.com/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkpASnAACgkQ7bkAtAithuuoVgCfcMS8QKR1iuHOnK5UaBp3bFKM
shoAoJi67Mpm+Mo06XNo8/EvvHyMRAxt
=TTYe
-----END PGP SIGNATURE-----
--
Colin Guthrie
gmane(at)colin.guthr.ie
http://colin.guthr.ie/
Day Job:
Tribalogic Limited [http://www.tribalogic.net/]
Open Source:
Mandriva Linux Contributor [http://www.mandriva.com/]
PulseAudio Hacker [http://www.pulseaudio.org/]
Trac Hacker [http://trac.edgewall.org/]
