# Variable won't work when joining 2 tables

**URL:** <https://www.ruby-forum.com/t/variable-wont-work-when-joining-2-tables/152968>\
**Category:** Rails\
**Created:** [November 10, 2008, 5:20pm UTC](https://www.ruby-forum.com/t/variable-wont-work-when-joining-2-tables/152968 "2008-11-10T17:20:15Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![gabi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/f07891/32.png) [@gabi](https://www.ruby-forum.com/u/gabi)\
**Post date:** [November 10, 2008, 5:20pm UTC](https://www.ruby-forum.com/t/variable-wont-work-when-joining-2-tables/152968/1 "2008-11-10T17:20:15Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![gabi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/f07891/32.png) [@gabi](https://www.ruby-forum.com/u/gabi)\
**Post date:** [November 10, 2008, 6:01pm UTC](https://www.ruby-forum.com/t/variable-wont-work-when-joining-2-tables/152968/2 "2008-11-10T18:01:55Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![gabi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/f07891/32.png) [@gabi](https://www.ruby-forum.com/u/gabi)\
**Post date:** [November 10, 2008, 6:51pm UTC](https://www.ruby-forum.com/t/variable-wont-work-when-joining-2-tables/152968/3 "2008-11-10T18:51:53Z")

</div>

I found attr\_internal\_accessor in ActiveSupport.  
[http://apidock.com/rails/Module/attr\_internal\_accessor](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?

---

<div class="post-metadata">

**Author:** ![gabi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/f07891/32.png) [@gabi](https://www.ruby-forum.com/u/gabi)\
**Post date:** [November 10, 2008, 7:26pm UTC](https://www.ruby-forum.com/t/variable-wont-work-when-joining-2-tables/152968/4 "2008-11-10T19:26:44Z")

</div>

On 10 Nov 2008, at 17:51, Gabi Ge [removed\_email\_address@domain.invalid](mailto: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

---

<div class="post-metadata">

**Author:** ![gabi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/f07891/32.png) [@gabi](https://www.ruby-forum.com/u/gabi)\
**Post date:** [November 10, 2008, 7:50pm UTC](https://www.ruby-forum.com/t/variable-wont-work-when-joining-2-tables/152968/5 "2008-11-10T19:50:49Z")

</div>

On Nov 10, 6:33 pm, Gabi Ge [removed\_email\_address@domain.invalid](mailto: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

---

<div class="post-metadata">

**Author:** ![gabi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/f07891/32.png) [@gabi](https://www.ruby-forum.com/u/gabi)\
**Post date:** [November 10, 2008, 7:33pm UTC](https://www.ruby-forum.com/t/variable-wont-work-when-joining-2-tables/152968/6 "2008-11-10T19:33:51Z")

</div>

Frederick C. wrote:

> On 10 Nov 2008, at 17:51, Gabi Ge [removed\_email\_address@domain.invalid](mailto: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

---

<div class="post-metadata">

**Author:** ![gabi](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/f07891/32.png) [@gabi](https://www.ruby-forum.com/u/gabi)\
**Post date:** [November 10, 2008, 8:05pm UTC](https://www.ruby-forum.com/t/variable-wont-work-when-joining-2-tables/152968/7 "2008-11-10T20:05:29Z")

</div>

Frederick C. wrote:

> On Nov 10, 6:33�pm, Gabi Ge [removed\_email\_address@domain.invalid](mailto: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!