mysql - 左连接不适用于 codeigniter

Question

我是代码 igniter 的新手。我正在尝试使用连接多个表来执行 delete 查询，但我在查询中遇到错误。

这是我的查询代码。

$this->db->from('order');   
$this->db->join('item_order', 'item_order.order_id = order.order_id','left');
$this->db->join('product', 'product.product_number = item_order.item_number','left');
$this->db->join('product_to_image', 'product_to_image.p_id = product.products_id','left');
$this->db->join('product_to_dropbox', 'product_to_dropbox.products_id = product.products_id','left');

$this->db->where('order.user_name', $ebay_user_name);
$this->db->where('order.user_id', $user_id);

$this->db->delete('order');

Answer 1

当使用 Codeigniter 时，您不能使用 Join 执行 DELETE。 see here和 here .

您需要编写一个自然的 SQL。见链接1:

$sql = "DELETE o FROM order as o
  LEFT JOIN item_order ON item_order.order_id = order.order_id
  LEFT JOIN product ON product.product_number = item_order.item_number
  LEFT JOIN product_to_image ON product_to_image.p_id = product.products_id
  LEFT JOIN product_to_dropbox ON product_to_dropbox.products_id = product.products_id

  WHERE order.user_name = ? and order.user_id = ?";
$this->db->query($sql, array($ebay_user_name, $user_id));

或者尝试使用反引号`。但我不确定是否要转义它们:

$sql = "DELETE o FROM `order` as o
  LEFT JOIN `item_order` ON item_order.order_id = order.order_id
  LEFT JOIN `product` ON product.product_number = item_order.item_number
  LEFT JOIN `product_to_image` ON product_to_image.p_id = product.products_id
  LEFT JOIN `product_to_dropbox` ON product_to_dropbox.products_id = product.products_id
WHERE order.user_name = ? and order.user_id = ?";
$this->db->query($sql, array($ebay_user_name, $user_id));