Fetching the complex records from database

Sometimes you'll want to do something more complex than displaying a single record from a database table. You will want to join in records that are from another database table as well as from a primary one. To do this, you want to avoid JTable and do a custom query in your model. So let's do that to show at single mall.

Create Mall file in the model

So first, go to the models folder in your com_noidacity component which have created earlier and create a file mall.php and write the following lines of code.

  1. <?php
  2. defined( '_JEXEC' ) or die;
  3.  
  4. jimport('joomla.application.component.model');
  5.  
  6. class NoidaCityModelMall extends JModel
  7. {
  8. public function getItem()
  9. {
  10. $mall_id = JRequest::getInt('id');
  11.  
  12. $db = $this->getDbo();
  13. $query = $db->getQuery(true);
  14.  
  15. $query->select('m.*,p.place_name');
  16. $query->from('#__noidacity_malls AS m');
  17. $query->join('LEFT','#_noidacity_places AS p USING(place_id)');
  18. $query->where('m.mall_id = {$mall_id}');
  19.  
  20. $db->setQuery($query);
  21. $row = $db->loadObject();
  22. return $row;
  23. }
  24.  
  25. }
  1. public function getItem()
  2. {
  3.  
  4. .............
  5. .............
  6. .............
  7. .............
  8.  
  9. }

we again have a getItem function that is just like the one for place which we created in earlier tutorial, but in this case, instead of pulling in a JTable object, we are doing a custom database query.

  1. $mall_id = JRequest::getInt('id');

We are pulling in the id and using getInt again, but this time, we are storing it as mall_id

  1. $db = $this->getDbo();
  2. $query = $db->getQuery(true);

Then we are getting a database object from the model. We just call this getDbo and that pulls in the database object, and then we are getting a fresh query by calling getQuery and passing in true. If we don't pass in true, we get the last query that was executed, and that's not what we want.

  1. $query->select('m.*,p.place_name');
  2. $query->from('#__noidacity_malls AS m');
  3. $query->join('LEFT','#_noidacity_places AS p USING(place_id)');
  4. $query->where('m.mall_id = {$mall_id}');

Next, we are building our query incrementally. We have select from, join in where and all those come together to pull in the data from the malls table, as well as some associated data from the place(table). So in this case, we are getting the mall that matches the mall_id that's coming in from the request.

  1. $db->setQuery($query);
  2. $row = $db->loadObject();
  3. return $row;

We are setting the query in the database using the setQuery function, and then we are calling the loadObject function on the database object. This returns a single object that's not in an array. And then once we have that row we can return it.

Create Mall Folder in view

So with this model in place, now we have to do is add a view for the mall. So go to the views folder in your com_noidacity component, create a mall folder and create the view.html.php and tmpl (default.php) our layout file.

So let's take a look at mall and let's add the following code in view.html.php file

  1. <?php
  2. defined( '_JEXEC' ) or die;
  3.  
  4. jimport( 'joomla.application.component.view' );
  5.  
  6. class NoidaCityMall extends JView
  7. {
  8. protected $item;
  9.  
  10. public function display($tpl = null){
  11. $this->item = $this->get('Item');
  12.  
  13. parent::display($tpl);
  14. }
  15. }

You will notice again we are just getting this getItem just like we did for place (in our previous tutorial).

Then let's create the layout file, default.php in our tmpl folder and add the following line of code.

  1. <?php defined( '_JEXEC' ) or die; ?>
  2. <h1><?php echo $this->escape($this->item->mall_name); ?></h1>
  3. <h2><?php echo "Place name near this mall: ". $this->escape($this->item->place_name); ?></h2>
  4.  
  5. <div id="mall">
  6. <div id="mall_left">
  7. <p id="mall_description">
  8. <?php echo $this->item->mall_description; ?>
  9. </p>
  10.  
  11. <h2><?php echo JTEXT::_('COM_NOIDACITY_MALLS_DETAILS'); ?></h2>
  12.  
  13. <dl>
  14.  
  15. <dt><?php echo JTEXT::_('COM_NOIDACITY_MALL_NAME'); ?></dt>
  16. <dd><?php echo $this->escape($this->item->mall_name); ?></dd>
  17. <dt><?php echo JTEXT::_('COM_NOIDACITY_MALLS_LOCATION'); ?></dt>
  18. <dd><?php echo $this->escape($this->item->mall_location); ?></dd>
  19.  
  20. </dl>
  21. </div>
  22. </div>

In this case we are getting the mall_name and the place_name that we joined in our database query in model. We are getting the mall_description, along with some details about the mall.

using complex query for our frontend

So if we go to the front end and go to index.php, option=com_noidacity&view=mall&id=1, it pulls in the name of the mall and it pulls in the name of the place near that mall. And then it pulls in all the information about the mall, including the details. So whenever you want to get more than just a single row from a single table, you can use a model to do a custom query and then that way you can get more complex data to display it in your view.