Find Where Associated Records Exist

Find where associated records exist

You can use .joins

Employee.joins(:tags)

The SQL this generates contains and INNER JOIN on the tags table, omitting employees table records who have no associated tags record.

ActiveRecord, find all records where associated record EITHER does not exist OR exists with nil status

Don't be afraid to use SQL. It is a great tool!

Logistic.
  joins("LEFT OUTER JOIN type_logistics ON type_logistics.logistic_id = logistics.id").
  joins("LEFT OUTER JOIN rental_requests ON type_logistics.rental_request_id = rental_requests.id").
  where("rental_requests.id IS NULL OR rental_requests.status_id IS NULL")

If you must avoid SQL:

It is possible to generate the same query as above using just Ruby code (no SQL snippets). For example you could use ARel, such as is suggested in this SO discussion.

ARel is great for constructing queries at a high level of abstraction. For the majority of cases I've encountered it is much simpler to use SQL snippets than ARel. Choose whichever is best for your use case.

Rails - only find records where has_many associated records are present

Rails 4

Parent.includes(:child).where.not(children: {id: nil})

or

Parent.joins(:child).distinct

Rails 3

Parent.joins(:child).distinct

Find records with missing associated records in Rails

Purchase.left_outer_joins(:product).where(products: {id: nil})

Note rails 5 have left_outer_joins

so here basically it will result all purchases who does not have associated product.

Finding all records that has at least one association from associated models (tables)

I'd use such query:

Tree.left_joins(:fruits, :flowers).where('fruits.id IS NOT NULL OR flowers.id IS NOT NULL').distinct

it will produce this SQL:

SELECT DISTINCT "trees".* FROM "trees" LEFT OUTER JOIN "fruits" ON "fruits"."tree_id" = "trees"."id" LEFT OUTER JOIN "flowers" ON "flowers"."tree_id" = "trees"."id" WHERE (fruits.id IS NOT NULL OR flowers.id IS NOT NULL)

Want to find records with no associated records in Rails

This is still pretty close to SQL, but it should get everyone with no friends in the first case:

Person.where('id NOT IN (SELECT DISTINCT(person_id) FROM friends)')

ActiveRecord find associated records of associated records in one query

Use a has_many through association:

class Author < ActiveRecord::Base
  has_many :books
  has_many :chapters, through: :books
end

And then use it like:

author = Author.find(...)
author.chapters

MySQL get all associated records where at least one record matches query

I believe you want:

SELECT u.`id`, u.`first_name` AS `firstName`, u.`last_name` AS `lastName`, u.`email`,
       us.`id` AS `usersSecondaryEmails.id`,
       us.`email` AS `usersSecondaryEmails.email`
FROM `Users` u LEFT OUTER JOIN
     `UsersSecondaryEmails` us
     ON u.`id` = us.`user_id`
WHERE (u.`first_name` LIKE '%bob%' OR u.`last_name` LIKE '%bob%' OR u.`email` LIKE '%bob%') OR
      EXISTS (SELECT 1
              FROM `UsersSecondaryEmails` us2
              WHERE us2.user_id = us.user_id AND us2.`email` LIKE '%bob%'
             );

This assumes you want all second emails even when only the fields in users match. If not, that could be taken into account.

Sql to fetch records only if related other table records exist

What you're asking for is called a semi-join. This returns one row from company if there are one or more matching rows in user.

If you use a regular join:

SELECT c.* FROM company c JOIN user u ON u.compid = c.id;

This does return the row from company, but you might not like that it returns one row per user. I.e. rows in the result are multiplied by the number of matches.

There are several possible fixes for this, to reduce the results to one row per company.

SELECT DISTINCT c.* FROM company c JOIN user u ON u.compid = c.id;

SELECT c.* FROM company c JOIN (SELECT DISTINCT compid FROM user) u ON u.compid = c.id;

SELECT * FROM company c WHERE c.id IN (SELECT compid FROM user);

SELECT * FROM company c WHERE EXISTS (SELECT * FROM user WHERE compid = c.id);

Which one is best for your app depends on many factors, such as the sizes of the tables, the other conditions in the query, etc... I'll leave it to you to evaluate them given your specific needs.

Rails find record with zero has_many records associated

Bah, found it here: https://stackoverflow.com/a/5570221/417872

City.includes(:photos).where(photos: { city_id: nil })

---

Related Topics