Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • nickornotto 397 posts 900 karma points
    Mar 08, 2022 @ 22:52
    nickornotto
    0

    How to sql query to return property data of the published version?

    I am trying to write a query to get all property data of a published content version, so I am trying this first:

    SELECT cd.*, cd.[text], cv.id, cv.VersionDate
    FROM cmsDocument as cd
        inner join cmsContentVersion as cv on cd.versionId = cv.VersionId
    WHERE
        cv.ContentId = 12270 and cd.newest = 1 and cd.published = 1
    

    and it returns one record of the published version correctly.

    Then I am extending it like this:

    SELECT pd.id, pt.alias, cd.*, cd.[text], cv.id, cv.VersionDate
    FROM cmsDocument as cd
        inner join cmsContentVersion as cv on cd.versionId = cv.VersionId
        inner join cmsPropertyData as pd **on pd.contentNodeId = cd.nodeId**
        inner join cmsPropertyType as pt  on pd.propertyTypeiD = pt.id
    WHERE
        cv.ContentId = 12270 and cd.newest = 1 and cd.published = 1
    

    and it returns the correct document but all the properties are multiplied (different number of times, depending on the property), plus it returns also properties that already have been deleted and are not present in the published version.

    This does not return anything

    SELECT pd.id, pt.alias, cd.*, cd.[text], cv.id, cv.VersionDate
    FROM cmsDocument as cd
        inner join cmsContentVersion as cv on cd.versionId = cv.VersionId
        inner join cmsPropertyData as pd **on cd.versionId = pd.versionId**
        inner join cmsPropertyType as pt  on pd.propertyTypeiD = pt.id
    WHERE
        cv.ContentId = 12270 and cd.newest = 1 and cd.published = 1
    

    and it seems that versionId of a document is something else than the versionId of the property data.


    How to write the correct query that returns the property of of the currently published content?


  • Damian 61 posts 342 karma points
    Mar 10, 2022 @ 15:46
    Damian
    0

    Highly recommend to use the api rather than a direct DB interaction. Any direct DB interaction is going to be problematic and make maintaining a site, quite simply, a nightmare.

  • nickornotto 397 posts 900 karma points
    Mar 10, 2022 @ 15:50
    nickornotto
    0

    You are wrongly assuming I want to use it in the backend. Please stick to answering the question.

  • Damian 61 posts 342 karma points
    Mar 10, 2022 @ 15:57
    Damian
    0

    whatever you're doing is wrong. you've been informed a number of times in the forums that direct SQL access is not a good idea. please be respectful of other people's time, experience and knowledge.

  • nickornotto 397 posts 900 karma points
    Mar 10, 2022 @ 17:26
    nickornotto
    0

    I ask you the same.

    You have no idea what I am doing so assuming anything is simply blind. Your advice do not help anything. It is the waste of your and my time.

Please Sign in or register to post replies

Write your reply to:

Draft