Databases
StackOverflow2013
Postsdbo
POActiveRecord Subquery Comparison as a Scope
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POActiveRecord Subquery Comparison as a Scope
    primarykey
    data
    text
    <p>I'm rewriting a manual SQL query into an ActiveRecord query for use in a Rails app.</p> <p>What it does it collect two groups of users from the same table: 1 who have a qualifying event (44 or 48) in the last week, and a group of users who have the same qualifying event a month ago.</p> <p>This is the current query. I'm not sure how to turn this into an ActiveRecord scope:</p> <pre><code>select top 500 active_users_last_week.email from ( select distinct(email) from user_event_tracking where event_id in (44,48) and sitedb_created_date between getdate()-8 and getdate()-1 and sitedb_name = 'XYZ' and email not like '%@company.com' and email not like '%@other_company.com' ) active_users_last_week, ( select distinct(email) from user_event_tracking where event_id in (44,48) and sitedb_created_date between getdate()-60 and getdate()-30 and sitedb_name = 'XYZ' and email not like '%@company.com' and email not like '%@other_company.com ) active_users_last_month where active_users_last_week.email = active_users_last_month.email; </code></pre> <p>Any suggestions on how to turn this into an ActiveRecord scope? I have these set as scopes already:</p> <pre><code>scope :active_events, lambda { where("event_id in (44,48)") } scope :for_property, lambda { |property| where('sitedb_name = ?', property) } scope :last_week, lambda { where("sitedb_created_date between GETDATE()-8 and GETDATE()-1") } scope :last_month, lambda { where("sitedb_created_date between GETDATE()-60 and GETDATE()-30") } scope :no_test_users, lambda { where("email not like '%@company.com' and email not like '%@other_company.com'") } </code></pre> <p>The scopes all work individually (and with each other). The question is how to get emails that are in both <code>Event.active_events.last_week</code> and <code>Event.active_events.last_month</code> in an efficient way.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJeff Clark
    1. USJeff Clark
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COIt looks like it returns users for last week ONLY if they also had a qualifying event in the last month. Is that correct? What are model names?
      singulars
      1. POActiveRecord Subquery Comparison as a Scope
      1. USiouri
    2. COYes, that's exactly right. The model is named Event.
      singulars
      1. POActiveRecord Subquery Comparison as a Scope
      1. USJeff Clark
    3. COThere is also a user model? How are they joined? By E-mail?
      singulars
      1. POActiveRecord Subquery Comparison as a Scope
      1. USiouri
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload