Forums

Auto Nav Database Query

Permalink
Hello,

I need to know how concrete5 stores information that hides pages from the navigation. We are attempting to extract the database records that control our menu but we are running into some issues/quirks. I'm pretty sure we don't have the query exactly right so I would like to share it and see if someone can point us in the right direction.

Here is the query we are using but this returns a couple pages that are hidden from our menu, for some reason but others that are hidden are not in our results. 

SELECT DISTINCT            
    cv.cvName,
    PagePaths.cPath,
    PagePaths.cID
 FROM `Pages` p
INNER JOIN Collections c USING ( cID )
INNER JOIN PagePaths USING (cID)
INNER JOIN CollectionVersions cv USING (cID)
INNER JOIN CollectionAttributeValues cav USING (cID)
WHERE `cParentID` = 1
AND `cIsSystemPage` !=1 
AND ppIsCanonical = 1
AND cav.akID != 4
order by cDisplayOrder asc
 
mikemccoy replied on at Permalink Best Answer Reply
So we figured it out. This query below generates our nav so we can export it and use it elsewhere, like in wordpress blog:

SELECT DISTINCT cv.cvName,
PagePaths.cPath,
PagePaths.cID,
cv.cvID
FROM `Pages` p
INNER JOIN Collections c USING ( cID )
INNER JOIN PagePaths USING (cID)
INNER JOIN CollectionVersions cv USING (cID)
LEFT JOIN CollectionAttributeValues cav on p.cID = cav.cID and cav.akID != 4
WHERE `cParentID` = {$parentID}
AND `cIsSystemPage` !=1
AND ppIsCanonical = 1
AND p.cID not in ( select cv1.cID from CollectionVersions cv1 inner join CollectionAttributeValues cav1 USING (cID) where cvIsApproved = 1 and cav1.akID = 4 )
AND cv.cvIsApproved =1
order by cDisplayOrder asc

You will need to do this for every level of your menu, ie. parents, children, grandchildren. I'm sure there are other approaches but this got the results we needed very efficiently and we can use this to extract our menu from the database and use it elsewhere.