Forums

Querying blocks via SQL

Permalink 1 user found helpful
Hello,

I have created a custom block (let's call it "MyBlock"), and another custom block to aggregate content from a subset of MyBlocks and display it elsewhere on the site (let's call this block "Elsewhere").

From the Elsewhere block I can do SQL queries on the btMyBlock table to get the blocks that I'm interested in. The problem is that after a doing a few edits, I'm now seeing old versions included in the results alongside the current version.

I've tried joining on the CollectionVersionsBlocks and CollectionVersions tables, but they also include data from old versions, and it's not clear to me how to identify the "current" version.

How do I restrict the query to include only blocks that exist in a currently published state?

I've looked through the documentation and forums and have found lots of examples of getting blocks that belong to a specific collection, or blocks that belong to a certain area of a page, but I want to query blocks from everywhere on the site.

Any suggestions?
 
swr replied on at Permalink Reply
I think I've figured it out. The bit I was missing is that CollectionVersions uses a composite primary key (cvID and cID).

select mb.*
from btMyBlock mb 
inner join Blocks b on mb.bID = b.bID 
inner join CollectionVersionBlocks cvb on cvb.bID = b.bID
inner join CollectionVersions cv on cv.cvID = cvb.cvID and cv.cID = cvb.cID
where cv.cvIsApproved = 1 and b.bIsActive = 1
and mb.whatImLookingFor = ?
Tony replied on at Permalink Reply
Tony
where possible, it's better to use the existing interfaces. doing all those joins can be a big headache.

here's an example: 
$a = new Area("Main");
$areaBlocks = $a->getAreaBlocksArray($c);
if(is_array($areaBlocks) && count($areaBlocks)) {
    foreach($areaBlocks as $b){
        if($b->getBlockTypeHandle() == 'content') {
            $bi = $b->getInstance();
            $content .= $bi->getContent();
        }
    }
}
return $content;