CODEWITHSUNDEEP
sqlruby-on-railsactiverecordruby-on-rails-5
Adrian Eranzi
Adrian Eranzi

Reputation: 35

Ruby on Rails - How to do custom query?

I have 2 tables with millions of records:

First table is Subscription and second table is Message.

In Subscription table, I have 3 indexes:

However, in Message table, I only have 2 indexes:

I have msisdn in Message table but it is not indexed.

So, I have a dashboard UI that let me search for Message.

I want to search Message using msisdn but since msisdn is not indexed, the query is taking forever!

but I want to put the idea of "Indexing msisdn in Message table" as plan B.

So my Plan A is(which is where I need help for):

Since both table has indexed reporting_id, I want to search for Message using subscription.msisdn in the dashboard, and then from there, I want to use the subscription.reporting_id and list all the Message with that subscription.reporting_id.

Something like subscription.reporting_id = message.reporting_id.

I have the view, controller and model for these 2 tables,

I tried doing this in both models

has_many :message, foreign_key: :reporting_id in Subscription.rb

and

belongs_to :subscription in Message.rb

but it didn't work.

In my view, the search_field for MSISDN is currently search for record from subscription.msisdn

Does anyone knows how can I achieve that?

PS: I want to prevent from editing the table structure.

Upvotes: 1

Views: 1136

Answers (3)

Adrian Eranzi
Adrian Eranzi

Reputation: 35

Found the solution!

In my Message.rb, I did:

belongs_to :subscription, foreign_key: :reporting_id, primary_key: :reporting_id

and it generated this query

SELECT  `subscription`.* FROM `subscription` WHERE `subscription`.`reporting_id` = '1eaa7a61fe635005c81df347b1a7c401' LIMIT 1

Upvotes: 0

Kvr Ramya
Kvr Ramya

Reputation: 36

have to add primary key as well has_many :message, foreign_key: :reporting_id, primary_key: :reporting_id in Subscription.rb

Upvotes: 1

Adim
Adim

Reputation: 1776

If you want to establish a has_many :messages association in the Subscription class, you'll need to specify the primary_key. 

class Subscription < ApplicationRecord
  has_many :messages, foreign_key: "reporting_id", primary_key: "reporting_id"
end

With this, the SQL generated will look like this:

SELECT "messages".* FROM "messages" WHERE "messages"."reporting_id" = $1  [["reporting_id", "#{reporting_id}"]]

The primary_key ensures the reporting_id value of the Subscription model is used, else it will default to the id column.

However, rather than establishing this association which doesn't seem exactly normalized(atleast 3NF), you can just have a query to find all messages using the msisdn and reporting_id. Something like this should still be effective:

Message.
 joins("INNER JOIN subscriptions ON subscriptions.reporting_id = messages.reporting_id").
 merge(Subscription.where(msisdn: "#{value}"))

Upvotes: 2

Related Questions