Menu
I have a one-to-many relationship between Product and ProductCategory.
How do I query all productcategories that have at least one product associated to them?
class Product < ActiveRecord::Base
belongs_to :product_category
end
class ProductCategory < ActiveRecord::Base
has_many :products
end
372
Below is the syntax to filter the rows without a null value in a specified column. Syntax: SELECT * FROM <table_name> WHERE <column_name> IS NOT NULL; Example: SELECT * FROM demo_orders WHERE ORDER_DATE IS NOT NULL; --Will output the rows consisting of non null order_date values.
SELECT * FROM yourTableName WHERE yourSpecificColumnName IS NULL OR yourSpecificColumnName = ' '; The IS NULL constraint can be used whenever the column is empty and the symbol ( ' ') is used when there is empty value.
SELECT column_names FROM table_name WHERE column_name IS NOT NULL; Query: SELECT * FROM Student WHERE Name IS NOT NULL AND Department IS NOT NULL AND Roll_No IS NOT NULL; To exclude the null values from all the columns we used AND operator.
To display records without NULL in a column, use the operator IS NOT NULL. You only need the name of the column (or an expression) and the operator IS NOT NULL (in our example, the price IS NOT NULL ). Put this condition in the WHERE clause (in our example, WHERE price IS NOT NULL ), which filters rows.
ProductCategory.all(
:joins => :products,
:select => "product_categories.*, count(products.id) as prod_count",
:group => "product_categories.id"
)
I found out how to solve this thanks to the great Ryan Bates on this screencast: http://railscasts.com/episodes/181-include-vs-joins
124
ProductCategory.includes(:products).where('products.id is not null').all
39
Joins makes an inner join, so the where clause in some of the other answers is superfluous. Grouping by products.id as some others do will repeat the category when the category has multiple products, grouping by the ProductCategory will eliminate dups.
ProductCategory.joins(:products).group('product_categories.id')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
