Variable won't work when joining 2 tables


#1

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?


#2

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?


#3

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’)
=> #

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?


#4

On 10 Nov 2008, at 17:51, Gabi Ge removed_email_address@domain.invalid
wrote:

Invoice.find(:first, :select => ‘345 as containers_count’)
=> #

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


#5

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


#6

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’)
=> #

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


#7

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!