some advanced rails where queries

advanced_rails_sql.rb

#Query for getting objects with association count of 0
Spree::Order.joins('LEFT OUTER JOIN spree_line_items ON spree_orders.id = spree_line_items.order_id').group("spree_orders.id").having("count(spree_line_items.id) = ?",0)
#Query for getting objects with association count greater than a number
ShopkeepOrder.joins('LEFT OUTER JOIN shopkeep_order_rows ON shopkeep_orders.id = shopkeep_order_rows.shopkeep_order_id').group("shopkeep_orders.id").having("count(shopkeep_order_rows.id) > ?",50)

#Query for getting objects with non 0 associations
Spree::Order.where("state != ? AND (payment_state IS NULL OR payment_state != ?) AND email is NOT NULL AND abandoned_email_sent_at IS NULL AND spree_orders.updated_at < ?", "complete", "paid", (Time.zone.now - 12.hours)).joins(:line_items).distinct


#Orders belonging to a certain category falling in a certain week
q = {
  line_items_product_taxons_name_cont: 'mascara',
  state_eq: 'complete'
}
Spree::Order.ransack(q).result.where('extract(week from completed_at) = (extract(week from CURRENT_TIMESTAMP) - 8)')