Answers.Body: <p>The old query for the view doesn’t work because, in Craft 5, the <code>content</code> table was moved to a JSON-encoded <code>content</code> column in the <code>element_sites</code> table.</p>
  <p>There's not much documentation for how plugin developers can interact with that yet, so you'll probably have to do some code diving.</p>
  <p><a href="https://github.com/craftcms/cms/blob/5.x/CHANGELOG.md#extensibility-2" rel="nofollow noreferrer">https://github.com/craftcms/cms/blob/5.x/CHANGELOG.md#extensibility-2</a></p>
  <blockquote>
  <p>Elements now store their content in an elements_sites.content column as JSON, rather than across multiple columns in a content table.</p>
  </blockquote>
  <p>Which references these Github issues, which add some context:</p>
  <ul>
  <li><a href="https://github.com/craftcms/cms/issues/2009" rel="nofollow noreferrer">https://github.com/craftcms/cms/issues/2009</a></li>
  <li><a href="https://github.com/craftcms/cms/issues/4308" rel="nofollow noreferrer">https://github.com/craftcms/cms/issues/4308</a></li>
  <li><a href="https://github.com/craftcms/cms/issues/7221" rel="nofollow noreferrer">https://github.com/craftcms/cms/issues/7221</a></li>
  <li><a href="https://github.com/craftcms/cms/issues/7750" rel="nofollow noreferrer">https://github.com/craftcms/cms/issues/7750</a></li>
  <li><a href="https://github.com/craftcms/cms/issues/12954" rel="nofollow noreferrer">https://github.com/craftcms/cms/issues/12954</a></li>
  </ul>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-07-01T02:18:47.623Z
Answers.LastActivityDate: 2024-07-01T02:18:47.623Z
Answers.UserDisplayName: Brad Bell
Body: <p>I have a custom module on my Craft CMS site that uses a database view by joining the content table and user table. This view consolidates all user details into one place. However, with the Craft CMS 5 update, the content table is no longer utilized.</p>
  <p>How can I recreate this view in Craft 5? The functionality of several portions of my website depends on this view, and I am unable to proceed with the update until this issue is resolved.</p>
  <p>Thank you for your help!</p>
  <p>Example view -</p>
  <pre><code>SELECT
     `elements`.`id` AS `UserId`,
     `users`.`username` AS `username`,
     `users`.`firstName` AS `firstName`,
     `users`.`lastName` AS `lastName`,
     `users`.`email` AS `email`,
     `users`.`unverifiedEmail` AS `unverifiedEmail`,
     `users`.`admin` AS `admin`,
     `users`.`locked` AS `locked`,
     `users`.`pending` AS `pending`,
     `users`.`suspended` AS `suspended`,
     `users`.`lastLoginDate` AS `lastLoginDate`,
     `content`.`field_companyAbbrv` AS `field_companyAbbrv`,
     `content`.`field_companyname` AS `field_companyname`,
     `content`.`field_companyReport` AS `field_companyReport`
  FROM (((`elements` join `users` 
  on(`users`.`id` = `elements`.`id`)) 
  join `elements_sites` 
  on(`elements_sites`.`elementId` = `elements`.`id`)) 
  join `content` on(`content`.`elementId` = `elements`.`id`));```
  </code></pre>
  
Tags: <database><content><craft5>
Title: Craft 5 Update: Database Views for Content Table No Longer Functioning
ThreadText: # Craft 5 Update: Database Views for Content Table No Longer Functioning
  
  (Asked by: username_0 on 2024-06-18)
  
  I have a custom module on my Craft CMS site that uses a database view by joining the content table and user table. This view consolidates all user details into one place. However, with the Craft CMS 5 update, the content table is no longer utilized.
  
  How can I recreate this view in Craft 5? The functionality of several portions of my website depends on this view, and I am unable to proceed with the update until this issue is resolved.
  
  Thank you for your help!
  
  Example view -
  
  ```
  SELECT
     `elements`.`id` AS `UserId`,
     `users`.`username` AS `username`,
     `users`.`firstName` AS `firstName`,
     `users`.`lastName` AS `lastName`,
     `users`.`email` AS `email`,
     `users`.`unverifiedEmail` AS `unverifiedEmail`,
     `users`.`admin` AS `admin`,
     `users`.`locked` AS `locked`,
     `users`.`pending` AS `pending`,
     `users`.`suspended` AS `suspended`,
     `users`.`lastLoginDate` AS `lastLoginDate`,
     `content`.`field_companyAbbrv` AS `field_companyAbbrv`,
     `content`.`field_companyname` AS `field_companyname`,
     `content`.`field_companyReport` AS `field_companyReport`
  FROM (((`elements` join `users` 
  on(`users`.`id` = `elements`.`id`)) 
  join `elements_sites` 
  on(`elements_sites`.`elementId` = `elements`.`id`)) 
  join `content` on(`content`.`elementId` = `elements`.`id`));```
  
  ```
  
  **Tags:** database, content, craft5
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2024-07-01. Score: 0
  
  The old query for the view doesn’t work because, in Craft 5, the `content` table was moved to a JSON-encoded `content` column in the `element_sites` table.
  
  There's not much documentation for how plugin developers can interact with that yet, so you'll probably have to do some code diving.
  
  <https://github.com/craftcms/cms/blob/5.x/CHANGELOG.md#extensibility-2>
  
  > Elements now store their content in an elements\_sites.content column as JSON, rather than across multiple columns in a content table.
  
  Which references these Github issues, which add some context:
  
  * <https://github.com/craftcms/cms/issues/2009>
  * <https://github.com/craftcms/cms/issues/4308>
  * <https://github.com/craftcms/cms/issues/7221>
  * <https://github.com/craftcms/cms/issues/7750>
  * <https://github.com/craftcms/cms/issues/12954>
  
  
  ---
  
  