Welcome, Guest

TOPIC: €25 for help:all records from related table @ item

€25 for help:all records from related table @ item 19 Sep 2012 19:59 #3793

dyvel
Offline
Elite Member
Posts: 200
Thank you received: 11
Karma: 10

Hi

I need to show all records from a related table (food budgets) on an item view (budget). My problem is, only the first record is returned, so I must be doing something wrong or not doing enough.

To describe the problem with a simple example:

a table of countries with another related table of cities. I would like a country view to list all cities to that country.

This problem and hopefully solution is something I believe many could benefit from...

I have added $query->join('LEFT', '`#__budgets_foodbudgets` AS _foodbudget_ ON _foodbudget_.budget = a.id');
to my model query.

I will gladly give €25 on Paypal to the person who can help me with this.

<?php
// no direct access
defined('_JEXEC') or die('Restricted access');

require_once(JPATH_ADMIN_BUDGETS .DS.'classes'.DS.'jmodel.item.php');


/**
* Budgets Item Model
*
* @package	Budgets
* @subpackage	Classes
*/
class BudgetsModelBudget extends BudgetsModelItem
{
	/**
	* List of all fields files indexes
	*
	* @var array
	*/
	protected $fileFields = array('image');

	/**
	* View list alias
	*
	* @var string
	*/
	protected $view_item = 'budget';

	/**
	* View list alias
	*
	* @var string
	*/
	protected $view_list = 'budgets';

	/**
	* Constructor
	*
	* @access	public
	* @param	array	$config	An optional associative array of configuration settings.
	* @return	void
	*/
	public function __construct($config = array())
	{
		parent::__construct();
	}

	/**
	* Method to delete a budget
	*
	* @access	public
	* @param	array	&$pks	The Ids of elements to delete.
	*
	* @return	boolean	True on success
	*/
	public function delete(&$pks = array())
	{
		if (!count( $pks ))
			return true;


		if (!parent::delete($pks))
			return false;



		return true;
	}

	/**
	* Method to get the layout (including default).
	*
	* @access	public
	*
	* @return	string	The layout alias.
	*/
	public function getLayout()
	{
		$jinput = JFactory::getApplication()->input;
		return $jinput->get('layout', 'budget', 'STRING');
	}

	/**
	* Returns a Table object, always creating it.
	*
	* @access	public
	* @param	string	$type	The table type to instantiate.
	* @param	string	$prefix	A prefix for the table class name. Optional.
	* @param	array	$config	Configuration array for model. Optional.
	*
	* @return	JTable	A database object
	*
	* @since	1.6
	*/
	public function getTable($type = 'Budget', $prefix = 'BudgetsTable', $config = array())
	{
		return JTable::getInstance($type, $prefix, $config);
	}

	/**
	* Method to increment hits (check session and layout)
	*
	* @access	public
	*
	* @return	boolean	Null if skipped. True when incremented. False if error.
	*
	* @since	11.1
	*/
	public function hit()
	{
		return parent::hit(array('budget'));
	}

	/**
	* Method to get the data that should be injected in the form.
	*
	* @access	protected
	*
	* @return	mixed	The data for the form.
	*/
	protected function loadFormData()
	{
		// Check the session for previously entered form data.
		$data = JFactory::getApplication()->getUserState('com_budgets.edit.budget.data', array());

		if (empty($data)) {
			//Default values shown in the form for new item creation
			$data = $this->getItem();

			// Prime some default values.
			if ($this->getState('budget.id') == 0)
			{
				$jinput = JFactory::getApplication()->input;

				$data->id = 0;
				$data->params = null;
				$data->title = null;
				$data->alias = null;
				$data->intro = null;
				$data->image = null;
				$data->financing = $jinput->get('filter_financing', $this->getState('filter.financing'), 'STRING');
				$data->loan_duration_in_years = null;
				$data->mortgages_per_year = null;
				$data->interest_rate = null;
				$data->loan_type = $jinput->get('filter_loan_type', $this->getState('filter.loan_type'), 'STRING');
				$data->loan_in_procent = null;
				$data->rate = null;
				$data->finance_cost = null;
				$data->region = $jinput->get('filter_region', $this->getState('filter.region'), 'INT');
				$data->tax_exempt_period = null;
				$data->interest_income_rate = null;
				$data->cash_account = null;
				$data->internal_notes = null;
				$data->published = null;
				$data->creation_date = null;
				$data->modification_date = null;
				$data->ordering = null;
				$data->access = 1;
				$data->checked_out = 0;
				$data->checked_out_time = null;
				$data->hits = null;
				$data->created_by = null;
				$data->modified_by = null;


			}
		}
		return $data;
	}

	/**
	* Method to auto-populate the model state.
	* 
	* This method should only be called once per instantiation and is designed to
	* be called on the first call to the getState() method unless the model
	* configuration flag to ignore the request is set.
	* 
	* Note. Calling getState in this method will result in recursion.
	*
	* @access	public
	* @param	string	$ordering	
	* @param	string	$direction	
	* @return	void
	*
	* @since	11.1
	*/
	public function populateState($ordering = null, $direction = null)
	{
		$app = JFactory::getApplication();
		$session = JFactory::getSession();



		parent::populateState($ordering, $direction);
	}

	/**
	* Preparation of the query.
	*
	* @access	protected
	* @param	object	&$query	returns a filled query object.
	* @param	integer	$pk	The primary id key of the budget
	* @return	void
	*/
	protected function prepareQuery(&$query, $pk)
	{

		//FROM : Main table
		$query->from('#__budgets AS a');

		switch($this->getState('context'))
		{
			case 'budget.budget':

				//BASE FIELDS
				$query->select(	'a.id,'
							.	'a.access,'
							.	'a.alias,'
							.	'a.cash_account,'
							.	'a.checked_out,'
							.	'a.checked_out_time,'
							.	'a.creation_date,'
							.	'a.finance_cost,'
							.	'a.financing,'
							.	'a.image,'
							.	'a.interest_income_rate,'
							.	'a.interest_rate,'
							.	'a.intro,'
							.	'a.loan_duration_in_years,'
							.	'a.loan_in_procent,'
							.	'a.loan_type,'
							.	'a.modification_date,'
							.	'a.mortgages_per_year,'
							.	'a.rate,'
							.	'a.region,'
							.	'a.tax_exempt_period,'
							.	'a.title');

				//SELECT
				$query->select('_region_.city AS `_region_city`');
				$query->select('_region_.region AS `_region_region`');
				$query->select('_region_city_.country AS `_region_city_country`');
				$query->select('_region_city_.city AS `_region_city_city`');
				$query->select('_region_city_country_.country AS `_region_city_country_country`');
				$query->select('_region_city_country_.corporate_tax_rate AS `_region_city_country_corporate_tax_rate`');
				$query->select('_region_city_country_.country_code AS `_region_city_country_country_code`');
				$query->select('_region_city_country_.flag AS `_region_city_country_flag`');
				$query->select('_checked_out_.name AS `_checked_out_name`');
				
				//SELECT BUDGETS
				$query->select('_foodbudget_.title AS `_foodtitle`');

				//JOIN
				$query->join('LEFT', '`#__budgets_regions` AS _region_ ON _region_.id = a.region');
				$query->join('LEFT', '`#__budgets_cities` AS _region_city_ ON _region_city_.id = _region_.city');
				$query->join('LEFT', '`#__budgets_countries` AS _region_city_country_ ON _region_city_country_.id = _region_city_.country');
				$query->join('LEFT', '`#__users` AS _checked_out_ ON _checked_out_.id = a.checked_out');
				
				//JOIN BUDGETS
				$query->join('LEFT', '`#__budgets_foodbudgets` AS _foodbudget_ ON _foodbudget_.budget = a.id');
				
				break;
			default:
				//SELECT : raw complete query without joins
				$query->select('a.*');
				break;
		}

		//SELECT : Instance Add-ons
		foreach($this->getState('query.select', array()) as $select)
			$query->select($select);

		//JOIN : Instance Add-ons
		foreach($this->getState('query.join.left', array()) as $join)
			$query->join('LEFT', $join);

		//WHERE : Item layout (based on $pk)
		$query->where('a.id = ' . (int) $pk);		//TABLE KEY


		//WHERE : Access
		//WHERE : Publish, publish date (state field)
	}

	/**
	* Prepare and sanitise the table prior to saving.
	*
	* @access	protected
	* @param	JTable	&$table	A JTable object.
	*
	* @return	void	
	* @return	void
	*
	* @since	1.6
	*/
	protected function prepareTable(&$table)
	{
		$date = JFactory::getDate();
		if ($table->access == null)
			$table->access = 1;

		if ($table->checked_out == null)
			$table->checked_out = 0;
		if (empty($table->id)){
			//Defines automatically the author of this element
			$table->created_by = JFactory::getUser()->get('id');

			// Set ordering to the last item if not set
			$table->ordering = $table->getNextOrder($this->getReorderConditions($table, true));
		}
		else{
			//Defines automatically the editor of this element
			$table->modified_by = JFactory::getUser()->get('id');

			//Modification date
			$table->modification_date = $date->toSql();
		}
		//Alias
		if (empty($table->alias))
			$table->alias = JApplication::stringURLSafe($table->title);

		//Creation date
		if (empty($table->creation_date))
			$table->creation_date = $date->toSql();
	}


}

Last Edit: 03 Oct 2012 13:26 by admin.

The administrator has disabled public write access.

Re: €25 for help:all records from related table @ item 20 Sep 2012 08:26 #3794

MorganL
Offline
Platinum Member
Posts: 438
Thank you received: 53
Karma: 16

the first thing I would do is turn on your debugging, go to the view that has the issue, go to queries in the output information at the bottom of the screen and copy / paste the query that is being generated.. should be easy enough to spot.. and past that into your database and see what is returned.

This will confirm if it is the query or the view / model / controller that is at fault. It may not be your data, but the loop that is generating the results in the view maybe?

I assume you have phpmyadmin or something similar running? if so just open the database, go to the sql tab and paste the code in and see what is generated.

Morgan Leecy MCSE

Novell / Linux
PHP. MYSQL, Apache, node.js
Coldfusion, JQuery, HTML5
Joomla

Last Edit: 20 Sep 2012 08:27 by MorganL.

The administrator has disabled public write access.

Re: €25 for help:all records from related table @ item 20 Sep 2012 08:39 #3795

dyvel Offline Elite Member Posts: 200 Thank you received: 11 Karma: 10	The SQL seem to be right... I have 2 records that relate to a record and they are visible in the output when I test the SQL. I'm positive it's something with the MVC part...
	Last Edit: 20 Sep 2012 08:39 by dyvel. The administrator has disabled public write access.

Re: €25 for help:all records from related table @ item 20 Sep 2012 09:39 #3796

dyvel
Offline
Elite Member
Posts: 200
Thank you received: 11
Karma: 10

If I add the SQL call to my list view instead, then I get the output I "almost" want... however, I would like the related items to be added as a "subarray"... and of course this should also be in item view, not list...

Print item from list view with my sql

What I would like:

Or 2 seperate arrays - one for the main view, and one for the related items

Last Edit: 20 Sep 2012 10:00 by dyvel.

The administrator has disabled public write access.

Re: €25 for help:all records from related table @ item 20 Sep 2012 12:54 #3801

admin Offline Administrator Chef Posts: 3711 Thank you received: 984 Karma: 140	Is somebody interressed by the offer ? Can I help you ?
	Coding is now a piece of cake The administrator has disabled public write access.

Re: €25 for help:all records from related table @ item 20 Sep 2012 12:56 #3802

dyvel Offline Elite Member Posts: 200 Thank you received: 11 Karma: 10	I apprecaiate any help I can get - and if you can help me solve it, then the money is yours
	The administrator has disabled public write access.

Re: €25 for help:all records from related table @ item 20 Sep 2012 13:08 #3803

admin Offline Administrator Chef Posts: 3711 Thank you received: 984 Karma: 140	I work on that and I tell you.
	Coding is now a piece of cake The administrator has disabled public write access. The following user(s) said Thank You: dyvel

Re: €25 for help:all records from related table @ item 20 Sep 2012 14:27 #3804

admin
Offline
Administrator
Chef
Posts: 3711
Thank you received: 984
Karma: 140

Well,

You cannot implement it in your SQL query. You must have another call.
The only way to do it in one query, is to create a groupby on a food budget list. but is is limited to these page only, so it is a wrong way if you want to be free.

I think for you, below is the correct answer :
(It works on an ITEM model for the moment)

in your model, models/budget.php :

1. Create a new function : (copy / paste this)

public function populateObjects(&$item)
{
	$modelFoodbudgets = JModel::getInstance('Foodbudgets', 'BudgetsModel');
	$modelFoodbudgets->addWhere('a.budget = ' . (int)$item->id);
	$item->foodbudget = $modelFoodbudgets->getItems();
}

2.Test it

3 lines. That's it !

Note : The amazing stuff is that populateObject function is available in infinite cascads !
I mean if you add such code in your foodbudgets model to obtain another N:1 links, you will get them also from budgets model.

It is really easy.

So, please consider also if you always need theses items everytime you load a budget.
This solution I propose you will ALWAYS load the foodbudgets items, even when you open a page that do not need it.

If you want to restrict the loading of the N:1 items, use the states vars :

In our example, let's define a state namespace : 'objects.budgets'

public function populateObjects(&$item)
{
	if ($this->getState('objects.budgets'))	//Custom namespace here
	{
		$modelFoodbudgets = JModel::getInstance('Foodbudgets', 'BudgetsModel');
		$modelFoodbudgets->addWhere('a.budget = ' . (int)$item->id);
		$item->foodbudget = $modelFoodbudgets->getItems();
	}
}

And this in your view file BEFORE to load the items :

$state->set('objects.budgets', true); //ADD THIS
$this->item = $item = $this->get('Item');	//loads the Items, do not touch

Coding is now a piece of cake

Last Edit: 20 Sep 2012 15:10 by admin.

The administrator has disabled public write access.

Re: €25 for help:all records from related table @ item 20 Sep 2012 14:29 #3805

admin Offline Administrator Chef Posts: 3711 Thank you received: 984 Karma: 140	Do you need it for a collection ?
	Coding is now a piece of cake The administrator has disabled public write access.

Re: €25 for help:all records from related table @ item 20 Sep 2012 14:54 #3806

admin
Offline
Administrator
Chef
Posts: 3711
Thank you received: 984
Karma: 140

For a collection :

put it in models/budgets.php

public function populateObjects(&$items)
{
	if ($this->getState('objects.budgets'))	//Custom namespace here
	{

		//Retreive all concerned ids to limit the query
		$ids = array();
		foreach($items as $item)
			$ids[] = $item->id;

		//Load only one time the model
		$modelFoodbudgets = JModel::getInstance('Foodbudgets', 'BudgetsModel');

		//Be carefull here because SQL query is not unlimited, you cannot concat thousands of ids
		$modelFoodbudgets->addWhere('a.budget IN (' . implode(',', $ids). ')');

		//Query SQL only one time
		$foodbudgets = $modelFoodbudgets->getItems();

		//Index the produits per category
		$values = array();
		foreach($foodbudgets as $foodbudget)
			$values[$foodbudget->budget][] = $foodbudget;


		//Populate the values
		foreach($items as $item)
		{
			if (isset($values[$item->id]))
				$item->foodbudget = $values[$item->id];
			else
				$item->foodbudget = array();
		}
	}
}

Is it working ?

EDIT : Same idea here, it is available in unlimited cascad !!
So everywhere you want to load it, just say it to the state var.

If you are using cascads + state vars, you must think about only ONE thing :
Before the call of a cascad model, transmit the state var to it, because from the main view, you init the state var for the first model only. So inside this function I gave to you, just add $modelXX->setState('objects.xxx', true); on the model before to load N:1 items.

Cook Self Service = A piece of cake.

Coding is now a piece of cake

Last Edit: 20 Sep 2012 14:59 by admin.

The administrator has disabled public write access.

Re: €25 for help:all records from related table @ item 20 Sep 2012 15:03 #3807

dyvel
Offline
Elite Member
Posts: 200
Thank you received: 11
Karma: 10

I'm not sure yet, but currently it's for an item.
But I can't get your code to output anything when I do a print_r($this);

Just to be clear

public function populateObjects(&$item)
{
	$modelFoodbudgets = JModel::getInstance('Foodbudgets', 'BudgetsModel');
	$modelFoodbudgets->addWhere('a.budget = ' . (int)$item->id);
	$item->foodbudget = $modelFoodbudgets->getItems();
}

should be in models/budget.php not budgets.php right? (i've tried both) and within the:

class BudgetsModelBudget ?

The administrator has disabled public write access.

Re: €25 for help:all records from related table @ item 20 Sep 2012 15:13 #3808

admin Offline Administrator Chef Posts: 3711 Thank you received: 984 Karma: 140	Yes, in item model, sorry, I've just edited it. do not work ? I see you avoided the state var for the moment. good to test. Do you have a fatal ? Can you dump the $modelFoodbudgets model ?
	Coding is now a piece of cake The administrator has disabled public write access.

Re: €25 for help:all records from related table @ item 20 Sep 2012 15:16 #3809

dyvel Offline Elite Member Posts: 200 Thank you received: 11 Karma: 10	I get no errors, but print_r($item->foodbudget); is empty How would I dump $modelFoodbudgets model ?
	The administrator has disabled public write access.

Re: €25 for help:all records from related table @ item 20 Sep 2012 15:25 #3810

admin
Offline
Administrator
Chef
Posts: 3711
Thank you received: 984
Karma: 140

From where are you dumping $item ? That's the question.

$modelFoodbudgets = JModel::getInstance('Foodbudgets', 'BudgetsModel');
$modelFoodbudgets->addWhere('a.budget = ' . (int)$item->id);
$item->foodbudget = $modelFoodbudgets->getItems();

//MODEL
echo("<pre>"); print_r($modelFoodbudgets); echo("</pre>");  // DBG temp VAR DUMP

//ITEM
echo("<pre>"); print_r($item); echo("</pre>");  // DBG temp VAR DUMP

//STOP HERE
exit();

Coding is now a piece of cake

The administrator has disabled public write access.

Re: €25 for help:all records from related table @ item 20 Sep 2012 15:27 #3811

dyvel Offline Elite Member Posts: 200 Thank you received: 11 Karma: 10	I dump $item from budget_fly.php
	The administrator has disabled public write access.