Forum: Ruby on Rails variable won't work when joining 2 tables

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Gabi G. (Guest)
on 2008-11-10 18:20
I'm trying to find a solution for the following problem:

I have to list 3 things - companies' names, total amount each company
owes, total number of containers that belong to each company - using
given invoices that still have a balance (they are outstanding). All
this in a given period of time (filter).

An invoice looks like this:

class Invoice < ActiveRecord::Base
  has_many :line_items, :dependent => :destroy
  belongs_to :company

and it has company_id, amount and balance. An invoice has 1 or many line
items, each line item stands for a container:

class LineItem < ActiveRecord::Base
  belongs_to :invoice
  belongs_to :container

class Container < ActiveRecord::Base
  has_many :line_items

Fetching the company's name and calculating the total amount owed per
company has worked so far, but I'm having a problem with adding up the
containers. If I could count the line items, that would result in
containers' number. To do so, I've created named scopes:

  named_scope :filter, lambda { |invoice_filter| {:conditions =>
invoice_filter.sql_conditions}}
  named_scope :outstanding, :conditions => ["invoices.balance != ?", 0]
  named_scope :totals, :include => :line_items,
    :select => 'sum(invoices.amount) as amount, company_id, count(*) as
containers_count',
    :joins => :line_items,
    :group => 'invoices.company_id'

:filter and :outstanding refer to invoices that haven't yet been payed
(so they have a balance) in a certain period of time (from, to). :totals
is the one grouping the companies by id, while summarizing their
invoices' amount and counting the line items as containers_count
(attr_accessor :containers_count).

The controller is:

@invoice_filter = InvoiceFilter.new(params[:filter])
@invoices = Invoice.filter(@invoice_filter).outstanding.totals

and the view:

<% for invoice in @invoices do %>
  <%= invoice.containers_count %>

The problem is that containers_count doesn't get a value, hence nothing
is shown in the 3rd column. I'm assuming that amount works because
invoice has an amount field, but it doesn't have a containers_count
field. Any idea on how the :totals named scope might be improved? Or is
there another solution for this?
Gabi G. (Guest)
on 2008-11-10 19:01
I'm gonna try to be more concise:

I do a find(:group => "some_column", :select => "count(*) as
some_column_count").


I did declare in the model "attr_accessor :some_column_count", but the
some_column_count comes up as nil, even though the SQL query yields
results.

I suppose the column is nil because is not a real active record
attribute. I added some_column_count as a column in the table and the
assign from the group find works, but I find the solutin ugly.

 Is there a way to add an activerecord attribute in the model behaves
the same as a normal one, only that it doesn't map to a real column?


Gabi Ge wrote:
> I'm trying to find a solution for the following problem:
>
> I have to list 3 things - companies' names, total amount each company
> owes, total number of containers that belong to each company - using
> given invoices that still have a balance (they are outstanding). All
> this in a given period of time (filter).
>
> An invoice looks like this:
>
> class Invoice < ActiveRecord::Base
>   has_many :line_items, :dependent => :destroy
>   belongs_to :company
>
> and it has company_id, amount and balance. An invoice has 1 or many line
> items, each line item stands for a container:
>
> class LineItem < ActiveRecord::Base
>   belongs_to :invoice
>   belongs_to :container
>
> class Container < ActiveRecord::Base
>   has_many :line_items
>
> Fetching the company's name and calculating the total amount owed per
> company has worked so far, but I'm having a problem with adding up the
> containers. If I could count the line items, that would result in
> containers' number. To do so, I've created named scopes:
>
>   named_scope :filter, lambda { |invoice_filter| {:conditions =>
> invoice_filter.sql_conditions}}
>   named_scope :outstanding, :conditions => ["invoices.balance != ?", 0]
>   named_scope :totals, :include => :line_items,
>     :select => 'sum(invoices.amount) as amount, company_id, count(*) as
> containers_count',
>     :joins => :line_items,
>     :group => 'invoices.company_id'
>
> :filter and :outstanding refer to invoices that haven't yet been payed
> (so they have a balance) in a certain period of time (from, to). :totals
> is the one grouping the companies by id, while summarizing their
> invoices' amount and counting the line items as containers_count
> (attr_accessor :containers_count).
>
> The controller is:
>
> @invoice_filter = InvoiceFilter.new(params[:filter])
> @invoices = Invoice.filter(@invoice_filter).outstanding.totals
>
> and the view:
>
> <% for invoice in @invoices do %>
>   <%= invoice.containers_count %>
>
> The problem is that containers_count doesn't get a value, hence nothing
> is shown in the 3rd column. I'm assuming that amount works because
> invoice has an amount field, but it doesn't have a containers_count
> field. Any idea on how the :totals named scope might be improved? Or is
> there another solution for this?
Gabi G. (Guest)
on 2008-11-10 19:51
I found attr_internal_accessor in ActiveSupport.
http://apidock.com/rails/Module/attr_internal_accessor

I put in the invoice class
attr_internal_accessor :containers_count

Still no help:
>> Invoice.find(:first, :select => '345 as company_id')
=> #<Invoice company_id: 345>
>> Invoice.find(:first, :select => '345 as containers_count')
=> #<Invoice >

I want to have containers_count from the select to be put in the
attribute containers_count in the class.

Gabi Ge wrote:
>
> I do a find(:group => "some_column", :select => "count(*) as
> some_column_count").
>
>
> I did declare in the model "attr_accessor :some_column_count", but the
> some_column_count comes up as nil, even though the SQL query yields
> results.
>
> I suppose the column is nil because is not a real active record
> attribute. I added some_column_count as a column in the table and the
> assign from the group find works, but I find the solutin ugly.
>
>  Is there a way to add an activerecord attribute in the model behaves
> the same as a normal one, only that it doesn't map to a real column?
>
>
Frederick C. (Guest)
on 2008-11-10 20:26
(Received via mailing list)
On 10 Nov 2008, at 17:51, Gabi Ge <removed_email_address@domain.invalid>
wrote:

>>> Invoice.find(:first, :select => '345 as containers_count')
> => #<Invoice >
>
This display (ie the output of inspect) will never show attributes
other the columns on the table. If you want to see whether a method is
there just call it.

Fred
Gabi G. (Guest)
on 2008-11-10 20:33
Frederick C. wrote:
> On 10 Nov 2008, at 17:51, Gabi Ge <removed_email_address@domain.invalid>
> wrote:
>
>>>> Invoice.find(:first, :select => '345 as containers_count')
>> => #<Invoice >
>>
> This display (ie the output of inspect) will never show attributes
> other the columns on the table. If you want to see whether a method is
> there just call it.

It makes no difference:
>> Invoice.find(:first, :select => '345 as containers_count').containers_count
=> nil
>> Invoice.find(:first, :select => '345 as id').id
=> 345
Frederick C. (Guest)
on 2008-11-10 20:50
(Received via mailing list)
On Nov 10, 6:33 pm, Gabi Ge <removed_email_address@domain.invalid> wrote:
>
> It makes no difference:>> Invoice.find(:first, :select => '345 as 
containers_count').containers_count
> => nil
If you've still got an attr_accessor for containers_count or anything
like that, this will stop the above from working.

Fred
Gabi G. (Guest)
on 2008-11-10 21:05
Frederick C. wrote:
> On Nov 10, 6:33�pm, Gabi Ge <removed_email_address@domain.invalid> wrote:
>>
>> It makes no difference:>> Invoice.find(:first, :select => '345 as 
containers_count').containers_count
>> => nil
> If you've still got an attr_accessor for containers_count or anything
> like that, this will stop the above from working.

Thanks a lot! It works. Really amazing,

I think that attr_accessor was probably overwriting the actual methods
what included write_attribute and read_attribute.

Thank you again!
This topic is locked and can not be replied to.