Need assistance re-writing some Rails DB Queries that were originally written with a gem called Sque-CodePudding

I have a couple really hard DB queries I need help to re-write in the correct way for Rails 6 Active Record. These are currently working in an app I an re-writing to the new version of Ruby on Rails (6.1.4.2).

It was originally written on Rails v3.2 with a Hell gem called squeel which uses its own DSL Language.

https://github.com/activerecord-hackery/squeel

I have been trying for days now and haven't been able to get it figured out. The first time I asked it I probably wasn't as clear as I needed to be. So this time I am going to put the query as it was written in squeel, and the SQL that the console from Heroku is spitting out and that's all. If anyone wants any additional information ask and I will HAPPILY post it. I want to keep it simple to start with as they are confusing enough.

WARNING: These seem to be EXTREMLY COMPLICATED. ANY HELP would be VERY Appreciated! :)

Here is squeel DB Query 1:

Project.joins{vendor}.joins{certifications.outer}.where{
        (projects.vendor_id.eq my{ vendor_id }) |
        (vendors.parent_vendor_id.eq my{ vendor_id }) |
        ((certifications.cdti == true) & (certifications.published == true))
      }.uniq

Here is the strait SQL from query 1 out of Rails v3.2:

SELECT DISTINCT "vendors".* FROM "vendors" INNER JOIN "projects" ON "projects"."vendor_id" = "vendors"."id" 
INNER JOIN "certifications" ON "certifications"."project_id" = "projects"."id" 
WHERE (("certifications"."cdti" = 't' AND "certifications"."published" = 't')) 
ORDER BY "vendors"."parent_vendor_id", "vendors"."name"

Here is the squeel DB query 2:

Fleet.joins{vendor.projects.certifications}.
        where{(certifications.cdti.eq true) & (certifications.published.eq true)}.
        uniq.includes(:vendor).
        order(:vendor_id, :name)

Here is the strait SQL from query 2 out of Rails v3.2: (I hit enter in a few places so you could see the entire statement without having to scroll to the right

SELECT DISTINCT "fleets".* FROM "fleets" INNER JOIN "vendors" ON "vendors"."id" = "fleets"."vendor_id" 
INNER JOIN "projects" ON "projects"."vendor_id" = "vendors"."id" 
INNER JOIN "certifications" ON "certifications"."project_id" = "projects"."id" 
WHERE (("certifications"."cdti" = 't' AND "certifications"."published" = 't')) 
ORDER BY "fleets"."vendor_id", "fleets"."name"

Again if anyone wants to see or know anything else just let me know as I am trying my best to figure this out, but these seem so advanced I just don't think I know the correct syntax.

Thank You for your time, Scott

CodePudding user response：

Query 1 equivalent is:

Vendor.joins(projects: :certifications).where(certifications: { cdti: 't', published: 't' }).order(:parent_vendor_id, :name).distinct

Query 2:

Fleet.joins(vendor: { projects: :certifications }).where(certifications: { cdti: 't', published: 't' }).order(:vendor_id, :name).distinct