0

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!

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.

  • Thank you! So much easier than I thought it would be. I assume that any modifications to the SQL query in the students model (models/students.php) will be reflected in the results returned here? I will eventually add a WHERE filter to the SQL query. – Nathanael Aug 29 '17 at 11:50
  • Yes, just add $query->where('yourfield = '.$db->quote($your_value)); to your list function: docs.joomla.org/Special:MyLanguage/… – Dennis Heiden Aug 29 '17 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.