Forums

Database structure: "Where can I find..."

Permalink
Hi guys,

I've run out of ideas on this one. Hope you can help.

I have a Block which inserts an image and a caption on to a Gallery page and into my btPhotoGallery table.


What I want to be able to do is to select the six most recently added images and add these to the homepage.

Should be easy, but I can't figure out how the data structure knows which images are actually on the Gallery page -- as when I delete an image block instance from the Gallery page, the record for that image in the btPhotoGallery table remains.

I guess there's a table which tells me which block instances are "live" on the current version of the page, but I can't puzzle it out...

Cheers.
edsalt
 
edsalt replied on at Permalink Reply
edsalt
OK. Some progress. Am using the following approach on the homepage:

$contentBlocks = array();
$c = new Page::getByPath('/gallery');
$blocks = $c->getBlocks('column2');
foreach($blocks as $b) {
    if ($b->getBlockTypeHandle() == 'photo') {
        $contentBlocks[] = $b;
    }
}


All I now need is a little nudge towards how to access the content of the blocks.

print_r($contentBlocks);

...gives me a huge array which contains what I want and a whole lot more.

Is there something as simple as a "getBlockContent" method?

Cheers again.
edsalt replied on at Permalink Reply
edsalt
In a moment of clarity I've figured out how the database tables relate. This seems pretty trivial now - but thought I'd post it here just in case anyone finds it useful.

In my example, I have a page containing a number of blocks of the "Gallery" type (a custom block I've created which allows me to place thumbnail images onto a page). I want to grab the half-dozen most-recently added thumbnails and place them on the homepage, too.

I suspect there might be a way to do this by accessing the blocks via the API but for now I've hardcoded a query onto the homepage which gives me what I need. Here's the query: 

SELECT * FROM btPhotoGallery 
INNER JOIN CollectionVersionBlocks 
WHERE (btPhotoGallery.bID = CollectionVersionBlocks.bID) 
AND CollectionVersionBlocks.cvID = 
(SELECT cvID FROM CollectionVersionBlocks
 WHERE cID = 46 
ORDER BY cvID DESC LIMIT 0 , 1 ) 
AND arHandle = "Content" 
ORDER BY CollectionVersionBlocks.bID DESC LIMIT 0,6


btPhotoGallery is my block table containing the image thumbnail etc.
Each row of this table (as for all block tables) has an auto-incremented bID which identifies each chunk of content.
However, btPhotoGallery contains all data - even for blocks which have been deleted through the GUI.
So need to figure out the latest version of the page and which blocks are on it.
I use CollectionVersionBlocks for this. This table contains as an auto-incremented field cvID, which indicates versioning. Co-existing blocks are grouped with the same cvID. The higher the cvID, the most recent the version, so I'm selecting top 1, ordered descending.
To get at only the blocks on the Gallery page, I need to pass in the cID for the page (46, in this case - see the Collections table for this).
To get at only the blocks in a specific area on this page, I need to pass in the arHandle (Content, in this case - this is whatever you've called your block content area).

Put it all together and it seems to work.

Like I say, it's not pretty and it can be hugely tidied, no doubt, by someone with a better knowledge of the API than me, but for now it does the job. If I can improve this I'll post back with modifications. If anyone wants to put me straight on a much better way of pulling this off, feel free ;)

Cheers all.
ryan replied on at Permalink Reply
ryan
This code should work for you (didn't test..). I think the part that you were missing is the BlockView object.

<?php
$galleryPage = Page::getByPath('/gallery');
$a = new Area("column2");
$galleryBlocks = $a->getAreaBlocksArray($galleryPage);
if(is_array($galleryBlocks) && count($galleryBlocks)) {
    array_slice($galleryBlocks,0,6);
   foreach($galleryBlocks as $b) {
        $bv = new BlockView();
        echo "<div>";
        $bv->render($b);
        echo "</div>";
    }
}
?>
edsalt replied on at Permalink Reply
edsalt
Many thanks Ryan. This just seems so elegant and simple, and it works brilliantly.

One tiny amend, I think. Should be...
$galleryBlocks = array_slice($galleryBlocks,0,6);

...instead of...
array_slice($galleryBlocks,0,6);


Now feel a tiny step closer to understanding the API.

Cheers!
ScottC replied on at Permalink Reply
ScottC
I love to see you post :)

-Scott