CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecord
Selim Alawwa
Selim Alawwa

Reputation: 762

Rails, Count of Users Grouped by created_at day - PostgreSQL

I am using Rails 5, PostgreSQL. I need to get count of users grouped by created_at day, using postgres DATE_TRUNC. The conditions are users created within a date range and have orders within the same date range.

Below is my code which result in an AmbiguousFunction error

Spree::User.joins(:orders)
           .where(spree_orders: { completed_at: params[:start_date]..params[:end_date] })
           .order("DATE_TRUNC('day', 'created_at')")
           .group("DATE_TRUNC('day', 'created_at')")
           .count

The params of start_date and end_date are as follow:

params[:end_date] = Time.current.end_of_day
params[:start_date] = (Time.current - 200.days).beginning_of_day

I get the following error

ActiveRecord::StatementInvalid: PG::AmbiguousFunction: ERROR: function date_trunc(unknown, unknown) is not unique

and even when I explicitly write spree_users.created_at I get the same error.

Is there a better way to achieve the required or a solution for this error?

Upvotes: 0

Views: 902

Answers (2)

Anuj Khandelwal
Anuj Khandelwal

Reputation: 1244

ActiveRecord::StatementInvalid: PG::AmbiguousFunction

This error occurs when our query contains a column name, which may belong to more than one table. For example, we have two tables, User and Company, and both of them have a column called name. Now the following query would raise an error similar to the one that you are facing:

User.joins(:companies).where("name = ABC")

This happens because we do not specify which table to search the name in. Hence, ActiveRecord gets confused and cannot create a unique query.
In the case mentioned above, the error can be resolved simply by prepending spree_users to the created_at column name used in the order and group queries:

Spree::User.joins(:orders)
       .where(spree_orders: { completed_at: params[:start_date]..params[:end_date] })
       .order("DATE_TRUNC('day', 'spree_users.created_at')")
       .group("DATE_TRUNC('day', 'spree_users.created_at')")
       .count

Upvotes: 1

widjajayd
widjajayd

Reputation: 6253

I think you can use date function from sql to get date of timestamp field, and since table User and SpreeOrder has created_at field, you should inform table name (spree_orders.created_at)

Spree::User.joins(:orders)
  .where(spree_orders: { completed_at: params[:start_date]..params[:end_date]})
  .order("date(spree_orders.created_at)")
  .group("date(spree_orders.created_at)")
  .count

Upvotes: 1

Related Questions