logo
down
shadow

How to find only Grandparent records where all grandchildren match some criteria in Rails?


How to find only Grandparent records where all grandchildren match some criteria in Rails?

By : user2185292
Date : November 21 2020, 04:01 AM
I think the issue was by ths following , You can easily do it with left joins.
I will show you how to get to the most activerecord-like code, starting from raw SQL, fixing it on the fly and increasing more and more the abstraction level. This might probably help you in some other similar task in the future.
code :
SELECT `projects`.`*`, 
       `tasks`.`*`, 
       `todo_items`.`*`
FROM `projects`
LEFT OUTER JOIN `tasks`
  ON `tasks`.`project_id` = `projects`.`id
LEFT OUTER JOIN `todo_items`
  ON `todo_items`.`task_id` = `tasks`.`id
  AND NOT `todo_items`.`done`
WHERE `todo_items`.`id` IS NULL
;
(p1, NULL, NULL)        -- project with no tasks
(p2, t2_1, NULL)        -- project with three tasks, first is complete
(p2, t2_2, todo2_2_1)   -- this task has one pending todo
(p2, t2_3, todo2_3_1)   -- this task has two pending todos
(p2, t2_3, todo2_3_2)
(p3, t3_1, NULL)        -- project with two complete tasks
(p3, t3_2, NULL)
...
SELECT `projects`.`*`,
       COUNT(`todo_items`.`id`) AS `pending_todo_count`
FROM `projects`
LEFT OUTER JOIN `tasks`
  ON `tasks`.`project_id` = `projects`.`id
LEFT OUTER JOIN `todo_items`
  ON `todo_items`.`task_id` = `tasks`.`id
  AND NOT `todo_items`.`done`
GROUP BY `projects`.`*`
;
(p1, 0)        -- project with no tasks
(p2, 3)        -- project with three tasks, first is complete
(p3, 0)        -- project with two complete tasks
...
SELECT `projects`.`*`
FROM `projects`
LEFT OUTER JOIN `tasks`
  ON `tasks`.`project_id` = `projects`.`id
LEFT OUTER JOIN `todo_items`
  ON `todo_items`.`task_id` = `tasks`.`id
  AND NOT `todo_items`.`done`
GROUP BY `projects`.`*`
HAVING COUNT(`todo_items`.`id`) = 0
;
(p1)
(p3)
class Task
  has_many :pending_todo_items, -> { where(done: false) }, class_name: 'TodoItem'
end


Project.
  left_joins(tasks: :pending_todo_items).
  group(Project.arel_table[:id]).
  having(TodoItem.arel_table[:id].count.eq(0))
Project.
  left_joins(tasks: :pending_todo_items).
  group('projects.id').
  having('COUNT(todo_items.id) = 0')
Project.
  joins('LEFT OUTER JOIN .........').
  group('projects.id').
  having('COUNT(todo_items.id) = 0')
SELECT `projects`.`*`
FROM `projects`
LEFT OUTER JOIN `tasks`
  ON `tasks`.`project_id` = `projects`.`id
LEFT OUTER JOIN `todo_items`
  ON `todo_items`.`task_id` = `tasks`.`id
GROUP BY `projects`.`*`
HAVING COUNT(NULLIF(`todo_items`.`done`, 1)) = 0
;
module Arel::Predications
  def null_if(other)
    Arel::Nodes::NamedFunction.new('NULLIF', [self, other])
  end
end

Project.
  left_joins(tasks: :todo_items).
  group(Project.arel_table[:id]).
  having(TodoItem.arel_table[:done].null_if(true).count.eq(0))


Share : facebook icon twitter icon
Find group of records that match a certain criteria

Find group of records that match a certain criteria


By : Passport Appointment
Date : March 29 2020, 07:55 AM
wish helps you I have the following data:
code :
SELECT
  ID,
  ParentID,
  DataValue
FROM
  MyTable
WHERE
  NOT EXISTS (
    SELECT 1 
      FROM MyTable i
     WHERE i.ParentId = MyTable.ParentId AND i.DataValue = 'A'
  )
How to find ANY records that match a criteria

How to find ANY records that match a criteria


By : Ash Money
Date : March 29 2020, 07:55 AM
Does that help I have a ruby (1.9.3) on rails (3.1) app where: , the SQL equivalent of ActiveSupport#blank? is:
code :
column_name IS NULL OR column_name = ''
@list = List.find(params[:id])
@tasks = @list.tasks.where("planned_for IS NULL OR planned_for = '' OR mins IS NULL OR mins = ''")
<% if @tasks.any? %>
  <% #do some stuff %>
<% else %>
<% end %>
Rails scope to return records where ALL associations match criteria

Rails scope to return records where ALL associations match criteria


By : محمود الحوراني
Date : March 29 2020, 07:55 AM
it should still fix some issue Assuming you're using ActiveRecord, you can achieve this by finding all products and removing products in the issue. That would normally result in an array, so in the code below, I did one additional database query to have it return a scoped result so you can cascade other "where" clauses to the result.
code :
class Product < ActiveRecord::Base
  has_many :listings
  scope :not_listed_in, lambda { |issue|
    id_list = Product.pluck(:id) - issue.products.pluck(:id)
    Product.where(id:id_list)
  }
end

class Listing < ActiveRecord::Base
  belongs_to :product
  belongs_to :issue
end

class Issue < ActiveRecord::Base
  has_many :listings
  has_many :products, through: :listings
end
SQL Query to Find Matching Records but Excluding those that Match Second Criteria

SQL Query to Find Matching Records but Excluding those that Match Second Criteria


By : Oscar Aguilera
Date : March 29 2020, 07:55 AM
wish helps you I have a SQL database and I want to locate all the records that match one criteria, but exclude from that set of results the records that match a second criteria. More specifically - , Give a try with below query:
code :
(SELECT d.DocID, s.SignID
FROM Documents AS d)
temp1
INNER JOIN Signatures AS s ON d.SignID = s.SignID
WHERE s.SignatoryName = 'Search Name Here' and d.DOCID not in ( select DocID from temp1)
SQLAlchemy query to find grandchildren filtered by parent and grandparent

SQLAlchemy query to find grandchildren filtered by parent and grandparent


By : loic san
Date : March 29 2020, 07:55 AM
I hope this helps . Indeed, when you collect all the Tags for the articles returned from your query, there will be a separate SQL issued for each article, which might be slow.
Option-1: One way around it would be to eagerly load all the tags during the original query by using:
code :
q = db.session.query(Article)
q = q.join(Article.project)
q = q.filter(Project.id == id)
q = q.filter(Article.date_created.between(now-timedelta(hours=1), now))
#q = q.options(joinedload(Article.tags)) # @new: load Tag immediatelly
q = q.options(subqueryload(Article.tags)) # @new: load Tag on first access (in the line where tags are collected)
articles = q.all()
tags = [tag for article in articles for tag in article.tags]
q = db.session.query(Tag, func.count('*').label("cnt")) #@new: added COUNT
q = q.join(Tag.article)
q = q.join(Article.project)
q = q.filter(Project.id == project_id)
q = q.filter(Article.date_created.between(now-timedelta(hours=1), now))
q = q.group_by(Tag) #@new:
tags = q.all()
return tags # @note: the result is a list of tuples: (Tag, cnt)
q = db.session.query(Tag.text) # @new:modified
q = q.join(Tag.article)
q = q.join(Article.project)
q = q.filter(Project.id == project_id)
q = q.filter(Article.date_created.between(now-timedelta(hours=1), now))
tags = q.all()
return tags # @note: the result is a list of tuples: (tag_name,)
Related Posts Related Posts :
  • JSON field into CSV
  • How can display json data two models in Rails index page?
  • If I shorten the length of each key name in a hash, will that shorten the read/write time of that hash?
  • cant git push heroku master after i tried to change one heroku domain to another heroku domain | ruby on rails app
  • Querying for values from several models
  • How to connect into Action Cable websocket from Arduino?
  • Cleanest way to query polymorphic association?
  • RAILS_ENV, NODE_ENV independent Docker image
  • SQS + Shoryuken: Large Receive Count in FIFO despite auto_delete=true
  • Summernote on Rails Production broken
  • "path_for" ActiveStorage attachments on S3
  • Undefined method `assistants' for ActiveRecord_Relation - Ruby on rails
  • last element of the hash
  • PayPal Express return configuration produces couldn't find Order without an ID
  • Rails Sidekiq - not connecting to Redis
  • Create associated model at the same time as Devise model
  • No route matches [GET] "/oauth/applications"
  • rails default scope with two values
  • uninitialized constant - RoR
  • ForbiddenAttributesError in ruby on rails when update_attributes use
  • Active Storage - Adding File Description / Text - Ruby on Rails 5.2
  • How to use Rails (outside docker) with Postgres (inside docker)?
  • Datagrid Rails : Hide header
  • Reverse a String in Ruby by Reading Backwards
  • How to pass id to an action in the controller - Ruby on Rails
  • Michael Hartl's Rails tutorial chapter 12 — undefined method `users'
  • Ruby on Rails - Linking a user and product controller together, is not working
  • Route constraints: raising exception when there's no match
  • Pass variable from content to layout in Nanoc using Slim
  • How to debug AWS S3 "Access Denied"
  • NoMethodError (undefined method `destroy' for nil:NilClass): even though it is defined
  • Rails template error implicit conversion string to string Heroku
  • Include resize in image_tag with id
  • Redirect to new view with submit_tag in Rails
  • Rails app in EC2 is not reachable but Python is
  • trying to display JSON with 422 error Rails React
  • Nested forms and automatic creation of parent, children
  • Support for multiple domains/subdomains in Rails
  • Model inheritance, the Factory pattern, and self-parsing in Ruby-on-Rails
  • has_many at two levels
  • regular expression to remove substrings delimited by matching double braces
  • What are your crowdsource translation management system recommendations?
  • Show selected name instead of ID in rails index view
  • Rails global content_for
  • rails complex form and ordering with build
  • Given a date, how can I efficiently calculate the next date in a given sequence (weekly, monthly, annually)?
  • What parts of a Ruby-on-Rails application (with reasonably expressive unit tests) should have RDoc?
  • Where to put methods that do simple date formatting
  • What are common ways of implementing web API request throttling/rate-limiting?
  • Linking new users signed in via Facebook connect to existing accounts
  • Rails check if IRB console or webpage
  • As of 2010, what good Rails-based content management systems are there (Typo?)?
  • How to test custom helper in Rails?
  • How can i include image into CSV
  • Rails.cache.fetch, Symbols, & Memcached
  • Polymorphic Join Table in Rails?
  • Returning HTML in the JS portion of a respond_to block throws errors in IE
  • Strange variable-argument problem in Capistrano Task
  • How to specify :primary_key starting value in rails schema migration?
  • Rails3 and Paperclip
  • shadow
    Privacy Policy - Terms - Contact Us © bighow.org