开发者

Rails 3 with PostgreSQL: Problem with 'update_all' in joined table

In Rails 3, I'm trying to use a statement like self.children.joins(:parent).where(...).update_all(...) to select a subset of records开发者_StackOverflow 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?


FYI, this issue was probably caused by this, now resolved in Rails 3.1, issue: https://github.com/rails/rails/issues/522

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜