Developing (v7+)

Single Block, multiple Tables

Permalink June 11, 2012 at 11:30 PM

Hello,

Basically, I have a single Block, a single view, a single Controller etc., but I need to deal with two tables.

Is there a way to do this without writing the whole SQL Statement for any of the tables? (Using C5 Functions for both tables, I hope)

If there is, can someone point me in the right direction of how to properly reference each table?

Thanks in advance.

adajad replied on Jun 12, 2012 at 2:21 am Permalink Reply

Insert needs separate queries, but you can always use join on select statements.

http://dev.mysql.com/doc/refman/5.0/en/join.html...

FernandoCordeiro replied on Jun 12, 2012 at 10:48 am Permalink Reply

So, I have to write the full queries then?

I was hoping C5 evolved to better support multiple tables (Using C5 Functions instead of accessing the DB directly)..

JBPrime replied on Jun 19, 2012 at 3:08 pm Permalink Best Answer Reply

I couldn't find a pre-existing way to do this, but after looking at the implementation of BlockController a bit, I created a new class that handles multiple tables for a single block. The end result is that each field from each table is exposed as a typical block variable (e.g. table field customField1 becomes $customField1 in the PHP code).

I put block_multi_table_controller.php in my <website>/libraries folder with the following contents:

<?php 
defined('C5_EXECUTE') or die("Access Denied.");
class BlockMultiTableController extends BlockController
{
   protected $records = array();
   public function __construct( $obj = null )
   {
      parent::__construct( $obj );
      if ( !( $obj instanceof BlockType ) && ( $obj instanceof Block ) )
      {
         if ( $this->btTables )
         {
            $bID = $obj->getBlockID();
            foreach ( $this->btTables as $table )
            {

Viewing 15 lines of 91 lines. View entire code block.

<?php 
defined('C5_EXECUTE') or die("Access Denied.");
 
class BlockMultiTableController extends BlockController
{
   protected $records = array();
 
   public function __construct( $obj = null )
   {
      parent::__construct( $obj );
 
      if ( !( $obj instanceof BlockType ) && ( $obj instanceof Block ) )
      {
         if ( $this->btTables )
         {
            $bID = $obj->getBlockID();
 
            foreach ( $this->btTables as $table )
            {
               $rec = new BlockRecord( $table );
               $rec->bID = $bID;
               $rec->Load( "bID=" . $bID );
 
               $this->records[] = $rec;
            }
 
            $this->load();
         }
      }
   }
 
   public function getBlockControllerData()
   {
      if ( count( $this->records ) == 0 )
      {
         return null;
      }
      else
      {
         return $this->records[0];
      }
   }
 
   public function save( $args )
   {
      foreach ( $this->records as $rec )
      {
         $attribs = $rec->getAttributeNames();
 
         foreach ( $attribs as $key )
         {
            if ( isset( $args[$key] ) )
            {
               $rec->{$key} = $args[$key];
            }
         }
 
         $rec->Replace();
      }
   }
 
   public function duplicate( $newBID )
   {
      if ( $this->btTables )
      {
         foreach ( $this->records as $rec )
         {
            $newInstance = clone $rec;
            $newInstance->bID = $newBID;
            $newInstance->Insert();
         }
 
         return 0;
      }
   }
 
   public function delete()
   {
      if ( $this->bID > 0 )
      {
         if ( $this->btTables)
         {
            foreach ( $this->records as $rec )
            {
               $rec->delete();
            }
         }
      }
   }
 
   protected function load()
   {
      foreach ( $this->records as $rec )
      {
         if ( is_object( $rec ) )
         {
            foreach ( $rec as $key => $value )
            {
               $this->{$key} = $value;
               $this->set( $key, $value );
            }
         }
      }
   }
}

Now in my actual block's controller.php, I add a call to load the new library file:

Loader::library( "block_multi_table_controller" );

Then I can inherit/extend my custom block controller from BlockMultiTableController rather than the normal BlockController.

At this point, rather than defining $btTable, I define $btTables as an array of strings with the names of my various block tables.

$btTables = array( "btCustomTable1", "btCustomTable2" );

In db.xml, I define each table, making sure to give each an integer bID field so that it gets hooked up to the right block instance.

<?xml version="1.0"?>
<schema version="0.3">
   <table name="btCustomTable1">
      <field name="bID" type="I">
         <key />
         <unsigned />
      </field>
      <field name="customField1" />
   </table>
   <table name="btCustomTable2">
      <field name="bID" type="I">
         <key />
         <unsigned />
      </field>
      <field name="customField2" />

Viewing 15 lines of 17 lines. View entire code block.

<?xml version="1.0"?>
<schema version="0.3">
   <table name="btCustomTable1">
      <field name="bID" type="I">
         <key />
         <unsigned />
      </field>
      <field name="customField1" />
   </table>
   <table name="btCustomTable2">
      <field name="bID" type="I">
         <key />
         <unsigned />
      </field>
      <field name="customField2" />
   </table>
</schema>

Some limitations: other than bID, you can't have duplicate field names in the tables. The system will overwrite them with whatever the last value it reads ends up being. Also, if you do define $btTable in your controller, it shouldn't do anything, but it's probably better if you don't.

Hopefully this helps.

FernandoCordeiro replied on Jun 19, 2012 at 11:28 pm Permalink Reply

Helps a lot. Gave me quite a few ideas to work with.

For my case it fits perfectly.

But for other cases it is not a complete solution, since you may need to reference one of the tables from the other.

I really think the Core Team should think of a nicer solution.

Still, helped me a lot. Thanks.

JSA1972 replied on Jul 23, 2012 at 6:53 am Permalink Reply

Hello JBPrime

I was trying to your code. How I can put onto add.php or edit.php?

<btCustomTable1 -> test1>
<?php echo $form->label('test1', 'Test One:');?>
<?php echo $form->text('test1', $test1, array('style' => 'width: 120px'));?>

<btCustomTable2 -> test2>
<?php echo $form->label('test2', 'Test Two:');?>
<?php echo $form->text('test2', $test2, array('style' => 'width: 120px'));?>

John

INTcommunications replied on Nov 11, 2013 at 5:18 pm Permalink Reply

I am wondering pretty much the same thing here. I can write sql to join tables but to simply executing a view from the 2 joined tables is probably simple but it is escaping my mind - after googleing like crazy and reading tons of forum questions I really don't see the answer I know now that you can define a relationship in a class in the model using

class Tablename1 Extends Model {}
class Tablename2 Extends Model {}
Model::ClassHasMany ('Tablename1','Tablename2',''Tablename1ID');

etc.
But in most of the packages I have seen they are using sql joins - so I get a little lost when it comes to actual accessing and presenting the data from both tables (in the view,php )

A simple demo of a simple inner join
model - classes and sql join

select players.name
from players
inner join teams
on players.team = teams.name

controller - to load model - Loader:: model ('Players');
etc.

view -

( This is where I get confused, I WOULD LIKE TO NAME A TEAM THEN NAME ALL THE PLAYERS NAMES FROM EACH TEAM )

Anwyay just typed in syntax to give you an idea of a demo I would like to see it is no way an offer of how to do it. It could be States table and cities etc. A demo of a simple join from top to bottom I think would help a lot of people like me ( new to Concrete 5 but not new to SQL )

Forums

Developing (v7+)

Single Block, multiple Tables

Code

Post Reply

Delete Post

Mark Post as Spam

Destroy Spammer

Sign In?