ruby-on-rails 如何从连接表中正确选择字段?

kadbb459  于 2022-12-01  发布在  Ruby
关注(0)|答案(5)|浏览(177)

这是我的简化模型,我有连接模型,
样板房

has_many :towers


模型塔

belong_to :apartment

然后我尝试在controller中连接这两个表,也尝试在rails控制台中连接,如下所示:
公寓.joins(:towers).select('公寓. id','公寓.名称','塔楼. id','塔楼.名称')
问题是上面查询只返回
apartments.id 和apartments.name
我也试过用这样的别名,还是不行
公寓.joins(:towers).select('公寓. id','公寓. name','塔楼. id作为塔楼id','塔楼. name作为塔楼名称')
我已经确认,所有塔有一个公寓,我知道我可以这样做,以获得1记录
公寓.joins(:towers).select('公寓. id','公寓.名称','塔楼. id','塔楼.名称').first.towers.id
等,但我需要所有的记录和所有这些领域,请意见。
下面是我在rails控制台中得到的最新结果:

Apt Load (1.0ms)  SELECT apts.id, apts.apt_name, towers.id as towerid, towers.
    tower_name as towername FROM `apts` INNER JOIN `towers` ON `towers`.`apt_id` = `
    apts`.`id`
    => #<ActiveRecord::Relation [#<Apt id: 5, apt_name: "basura">, #<Apt id: 5, apt_
    name: "basura">, #<Apt id: 124, apt_name: "hydra">, #<Apt id: 124, apt_name: "hy
    dra">, #<Apt id: 126, apt_name: "mediterania">, #<Apt id: 126, apt_name: "mediterania">, #<Apt id: 142, apt_name: "apartement gajah mada">, #<Apt id: 142, apt_name: "apartement gajah mada">]>

正如你所看到的,上面的查询只返回2个字段,我需要的结果是这样的:

#<Apt id: 126, apt_name: "mediterania", tower_id: 12, tower_name: "tower A">, 
#<Apt id: 126, apt_name: "mediterania", tower_id: 15, tower_name: "tower F">

等等...

vyswwuz2

vyswwuz21#

我认为唯一可行的方法是使用as

q = Apartment.joins(:towers).select('apartments.id, apartments.name, towers.id as t_id, towers.name as t_name')

q.first.t_id
q.first.t_name

为什么first.towers.id无法运行?

apartment.towers将返回ActiveRecord::Associations::CollectionProxy。您可以将其视为塔的集合。在SQL查询中,您将引用towers表。但当您运行www.example.com时apartment.towers.id,您将调用CollectionProxy对象上的id,该对象将不起作用。您可以使用towers.first获取第一个塔。
关于

Apt Load (1.0ms)  SELECT apts.id, apts.apt_name, towers.id as towerid, towers.
    tower_name as towername FROM `apts` INNER JOIN `towers` ON `towers`.`apt_id` = `
    apts`.`id`
    => #<ActiveRecord::Relation [#<Apt id: 5, apt_name: "basura">, #<Apt id: 5, apt_
    name: "basura">, #<Apt id: 124, apt_name: "hydra">, #<Apt id: 124, apt_name: "hy
    dra">, #<Apt id: 126, apt_name: "mediterania">, #<Apt id: 126, apt_name: "mediterania">, #<Apt id: 142, apt_name: "apartement gajah mada">, #<Apt id: 142, apt_name: "apartement gajah mada">]>

**您在控制台中看到的是inspsect方法返回的结果。inspect方法不用于显示非列属性。因此,即使内存中有towername,它也只会显示公寓模型的列属性。**有关inspect的详细信息

我还建议您尝试以下操作:

Apartment.joins(:towers).pluck('apartments.id, apartments.name, towers.id as t_id, towers.name as t_name')

上面的语句将获取数组中的所有数据。结果与select相同,但select不会加载数组中的所有数据。

vsikbqxv

vsikbqxv2#

您应该使用

Apartment.joins(:towers).select('apartments.id, apartments.name, towers.id , towers.name')

即单个字符串中的所有列名。
请参阅此。

5anewei6

5anewei63#

您可以尝试以下别名

Apartment.joins(:towers).select('apartments.id as apartment_id, apartments.name as apartment_name, towers.id as tower_id , towers.name as tower_name)
au9on6nz

au9on6nz4#

你可以试试这个
公寓.joins(:towers).select('apartments.id作为标识,apartments.name作为公寓名称,towers.id作为塔楼标识,towers.name作为塔楼名称)
您将得到如下响应#ActiveRecord::Relation [#〈Apt id:126,公寓名称:“地中海”,塔标识:12、铁塔_名称:“A塔”〉,#〈公寓标识:126,公寓名称:“地中海”,塔标识:15、铁塔_名称:“F座”〉]〉

pw9qyyiw

pw9qyyiw5#

Rails 7adds支持此类查询,现在select接受哈希
您可以使用如下别名

Apartment
  .joins(:towers)
  .select(
    apartments: { id: :apartment_id, name: :apartment_name },
    towers: { id: :tower_id, name: :tower_name }
  )

它产生这样的查询

SELECT apartments.id AS apartment_id, apartments.name AS apartments_name,
  towers.id AS tower_id, towers.name AS tower_name
FROM apartments
INNER JOIN towers ON towers.apartment_id = apartments.id

相关问题