Join two list views without a shared key

Question

I have a front end list view for a table called #_students and a separate front end list view for a table called #_sessions. The two tables have no shared foreign key and the two data sets are completely unrelated.

I want to display the data from both tables in the same list view, in the following manner:

• session 1
  • student 1
  • student 2
  • student 3
• session 2
  • student 1
  • student 2
  • student 3
• session 3
  • student 1
  • student 2
  • student 3

The point of this is that eventually, I want to add radio buttons next to each student name under each session name and allow the front end user to assign certain students to certain sessions. Upon clicking a 'submit' button, the results will be written to a third table, #_orders.

The trouble is, all of the articles I can find on joining two list views assume there is a shared key between the two tables and suggest using an SQL join. I do not have a shared key. Should I look into SQL join or can I do two separate SQL queries within the same model?

Here are the key functions from models/sessions.php:

    public function __construct($config = array())
    {
        if (empty($config['filter_fields']))
        {
            $config['filter_fields'] = array(
                'id', 'a.id',
                'ordering', 'a.ordering',
                'state', 'a.state',
                'created_by', 'a.created_by',
                'modified_by', 'a.modified_by',
                'date', 'a.date',
                'time', 'a.time',
                'title', 'a.title',
                'teacher', 'a.teacher',
                'lvl', 'a.lvl',
                'notes', 'a.notes',
                'jr', 'a.jr',
                'cap', 'a.cap',
                'att', 'a.att',
                'sid', 'a.sid',
            );
        }
        parent::__construct($config);
    }

    protected function getListQuery()
    {
        // Create a new query object.
        $db    = $this->getDbo();
        $query = $db->getQuery(true);

        // Select the required fields from the table.
        $query
            ->select(
                $this->getState(
                    'list.select', 'DISTINCT a.*'
                )
            );

        $query->from('`#__merakiac_sessions` AS a');

        // Join over the users for the checked out user.
        $query->select('uc.name AS uEditor');
        $query->join('LEFT', '#__users AS uc ON uc.id=a.checked_out');

        // Join over the created by field 'created_by'
        $query->join('LEFT', '#__users AS created_by ON created_by.id = a.created_by');

        // Join over the created by field 'modified_by'
        $query->join('LEFT', '#__users AS modified_by ON modified_by.id = a.modified_by');

        return $query;
    }

    public function getItems()
    {
        $items = parent::getItems();
        return $items;
    }

Here are the key functions from models/students.php:

public function __construct($config = array())
{
    if (empty($config['filter_fields']))
    {
        $config['filter_fields'] = array(
            'id', 'a.id',
            'ordering', 'a.ordering',
            'state', 'a.state',
            'created_by', 'a.created_by',
            'modified_by', 'a.modified_by',
            'parent', 'a.parent',
            'name', 'a.name',
            'lvl', 'a.lvl',
            'jr', 'a.jr',
        );
    }
    parent::__construct($config);
}

protected function getListQuery()
{
    // Create a new query object.
    $db    = $this->getDbo();
    $query = $db->getQuery(true);

    // Select the required fields from the table.
    $query
        ->select(
            $this->getState(
                'list.select', 'DISTINCT a.*'
            )
        );

    $query->from('`#__merakiac_student` AS a');

    // Join over the users for the checked out user.
    $query->select('uc.name AS uEditor');
    $query->join('LEFT', '#__users AS uc ON uc.id=a.checked_out');

    // Join over the created by field 'created_by'
    $query->join('LEFT', '#__users AS created_by ON created_by.id = a.created_by');

    // Join over the created by field 'modified_by'
    $query->join('LEFT', '#__users AS modified_by ON modified_by.id = a.modified_by');

    // Join over the created by field 'parent'
    $query->join('LEFT', '#__users AS parent ON parent.id = a.parent');

    return $query;
}

public function getItems()
{
    $items = parent::getItems();


    return $items;
}

And here is the site/views/sessions/tmpl/default.php (and it still needs work as I will need to somehow nest students' foreach loop within sessions' foreach loop):

<form action="<?php echo JRoute::_('index.php?option=com_merakiac&view=sessions'); ?>" method="post"
      name="adminForm" id="adminForm">

   <table>
    <?php foreach ($this->items as $i => $item) : ?>
        <?php echo $item->fieldnames; ?>
    <?php endforeach; ?>
   </table>

</form>

<form action="<?php echo JRoute::_('index.php?option=com_merakiac&view=students'); ?>" method="post"
      name="adminForm" id="adminForm">

   <table>
    <?php foreach ($this->items as $i => $item) : ?>
        <?php echo $item->fieldnames; ?>
    <?php endforeach; ?>
   </table>

</form>

Can somebody help me out with how to combine students.php's SQL query into sessions.php's SQL query and display the results of both in sessions/default.php? Is it even the SQL query which I must modify or the getItems() function? Do I even need the __construct($config = array()) function? I am new to Joomla and its SQL syntax. Any help would be greatly appreciated. Thank you!

Answer 1

All roads lead to Rome.

If you are new to Joomla, I recommend to read this:

https://docs.joomla.org/J3.x:Developing_an_MVC_Component

There you can get a good idea how Joomla components work.

Back to your question / problem:

I would do it the simple way and load the students model into the sessions view (default.php):

JLoader::import('joomla.application.component.model');
JLoader::import( 'students', JPATH_SITE . DS . 'components' . DS . 'com_merakiac' . DS . 'models' );
$model= JModelLegacy::getInstance('students','MerakiacModel');
$student_items = $model->getItems();

Then you can use the array if $student_items within you session item loop. If the output for the students never changes, I would loop through the student items and build the html before I loop the session items, this will save performance.