zend框架连接3个表

发布于 2024-10-12 10:21:00 字数 561 浏览 0 评论 0原文

我有 3 个表（订单、产品、订单项目）。在订单中我有日期。在 order_item 中，我有 product_id 和 order_id。我需要选择当月创建的所有有订单的产品。这是我的选择：

$select = $this->select()
    ->setIntegrityCheck(false)
    ->from(array('o' => 'order'))
    ->join(array('oi' => 'order_item'), 'o.id = oi.order_id', array('quantity'))
        ->joinLeft(array('p' => 'product'), 'p.id = oi.product_id', array('id', 'pbv', 'name'))
        ->where('MONTH(o.date) = MONTH(CURDATE())');

但是当我没有订单时结果为空。我应该永远拥有所有产品。对不起我的英语。谢谢。

原文

I have 3 tables (order, product, order_item). In order i have the date. In order_item i have product_id and order_id. I need to select all products with orders, what created in the current month. It is my select:

$select = $this->select()
    ->setIntegrityCheck(false)
    ->from(array('o' => 'order'))
    ->join(array('oi' => 'order_item'), 'o.id = oi.order_id', array('quantity'))
        ->joinLeft(array('p' => 'product'), 'p.id = oi.product_id', array('id', 'pbv', 'name'))
        ->where('MONTH(o.date) = MONTH(CURDATE())');

But when i haven't orders result is empty. And i should always have all products. Sorry for my english. Thanks.

分享到QQ

分享到微博

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

发布评论

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

痴意少年 2024-10-19 10:21:00

这非常困难。正确的 SQL：

USE lyf;
SELECT
  *
FROM
  `order` AS o
  LEFT JOIN order_item AS oi ON oi.order_id = o.id
  RIGHT JOIN product AS p ON oi.product_id = p.id
WHERE
  IF(o.`date` IS NOT NULL, MONTH(o.`date`) = MONTH(NOW()), 1) = 1

It was very hard. The right SQL:

USE lyf;
SELECT
  *
FROM
  `order` AS o
  LEFT JOIN order_item AS oi ON oi.order_id = o.id
  RIGHT JOIN product AS p ON oi.product_id = p.id
WHERE
  IF(o.`date` IS NOT NULL, MONTH(o.`date`) = MONTH(NOW()), 1) = 1

回复收藏 0 原文