CODEWITHSUNDEEP
ruby-on-rails
catmal
catmal

Reputation: 1758

Rails not displaying rows on which association value is nil

I have a controller stockdiaries

 Ticketline.joins(:ticket, :product => :location)

ticketline belongs to product and product belongs to location.

Ticketlines table has a column PRODUCT, products table has a column LOCATION.

A product can have value in LOCATION column nil.

If I list all ticketlines in a table in view, ticketlines containing a product with location nil are not shown. The entire row.

While I'd expect to show the row and perhaps if I call:

ticketline.product.location.try(:NAME)

just show empty value in that column.

If I remove from my controller :product => :location it shows all rows.

Values in database are:

TICKET, LINE, PRODUCT, ATTRIBUTESETINSTANCE_ID, UNITS, PRICE, TAXID, ATTRIBUTES, PRODUCT_COST, PRICESELL, TAXES, SERVICES, SERVICES_RATE, HISTORY_ID
    'd0d82dbb-f6eb-41b3-a03e-37846e0e011a', 0, '130', '', 1, 850000, '001', '', 350000, 850000, 85000, 0, 0, 0
    'ec586062-af32-423e-82f7-0de1da9cffdd', 0, '121', '', 1, 1600000, '001', '', 650000, 1600000, 160000, 0, 0, 0

In this case there are 2 lines, one contains a product for which location is nil. That row is not shown.

How can I keep the join :product => :location (as I need to get data from location table for each product) while displying all records, also those with a product that has nil as value on LOCATION column?

Upvotes: 0

Views: 51

Answers (2)

Shabini Rajadas
Shabini Rajadas

Reputation: 761

Ticketline.joins(:ticket).joins("LEFT JOIN products ON tickelines.product_id = products.id").joins("LEFT JOIN locations ON products.location_id = locations.id")

The above query will INNER JOIN the ticket and LEFT JOIN products and locations. This will produce the needed results.

Upvotes: 1

Rajdeep Singh
Rajdeep Singh

Reputation: 17834

joins() function by default work as INNER JOIN that's why it is giving nil values, you need to do LEFT JOIN instead.

 Ticketline.joins("LEFT JOIN tickets ON ticketlines.ticket_id = tickets.id LEFT JOIN products ON tickelines.product_id = products.id LEFT JOIN locations ON products.location_id = locations.id")

Hope that helps!

Upvotes: 1

Related Questions