Answers.Body: <blockquote>
    <p>I wonder about efficiency.</p>
  </blockquote>
  
  <p>You're right to worry.  Doing this from PHP (even if you break it up into multiple SQL queries to get the data you need) is going to be much faster and less resource intensive than doing it from Twig.</p>
  
  <p>One thing I'd look at though, instead of querying data directly against the database, is to see if you can get what you need going through Craft's <a href="http://buildwithcraft.com/classreference#craft.app.services" rel="nofollow">services</a> in <code>craft/app/services/</code>.</p>
  
  <p>That way you don't have to worry about your plugin breaking in the event that the underlying database structure changes during an update.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-27T23:00:53.093Z
Answers.Text: Thank you, Brad, that helps.  I have now posted a [follow-up question relating to your advice to use Craft's services](http://craftcms.stackexchange.com/questions/9935/how-to-get-all-the-categories-related-to-an-entry-using-services).
Answers.UserDisplayName: kr37
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-27T17:49:47.290Z
Answers.LastActivityDate: 2015-05-27T17:49:47.290Z
Answers.UserDisplayName: Brad Bell
Body: <p>Sorry if this question is starting off too abstract. I'm porting a calendar plugin from Textpattern and WordPress into Craft.  Basic workflow for the output has been (in twig terms):</p>
  
  <pre><code>{% set start = "now"|date("Y-m-d") %}
  {{ craft.Calendar37.calendar_full(start)|raw }}
  </code></pre>
  
  <p>and the plugin php does all the work of querying the db and producing the calendar.</p>
  
  <p>What I'm up against is getting the db query right. It seems pretty abstract to get all the fields related to an entry--in particular working out the query to get the category from a category field in the entry. Probably I can work that out, but here's my question:</p>
  
  <p>Would it be possible or better to do more of the work with Twig, and less in php?  I.e. once the php db query knows the entries and i could put that into (I suppose) a model, then can Twig figure out the rest using (I haven't tried this, just working it out in my mind):</p>
  
  <pre><code>{% set start = "now"|date("Y-m-d") %}
  {% craft.Calendar37.initialize(start) %}  {# just does the DB query #}
  
  &lt;table&gt;
    ...
    &lt;td&gt;
      {% set event_instances = craft.Calendar37.getInstances(date) %}
      {% for instance in event_instances %}
         {% set entry = craft.entries.id(instance.entryId) %}
         &lt;li title='{{ entry.tip }}'&gt;
            &lt;a href='{{ siteUrl }}{{ entry.category }}'&gt;
               {{ instance.time }} - {{ craft.entries.id(instance.entryId).title }}
            &lt;/a&gt;
         &lt;/li&gt;
      {% endfor %}
    &lt;td&gt;
  </code></pre>
  
  <p>I wonder about efficiency.  One big, but precise query in php is probably better than going back from twig for every event on each day of the month as shown in the second part here.  This is the query so far; it doesn't find the category, or slug, or several other things yet:</p>
  
  <pre><code>$events = $query
      -&gt;select('*') //c37.event_id, dateYmd, timestr, alt_text, css_class, entries.id as event_id, title as event_title, slug')
      -&gt;from('calendar37 c37')
      -&gt;join('entries entries', 'c37.event_id = entries.id')
      -&gt;join('content content', 'entries.id = content.elementId')
      -&gt;join('elements_i18n elements_i18n', 'entries.id = elements_i18n.elementId')
      -&gt;where("c37.dateYmd &gt;= $fromDateYmd")
      -&gt;andWhere("c37.dateYmd &lt;= $toDateYmd") 
      //-&gt;andWhere('STATUS = published, pending, etc')
      -&gt;order('dateYmd ASC, timestr ASC')
      -&gt;queryAll();
  </code></pre>
  
  <p>Thanks for any advice!</p>
  
Tags: <plugin-development><templating><database><queries>
Title: db query to get fields related to entry
ThreadText: # db query to get fields related to entry
  
  (Asked by: username_0 on 2015-05-27)
  
  Sorry if this question is starting off too abstract. I'm porting a calendar plugin from Textpattern and WordPress into Craft. Basic workflow for the output has been (in twig terms):
  
  ```
  {% set start = "now"|date("Y-m-d") %}
  {{ craft.Calendar37.calendar_full(start)|raw }}
  
  ```
  
  and the plugin php does all the work of querying the db and producing the calendar.
  
  What I'm up against is getting the db query right. It seems pretty abstract to get all the fields related to an entry--in particular working out the query to get the category from a category field in the entry. Probably I can work that out, but here's my question:
  
  Would it be possible or better to do more of the work with Twig, and less in php? I.e. once the php db query knows the entries and i could put that into (I suppose) a model, then can Twig figure out the rest using (I haven't tried this, just working it out in my mind):
  
  ```
  {% set start = "now"|date("Y-m-d") %}
  {% craft.Calendar37.initialize(start) %}  {# just does the DB query #}
  
  <table>
    ...
    <td>
      {% set event_instances = craft.Calendar37.getInstances(date) %}
      {% for instance in event_instances %}
         {% set entry = craft.entries.id(instance.entryId) %}
         <li title='{{ entry.tip }}'>
            <a href='{{ siteUrl }}{{ entry.category }}'>
               {{ instance.time }} - {{ craft.entries.id(instance.entryId).title }}
            </a>
         </li>
      {% endfor %}
    <td>
  
  ```
  
  I wonder about efficiency. One big, but precise query in php is probably better than going back from twig for every event on each day of the month as shown in the second part here. This is the query so far; it doesn't find the category, or slug, or several other things yet:
  
  ```
  $events = $query
      ->select('*') //c37.event_id, dateYmd, timestr, alt_text, css_class, entries.id as event_id, title as event_title, slug')
      ->from('calendar37 c37')
      ->join('entries entries', 'c37.event_id = entries.id')
      ->join('content content', 'entries.id = content.elementId')
      ->join('elements_i18n elements_i18n', 'entries.id = elements_i18n.elementId')
      ->where("c37.dateYmd >= $fromDateYmd")
      ->andWhere("c37.dateYmd <= $toDateYmd") 
      //->andWhere('STATUS = published, pending, etc')
      ->order('dateYmd ASC, timestr ASC')
      ->queryAll();
  
  ```
  
  Thanks for any advice!
  
  **Tags:** plugin-development, templating, database, queries
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2015-05-27. Score: 1 | Accepted answer
  
  > I wonder about efficiency.
  
  You're right to worry. Doing this from PHP (even if you break it up into multiple SQL queries to get the data you need) is going to be much faster and less resource intensive than doing it from Twig.
  
  One thing I'd look at though, instead of querying data directly against the database, is to see if you can get what you need going through Craft's [services](http://buildwithcraft.com/classreference#craft.app.services) in `craft/app/services/`.
  
  That way you don't have to worry about your plugin breaking in the event that the underlying database structure changes during an update.
  
  > **Comments:**
  > **username_0**: Thank you, username_1, that helps.  I have now posted a [follow-up question relating to your advice to use Craft's services](http://craftcms.stackexchange.com/questions/9935/how-to-get-all-the-categories-related-to-an-entry-using-services).  
  
  ---
  
  