Active Record查询以过滤不重复的记录

huangapple go评论58阅读模式
英文:

Active Record Query to filter without duplicate records

问题

以下是翻译好的部分:

"Could anyone help me to filter the records without duplicates?"

"Here is the PurchaseOrder table with id, job_number, descriptiom and order number fields. If I search a job number 348282227 from this table we can see here line number 2 with id 6678 and 6686 is duplicated with same description and order number. How can I get results without 6686 row."

"以下是PurchaseOrder表格,包括id、job_number、description和order number字段。如果我从这个表格中搜索工作编号348282227,我们可以看到在这里,行号2的id为6678和6686,它们的description和order number是相同的。我如何获取不包括6686行的结果。"

"+-------+------------+------------------------------+--------------+
| id | job_number | description | order_number |
+-------+------------+------------------------------+--------------+
| 6677 | 348282227 | Moisture Inspection 1 | 224201 |
| 6678 | 348282227 | Inspection 3 | 224202 |
| 6679 | 348282227 | Moisture Inspection Final | 224203 |
| 6680 | 348282227 | BFP Certification Inspection | 224204 |
| 6681 | 348282227 | Structural Engineering | 224206 |
| 6682 | 348282227 | Frame Labor 8 | 224218 |
| 6683 | 348282227 | Stucco Turnkey #3 | 224244 |
| 6684 | 348282227 | Retention - Stucco | 224245 |
| 6685 | 348282227 | Roofing Turnkey | 224246 |
| 6686 | 348282227 | Inspection 3 | 224202 |
+-------+------------+------------------------------+--------------+"

"已经尝试了下面的查询,但不起作用。"

"试图使用以下查询,但没有成功。"

uniq_order_numbers = PurchaseOrder.where(job_number: "348282227").pluck(:order_number).uniq
PurchaseOrder.where(order_number: uniq_order_numbers).select(:job_number, :description, :order_number)

"Can anyone help me for this?"

"有人可以帮助我吗?"

英文:

Could anyone help me to filter the records without duplicates

Here is the PurchaseOrder table with id, job_number, descriptiom and order number fields. If I search a job number 348282227 from this table we can see here line number 2 with id 6678 and 6686 is duplicated with same description and order number. How can I get results without 6686 row.

    +-------+------------+------------------------------+--------------+
| id    | job_number | description                  | order_number |
+-------+------------+------------------------------+--------------+
|  6677 | 348282227  | Moisture Inspection 1        | 224201       |
|  6678 | 348282227  | Inspection 3                 | 224202       |
|  6679 | 348282227  | Moisture Inspection Final    | 224203       |
|  6680 | 348282227  | BFP Certification Inspection | 224204       |
|  6681 | 348282227  | Structural Engineering       | 224206       |
|  6682 | 348282227  | Frame Labor 8                | 224218       |
|  6683 | 348282227  | Stucco Turnkey #3            | 224244       |
|  6684 | 348282227  | Retention - Stucco           | 224245       |
|  6685 | 348282227  | Roofing Turnkey              | 224246       |
|  6686 | 348282227  | Inspection 3                 | 224202       |
+-------+------------+------------------------------+--------------+

Tried the below query but not works.

    uniq_order_numbers = PurchaseOrder.where(job_number: "348282227").pluck(:order_number).uniq
    PurchaseOrder.where(order_number: uniq_order_numbers).select(:job_number, :description, :order_number)

Can anyone help me for this.

答案1

得分: 1

你可以按照dbugger的建议选择唯一路线:

https://www.rubyguides.com/2019/08/ruby-uniq-method/

但真正的问题是你的数据库中存在重复数据。你可能不应该允许这种情况发生 - 要么在记录级别,要么(最好的情况下)在数据库级别进行限制。

https://guides.rubyonrails.org/active_record_validations.html#uniqueness

https://stackoverflow.com/questions/1449459/how-do-i-make-a-column-unique-and-index-it-in-a-ruby-on-rails-migration#1449466

英文:

You can go the uniq route, as dbugger suggests:

https://www.rubyguides.com/2019/08/ruby-uniq-method/

But the real issue is that you have duplicate data in your database. You should probably not be permitting that - either at the record level or/also (ideally) at the database level.

https://guides.rubyonrails.org/active_record_validations.html#uniqueness

https://stackoverflow.com/questions/1449459/how-do-i-make-a-column-unique-and-index-it-in-a-ruby-on-rails-migration#1449466

huangapple
  • 本文由 发表于 2023年7月14日 03:27:23
  • 转载请务必保留本文链接:https://go.coder-hub.com/76682662.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定