Answers.Body: <p>You'll be <em>much</em> better off doing this in Twig or PHP, as there is a lot of logic involved in fetching the content of live entries. But since you asked, here is an SQL query that joins 3 tables to get you the title of all live entries.</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT title FROM content
  INNER JOIN entries ON entries.id = content.elementId
  INNER JOIN elements ON elements.id = content.elementId
  WHERE enabled = 1
  AND draftId IS NULL AND revisionId IS NULL AND dateDeleted IS NULL
  AND postDate &lt;= now() 
  AND (expiryDate IS NULL OR expiryDate &gt; now())
  </code></pre>
  <p>Some of the above logic was referenced from the <a href="https://github.com/craftcms/cms/blob/c5730385b295fc383002832435e9813bdcad98d4/src/elements/db/EntryQuery.php#L874-L886" rel="nofollow noreferrer">EntryQuery</a> class.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-01-09T14:48:22.980Z
Answers.Text: To add to that, it's simply because fields and titles are saved in the content table and other meta informations are in the entries and elements table. An entry is an element, so we have to fetch infos from both tables and then make sure that it's not in draft, revision or that it's not been deleted (dateDeleted is a field that indicates a "soft delete"). Then checking if the entry has a valid date with a few more sql lines.
Answers.UserDisplayName: davidwebca
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-01-31T17:03:14.723Z
Answers.Text: Thanks very much; I appreciate this answer far more than my delayed acceptance would suggest.
Answers.UserDisplayName: Sapphireblue
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-12-29T17:52:39.363Z
Answers.LastActivityDate: 2022-12-31T18:14:32.210Z
Answers.LastEditDate: 2022-12-31T18:14:32.210Z
Answers.UserDisplayName: Ben Croker
Body: <p>I maintain a Craft 3.4.17.1 site for a non-profit client and it's grant-reporting season. My client has asked me to put together a quick-turnaround offline report of all the content we published this year across several sections of our site, so that we can do some analysis around our authors, topics, etc. No problem, I said! Totes easy, I said! Lol. Loooool.</p>
  <p>Can someone please help me to understand how I write a SQL query for retrieving some field values such as entry title for ONLY THE CURRENT/LIVE VERSION/DRAFT of entries?</p>
  <p>For a while it seemed like the key would be to exclude rows where the elements table had any values for revisionId or draftId—but that was a false ray of hope, because we have a relational field, &quot;Contributor,&quot; for the content entries I'm focused on, which points to to author—profile type entries that I'll need to join to grab the author names, and in some cases the craft_relations row containing the relevant fieldId only exists in association with a craft_elements row that does have non-null revisionId or draftId.</p>
  <p>So now I'm back to just blankly staring at my SQL editor, unable to puzzle out how Craft keeps track of all the revisions/drafts/entries of all the entries/elements/content that make up one lil blog post on our website. Halp? Thank you very much in advance.</p>
  <p>(I want to acknowledge that it seems as if, if I were willing to both upgrade to 3.7+ and also write the whole report in Craft queries instead of SQL, this would be more intuitive to accomplish. However, for a one-off/internal-use-only use case, I'm not trying to sign up for that level of prerequisite effort plus a maintenance window/content freeze on our public-facing site, vs what I thought would be a hour or two of SQL noodling on a local copy of our database—and which WOULD be that in other, not-necessarily-at-all-superior CMSes that will go unnamed!)</p>
  <p>(Also also: I did attempt to install a trial of Sprout Reports to see if that'd do this job for me, but it just throws gnarly CP errors once installed, so.)</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-12-27T19:56:07.237Z
Comments.Text: Are you targeting a specific data format or just a list of titles? You might have a faster turnaround using element queries in a Twig template.
Comments.UserDisplayName: CraftQuest
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-12-27T20:02:39.373Z
Comments.Text: I'm also struggling to understand why Craft queries (i.e. element queries) is off the table for this use case. Regardless of whether you use PHP or Twig, compared to writing custom SQL queries using the document element query API would be a lot easier. It would also infer less of a maintenance burden (considering the database structure could potentially change outside of major Craft versions, but the element query APIs on top of that structure, won't).
Comments.UserDisplayName: Mats Mikkel Rummelhoff
Tags: <entries><database><revisions>
Title: How to write SQL query for live/current version of Craft entries?
ThreadText: # How to write SQL query for live/current version of Craft entries?
  
  (Asked by: username_0 on 2022-12-27)
  
  I maintain a Craft 3.4.17.1 site for a non-profit client and it's grant-reporting season. My client has asked me to put together a quick-turnaround offline report of all the content we published this year across several sections of our site, so that we can do some analysis around our authors, topics, etc. No problem, I said! Totes easy, I said! Lol. Loooool.
  
  Can someone please help me to understand how I write a SQL query for retrieving some field values such as entry title for ONLY THE CURRENT/LIVE VERSION/DRAFT of entries?
  
  For a while it seemed like the key would be to exclude rows where the elements table had any values for revisionId or draftId—but that was a false ray of hope, because we have a relational field, "Contributor," for the content entries I'm focused on, which points to to author—profile type entries that I'll need to join to grab the author names, and in some cases the craft\_relations row containing the relevant fieldId only exists in association with a craft\_elements row that does have non-null revisionId or draftId.
  
  So now I'm back to just blankly staring at my SQL editor, unable to puzzle out how Craft keeps track of all the revisions/drafts/entries of all the entries/elements/content that make up one lil blog post on our website. Halp? Thank you very much in advance.
  
  (I want to acknowledge that it seems as if, if I were willing to both upgrade to 3.7+ and also write the whole report in Craft queries instead of SQL, this would be more intuitive to accomplish. However, for a one-off/internal-use-only use case, I'm not trying to sign up for that level of prerequisite effort plus a maintenance window/content freeze on our public-facing site, vs what I thought would be a hour or two of SQL noodling on a local copy of our database—and which WOULD be that in other, not-necessarily-at-all-superior CMSes that will go unnamed!)
  
  (Also also: I did attempt to install a trial of Sprout Reports to see if that'd do this job for me, but it just throws gnarly CP errors once installed, so.)
  
  **Tags:** entries, database, revisions
  
  > **Comments:**
  > **username_1**: Are you targeting a specific data format or just a list of titles? You might have a faster turnaround using element queries in a Twig template.  
  > **username_2**: I'm also struggling to understand why Craft queries (i.e. element queries) is off the table for this use case. Regardless of whether you use PHP or Twig, compared to writing custom SQL queries using the document element query API would be a lot easier. It would also infer less of a maintenance burden (considering the database structure could potentially change outside of major Craft versions, but the element query APIs on top of that structure, won't).  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_3 on 2022-12-29. Score: 2 | Accepted answer
  
  You'll be *much* better off doing this in Twig or PHP, as there is a lot of logic involved in fetching the content of live entries. But since you asked, here is an SQL query that joins 3 tables to get you the title of all live entries.
  
  ```sql
  SELECT title FROM content
  INNER JOIN entries ON entries.id = content.elementId
  INNER JOIN elements ON elements.id = content.elementId
  WHERE enabled = 1
  AND draftId IS NULL AND revisionId IS NULL AND dateDeleted IS NULL
  AND postDate <= now() 
  AND (expiryDate IS NULL OR expiryDate > now())
  
  ```
  
  Some of the above logic was referenced from the [EntryQuery](https://github.com/craftcms/cms/blob/c5730385b295fc383002832435e9813bdcad98d4/src/elements/db/EntryQuery.php#L874-L886) class.
  
  > **Comments:**
  > **username_4**: To add to that, it's simply because fields and titles are saved in the content table and other meta informations are in the entries and elements table. An entry is an element, so we have to fetch infos from both tables and then make sure that it's not in draft, revision or that it's not been deleted (dateDeleted is a field that indicates a "soft delete"). Then checking if the entry has a valid date with a few more sql lines.  
  > **username_0**: Thanks very much; I appreciate this answer far more than my delayed acceptance would suggest.  
  
  ---
  
  