Databases
StackOverflow2013
Postsdbo
POrails 3 - query generated with joins getting duplicated data
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POrails 3 - query generated with joins getting duplicated data
    text
    copied!<p>I'll be short and to the point. I am trying to join three tables together to get a total of invoiced hours times the invoiced rate from all <strong>non-archived</strong> clients. This is what i currently have in my controller:</p> <pre><code>@total_due = Client.find(:all, :joins =&gt; [:invoices, :invoice_line_items], :select =&gt; "SUM(invoice_line_items.hours * invoice_line_items.rate) as total", :group =&gt; "clients.archive, invoices.paid_status HAVING clients.archive = false AND invoices.paid_status = false") </code></pre> <p>My models look like this:</p> <pre><code># /models/clients.rb class Client &lt; ActiveRecord::Base has_many :invoices has_many :invoice_line_items, :through =&gt; :invoices end # /models/invoices.rb class Client &lt; ActiveRecord::Base belongs_to :client has_many :invoice_line_items end # /models/invoice_line_items.rb class Client &lt; ActiveRecord::Base belongs_to :invoice end </code></pre> <p>So, as you can see from the code in my controller i am trying to get the total of all hours times rate for all invoices related to <strong>non-archived</strong> clients, <em>and</em> they have not paid yet. The above code in my controller is duplicating data somewhere because my number is a lot higher than it should be.</p> <p>The query i am trying to build is this:</p> <pre><code>SELECT SUM(invoice_line_items.hours * invoice_line_items.rate) AS total FROM clients INNER JOIN invoices ON clients.id = invoices.client_id INNER JOIN invoice_line_items ON invoices.id = invoice_line_items.invoice_id GROUP BY clients.archive, invoices.paid_status HAVING clients.archive = false AND invoices.paid_status = false </code></pre> <p>Can someone explain or figure out why i am getting duplicated data in my rails query? I have a feeling it is just joining the tables differently than i am expecting. I am still fairly new to rails as well. Thanks!</p> <p>UPDATE: This is the query that rails is generating:</p> <pre><code>select sum(invoice_line_items.hours * invoice_line_items.rate) as total from clients inner join invoices on invoices.client_id = clients.id inner join invoices invoices_clients_join on invoices_clients_join.client_id = clients.id inner join invoice_line_items on invoice_line_items.invoice_id = invoices_clients_join.id group by clients.archive, invoices.paid_status having clients.archive = false and invoices.paid_status = false </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload