Zend Framework:如何使用联接在一个查询中组合三个表?

发布于 2024-08-23 11:38:28 字数 1223 浏览 6 评论 0原文

我有这样的三个表:

Person 表:

person_id |    name     |   dob
--------------------------------
    1     |   Naveed    |  1988
    2     |   Ali       |  1985
    3     |   Khan      |  1987
    4     |   Rizwan    |  1984

Address 表:

address_id |  street  |   city  |  state  | country
----------------------------------------------------
   1       | MAJ Road | Karachi |  Sindh  | Pakistan
   2       | ABC Road | Multan  |  Punjab | Pakistan
   3       | XYZ Road | Riyadh  |    SA   | SA

Person_Address 表:

person_id | address_id
----------------------
   1      |     1
   2      |     2
   3      |     3

现在我想通过一个查询获取 Person_Address 表的所有记录以及他们的人员和地址记录:

person_id|    name  |  dob  | address_id |  street  |   city  |  state  | country
----------------------------------------------------------------------------------
    1    |   Naveed |  1988 |    1       | MAJ Road | Karachi |  Sindh  | Pakistan
    2    |   Ali    |  1985 |    2       | ABC Road | Multan  |  Punjab | Pakistan 
    3    |   Khan   |  1987 |    3       | XYZ Road | Riyadh  |    SA   | SA

如何使用 zend?谢谢

I have three tables like this:

Person table:

person_id |    name     |   dob
--------------------------------
    1     |   Naveed    |  1988
    2     |   Ali       |  1985
    3     |   Khan      |  1987
    4     |   Rizwan    |  1984

Address table:

address_id |  street  |   city  |  state  | country
----------------------------------------------------
   1       | MAJ Road | Karachi |  Sindh  | Pakistan
   2       | ABC Road | Multan  |  Punjab | Pakistan
   3       | XYZ Road | Riyadh  |    SA   | SA

Person_Address table:

person_id | address_id
----------------------
   1      |     1
   2      |     2
   3      |     3

Now I want to get all records of Person_Address table but also with their person and address records like this by one query:

person_id|    name  |  dob  | address_id |  street  |   city  |  state  | country
----------------------------------------------------------------------------------
    1    |   Naveed |  1988 |    1       | MAJ Road | Karachi |  Sindh  | Pakistan
    2    |   Ali    |  1985 |    2       | ABC Road | Multan  |  Punjab | Pakistan 
    3    |   Khan   |  1987 |    3       | XYZ Road | Riyadh  |    SA   | SA

How it is possible using zend? Thanks

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

清音悠歌 2024-08-30 11:38:28

参考指南是了解 Zend_Db_Select 的最佳起点。当然,还有我下面的示例:

//$db is an instance of Zend_Db_Adapter_Abstract
$select = $db->select();
$select->from(array('p' => 'person'), array('person_id', 'name', 'dob'))
       ->join(array('pa' => 'Person_Address'), 'pa.person_id = p.person_id', array())
       ->join(array('a' => 'Address'), 'a.address_id = pa.address_id', array('address_id', 'street', 'city', 'state', 'country'));

获取一行就像这样简单:

$db->fetchRow($select);

在调试 Zend_Db_Select 时,您可以使用一个聪明的技巧 - 只需打印 select 对象,该对象反过来调用 toString 方法来生成 SQl:

echo $select; //prints SQL

The reference guide is the best starting point to learn about Zend_Db_Select. Along with my example below, of course:

//$db is an instance of Zend_Db_Adapter_Abstract
$select = $db->select();
$select->from(array('p' => 'person'), array('person_id', 'name', 'dob'))
       ->join(array('pa' => 'Person_Address'), 'pa.person_id = p.person_id', array())
       ->join(array('a' => 'Address'), 'a.address_id = pa.address_id', array('address_id', 'street', 'city', 'state', 'country'));

It's then as simple as this to fetch a row:

$db->fetchRow($select);

In debugging Zend_Db_Select there's a clever trick you can use - simply print the select object, which in turn invokes the toString method to produce SQl:

echo $select; //prints SQL
挽心 2024-08-30 11:38:28

我不确定您是否正在寻找 SQL 来执行上述操作,或者使用 Zend 的工具进行编码。鉴于标签中存在“sql”和“joins”,以下是您需要的 SQL:

SELECT p.person_id, p.name, p.dob, a.address_id, street, city, state, country
FROM person p
INNER JOIN Person_Address pa ON pa.person_id = p.person_id
INNER JOIN Address a ON a.address_id = pa.address_id

请记住,Person_Address 告诉我们 Person 和 Address 之间存在多对多关系。许多人可能共享一个地址,并且一个人可能有多个地址。

上面的 SQL 将显示所有此类关系。因此,如果 Naveed 有两条 Address 记录,则结果集中将有两行 person_id = 1。

I'm not sure if you're looking for SQL to do the above, or code using Zend's facilities. Given the presence of "sql" and "joins" in the tags, here's the SQL you'd need:

SELECT p.person_id, p.name, p.dob, a.address_id, street, city, state, country
FROM person p
INNER JOIN Person_Address pa ON pa.person_id = p.person_id
INNER JOIN Address a ON a.address_id = pa.address_id

Bear in mind that the Person_Address tells us that there's a many-to-many relationship between a Person and an Address. Many Persons may share an Address, and a Person may have more than one address.

The SQL above will show ALL such relationships. So if Naveed has two Address records, you will have two rows in the result set with person_id = 1.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文