1

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.)

Improve this question
2
  • 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.
    – CraftQuest
    Commented Dec 27, 2022 at 19:56
  • 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).
    – Mats Mikkel Rummelhoff
    Commented Dec 27, 2022 at 20:02

1 Answer 1

Reset to default
2

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.

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 class.

Improve this answer
2
  • 1
    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.
    – davidwebca
    Commented Jan 9, 2023 at 14:48
  • Thanks very much; I appreciate this answer far more than my delayed acceptance would suggest.
    – Sapphireblue
    Commented Jan 31, 2023 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.