Rails 3 与 PostgreSQL：“update_all”问题在连接表中

发布于 2024-10-31 22:56:03 字数 1044 浏览 0 评论 0原文

在 Rails 3 中，我尝试使用类似 self.children.joins(:parent).where(...).update_all(...) 的语句来选择记录子集并全部更新。这适用于 MySQL，但不适用于 PostgreSQL。 Rails 语法是否错误？

详情

成员属于家庭，家庭有很多成员。所涉及的语句位于 Family 实例方法中，因此“self”是一个家庭：

self.members.joins(:family).
    where('spouse_id > 0 OR child OR members.id = families.head_id').
    update_all("members.residence_location_id = #{self.residence_location_id}")

换句话说，对于该家庭的成员子集，将家庭resident_location 复制到成员residence_location。

这在 MySQL 中运行得非常好。然而，当我更改为 PostgreSQL 时，我收到此错误：

PGError: ERROR:  syntax error at or near "INNER"
 LINE 1: UPDATE "members" INNER JOIN "families" ON "families"."id" = ...

UPDATE "members" INNER JOIN "families" ON "families"."id" = "members"."family_id" 
  SET members.residence_location_id = 27 
  WHERE ("members".family_id = 425) AND 
        (spouse_id > 0 OR child OR members.id = families.head_id)

我的 Rails 语句是否不正确，但恰好在 MySQL 中工作？有没有办法修复它以与 PostgreSQL 一起使用？或者也许是一种使用 MetaWhere 的方法？

原文

In Rails 3, I'm trying to use a statement like self.children.joins(:parent).where(...).update_all(...) to select a subset of records and update them all. This worked using MySQL but is not working with PostgreSQL. Is the Rails syntax wrong?

Details

Members belong to families, families have many members. The statement in question is in a Family instance method, so "self" is a family:

self.members.joins(:family).
    where('spouse_id > 0 OR child OR members.id = families.head_id').
    update_all("members.residence_location_id = #{self.residence_location_id}")

In other words, for a subset of members of this family, copy the family residence_location to the member residence_location.

This worked perfectly well in MySQL. When I changed to PostgreSQL, however, I get this error:

PGError: ERROR:  syntax error at or near "INNER"
 LINE 1: UPDATE "members" INNER JOIN "families" ON "families"."id" = ...

UPDATE "members" INNER JOIN "families" ON "families"."id" = "members"."family_id" 
  SET members.residence_location_id = 27 
  WHERE ("members".family_id = 425) AND 
        (spouse_id > 0 OR child OR members.id = families.head_id)

Is my Rails statement incorrect but just happened to work in MySQL? Is there a way to fix it to work with PostgreSQL? Or perhaps a way to use MetaWhere?

分享到QQ

分享到微博