Why my query returns only 34 records, but there is more than 300 records to return?
Query 1:
2.1.5 :109 > Refinery::BeerBrands::BeerBrand.all.count
(0.5ms) SELECT COUNT(*) FROM `refinery_beer_brands`
=> 34
Query 2:
2.1.5 :106 > Refinery::BeerBrands::BeerBrand.joins(:brewery).count
(0.2ms) SELECT COUNT(*) FROM `refinery_beer_brands` brewery
=> 34
Query 3:
2.1.5 :112 > ::Refinery::Breweries::Brewery.all.count
(0.3ms) SELECT COUNT(*) FROM `refinery_breweries`
=> 303
Query 4:
2.1.5 :111 > ::Refinery::Breweries::Brewery.joins(:beer_brands).count
(1.0ms) SELECT COUNT(*) FROM `refinery_breweries` INNER JOIN `refinery_beer_brands` ON `refinery_beer_brands`.`brewery_id` = `refinery_breweries`.`id`
=> 34
Query 5:
2.1.5 :126 > ::Refinery::Breweries::Brewery.joins(:beer_brands).where(:refinery_beer_brands => { beer_type: [0,1,2,3,4,5] } ).count
(1.1ms) SELECT COUNT(*) FROM `refinery_breweries` INNER JOIN `refinery_beer_brands` ON `refinery_beer_brands`.`brewery_id` = `refinery_breweries`.`id` WHERE `refinery_beer_brands`.`beer_type` IN (0, 1, 2, 3, 4, 5)
=> 34
Why the "query 5" returns only 34 records? All "beer_brands" must have "beer_type" selected, and it is in db number 0-5. I want to return 303 records. Each brewery has many beer_brands.
Brewery model:
has_many :beer_brands, :class_name => '::Refinery::BeerBrands::BeerBrand'
Beer brand model:
belongs_to :brewery,:class_name => '::Refinery::Breweries::Brewery'
How to get all 304 records with similar query? I want to filter records with different array in query.
//edit 16.3.2015:
Ok, you're right... But I have another problem. What about this case: 1) brewery can have beer brands, but it doesn't have to 2) I want fulltext search in brewery title or their related beer brands titles
Now my query looks like:
out.includes(:beer_brands).where("refinery_breweries.title LIKE ? OR refinery_beer_brands.title LIKE ?", "%#{pars[:query]}%", "%#{pars[:query]}%")"
Now it throws error:
Unknown column 'refinery_beer_brands' in 'where clause'
Your query 1 and 2 shows that you have only 34 records in beer brands which means that only few breweries have beer brands and not all 303 and this is why it is returning only 34 records. If you still need all the 303 records use left outer join or right outer join. But you will obviously get null values for the other breweries.