Body: <p>I'm wondering: what is the best way to do complex queries for a plugin?</p>
  
  <p>For example, I'm building a site that publishes stories and sometimes the same story can be in different languages.</p>
  
  <p>The database tables would be:</p>
  
  <pre><code>story { id, author, ...  }
  storyTranslation { id, storyId, storyTitle, storyContent, locale }  
  revision { storyId, storyTranslationId, revision, status }
  </code></pre>
  
  <p>I tried to use scopes, and criteria but i was not able to make it work in the following scenario:</p>
  
  <p>I want to show latest stories (scope for story) that are in the currently selected language (scope for storyTranslation) and that has the latest revision with a status of 1 (scope for revision)  .</p>
  
  <p>At the moment of testing I have 2 stories, both have content in english but one of them does not have a revision data, however I still see this story in the list for some reason. It looks like the scopes are somehow not part of the main query.</p>
  
  <p>I guess i would like to be able to write the following query:</p>
  
  <pre><code>SELECT * FROM
      story AS s,
      storyTranslation AS st,
      revision AS r
  WHERE
      st.storyId = s.id AND
      r.storyTranslationId = st.id AND
      st.locale = $locale AND
      r.status = $status
  </code></pre>
  
  <p>All the tables have records and all the relations are set up. If I'm just querying using attributes it works but if the query grows longer than just single record I'm starting to get unexpected results.</p>
  
  <p>Thanks for help in advance. </p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2014-11-19T16:45:44.883Z
Comments.Text: Craft has built-in support for [locales](http://buildwithcraft.com/docs/localization-guide) (i.e. translations) and revision history for entries. Are you not using these?
Comments.UserDisplayName: Douglas McDonald
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2014-11-20T11:10:53.003Z
Comments.Text: The situation i described was just an example, i was basically trying to understand how to do the complex queries using active record. In this project there are about 15 new tables used within a plugin. From what i have read so far it seems that the best option is to use the query builder when listing multiple items but to use active record for inserts updates and error handling as it does many things automatically
Comments.UserDisplayName: Gints Ligotnis
Tags: <plugin-development><queries><records>
Title: Complex queries
ThreadText: # Complex queries
  
  (Asked by: username_0 on 2014-11-19)
  
  I'm wondering: what is the best way to do complex queries for a plugin?
  
  For example, I'm building a site that publishes stories and sometimes the same story can be in different languages.
  
  The database tables would be:
  
  ```
  story { id, author, ...  }
  storyTranslation { id, storyId, storyTitle, storyContent, locale }  
  revision { storyId, storyTranslationId, revision, status }
  
  ```
  
  I tried to use scopes, and criteria but i was not able to make it work in the following scenario:
  
  I want to show latest stories (scope for story) that are in the currently selected language (scope for storyTranslation) and that has the latest revision with a status of 1 (scope for revision) .
  
  At the moment of testing I have 2 stories, both have content in english but one of them does not have a revision data, however I still see this story in the list for some reason. It looks like the scopes are somehow not part of the main query.
  
  I guess i would like to be able to write the following query:
  
  ```
  SELECT * FROM
      story AS s,
      storyTranslation AS st,
      revision AS r
  WHERE
      st.storyId = s.id AND
      r.storyTranslationId = st.id AND
      st.locale = $locale AND
      r.status = $status
  
  ```
  
  All the tables have records and all the relations are set up. If I'm just querying using attributes it works but if the query grows longer than just single record I'm starting to get unexpected results.
  
  Thanks for help in advance.
  
  **Tags:** plugin-development, queries, records
  
  > **Comments:**
  > **username_1**: Craft has built-in support for [locales](http://buildwithcraft.com/docs/localization-guide) (i.e. translations) and revision history for entries. Are you not using these?  
  > **username_0**: The situation i described was just an example, i was basically trying to understand how to do the complex queries using active record. In this project there are about 15 new tables used within a plugin. From what i have read so far it seems that the best option is to use the query builder when listing multiple items but to use active record for inserts updates and error handling as it does many things automatically  
  
  ---
  
  ## 0 answers
  
  