Skip to main content
Craft CMS

Return to Answer

Should probably exclude soft-deleted entries as well.
Source Link
Mats Mikkel Rummelhoff
Mats Mikkel Rummelhoff
  • 23k
  • 3
  • 39
  • 71

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.

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 postDate <= now() 
AND (expiryDate IS NULL OR expiryDate > now())

Some of the above logic was referenced from the EntryQuery class.

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.

added 15 characters in body
Source Link
Ben Croker
Ben Croker
  • 7.8k
  • 27
  • 56

You'll be muchmuch 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 willjoins 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 postDate <= now() 
AND (expiryDate IS NULL OR expiryDate > now())

Some of the above logic was referenced from the EntryQuery class.

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 will 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 postDate <= now() 
AND (expiryDate IS NULL OR expiryDate > now())

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 postDate <= now() 
AND (expiryDate IS NULL OR expiryDate > now())

Some of the above logic was referenced from the EntryQuery class.

Source Link
Ben Croker
Ben Croker
  • 7.8k
  • 27
  • 56

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 will 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 postDate <= now() 
AND (expiryDate IS NULL OR expiryDate > now())