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:

    story AS s,
    storyTranslation AS st,
    revision AS r
    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.

  • 1
    Craft has built-in support for locales (i.e. translations) and revision history for entries. Are you not using these? Nov 19 '14 at 16:45
  • 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 Nov 20 '14 at 11:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.