Welcome, Guest

TOPIC: [SOLVED] How to work with function buildQueryWhere()

[SOLVED] How to work with function buildQueryWhere() 14 Jan 2012 09:24 #974

etc
Offline
Premium Member
Posts: 132
Thank you received: 19
Karma: 7

I have a table exercises which I would like to display in frontend. This works well. Now I need to add coding for displaying data of logged in user. There is function inside models/exercises.php:

	function _buildQuery_exercises()
	{
    
    $user =& JFactory::getUser();  // I added this line
    $query = ' SELECT a.*'

	. $this->_buildQuerySelect()

	. ' FROM `#__training_exercises` AS a '
      
	. $this->_buildQueryJoin() . ' '

	. $this->_buildQueryWhere()
       . ' WHERE `a.user_exercise` = $user->id'   // I added this line. user_exercise is column where is saved user's id

	. $this->_buildQueryOrderBy()
	. $this->_buildQueryExtra()
	;

	return $query;
	}

Any idea how to correctly build this functionality?
thanks for any help

Last Edit: 14 Jan 2012 09:25 by etc.

The administrator has disabled public write access.

Re: How to work with function buildQueryWhere() 14 Jan 2012 10:51 #983

admin Offline Administrator Chef Posts: 3711 Thank you received: 984 Karma: 140	Of course I have an idea... $this->_buildQueryWhere() instance the "WHERE" keyword, so you cannot write it a second time. before the call : $this->_where[] = "`a.user_exercise` = " . (int)$user->id; Or this $this->addWhere("`a.user_exercise` = " . (int)$user->id)
	Coding is now a piece of cake The administrator has disabled public write access.

Re: How to work with function buildQueryWhere() 15 Jan 2012 08:25 #1000

etc Offline Premium Member Posts: 132 Thank you received: 19 Karma: 7	Nice, thanks. After a few tests works this: . $this->addWhere('`user_exercise` = ' . (int)$user->id)
	The administrator has disabled public write access.

Re: How to work with function buildQueryWhere() 04 Mar 2012 04:46 #1502

antzog
Offline
New Member
Posts: 9
Karma: 0

Hi

I have tried to use this code to set 'where' limitations on my model but am receiving the following error:

Fatal error: Call to undefined method SwauniversitiesModelUniversity::addWhere() in /home/.../com_swauniversities/models/university.php on line 170

The code I have added is marked below.

Also, I know that I have perfectly good working Query strings which i have added and I could use
$query = "SELECT a.* FROM #__swa_universities AS a WHERE uni_id='$uni'";
to perform the whole request but I would like to understand the Cook system more and how to build the models using the JOIN and WHERE code already in the component.

Any suggestions welcome.

Thanks

function _buildQuery_university()
	{
//CODE ADDED TO COOK COMPONENT
		//get user id
		$dbo =& JFactory::getDBO();
		$user =& JFactory::getUser();
		
		//get uni of president
		$query = "SELECT uni_id FROM #__swa_user WHERE mambo_id='$user->id'";
		$dbo->setQuery($query);
		$uni = $dbo->loadResult();

		//get the university id
		$query = "SELECT id FROM #__swa_universities WHERE uni_id='$uni'";
		$dbo->setQuery($query);
		$uniid = $dbo->loadResult();
//END OF CODE ADDED TO COOK COMPONENT

			$query = 'SELECT a.*'
					. 	$this->_buildQuerySelect()

					.	' FROM `#__swa_universities` AS a'
					. 	$this->_buildQueryJoin()

					. 	$this->_buildQueryWhere()
//CODE ADDED TO COOK COMPONENT (the below is line 170)
					.   $this->addWhere('`#__swa_universities.id` = ' . $uniid)
//END OF CODE ADDED TO COOK COMPONENT
					.	'';

		return $query;

Last Edit: 04 Mar 2012 05:10 by antzog.

The administrator has disabled public write access.

Re: How to work with function buildQueryWhere() 04 Mar 2012 10:17 #1505

admin Offline Administrator Chef Posts: 3711 Thank you received: 984 Karma: 140	There is no 'addWhere()' function in an Item model. It is based on object ID. If you want it, just overload the _buildQueryWhere() function.
	Coding is now a piece of cake The administrator has disabled public write access.

Re: How to work with function buildQueryWhere() 05 Mar 2012 18:14 #1506

antzog
Offline
New Member
Posts: 9
Karma: 0

Hi, thanks for your reply.

By 'overload the _buildQueryWhere() function' do you mean I should add my 'WHERE' parameters within this function:

function _buildQueryWhere()
	{
		$where = array();

		$app = &JFactory::getApplication();
		$option	= JRequest::getCmd('option');
		$view	= JRequest::getCmd('view');
		$layout	= JRequest::getCmd('layout', 'default');

		$baseUserState = $option . '_' . $view . '.' . $layout . '.';

		$where[] = 'a.id = '.(int) $this->_id;

		return parent::_buildQueryWhere($where);
	}

I have read etc's previous post and see that they input a line similar to this:

. $this->addWhere('`uni_id` = ' . $uniid)

Can you advise where I should place that in the function. Should it be after this line?

$where[] = 'a.id = '.(int) $this->_id;

Sorry for having only a basic understanding of the code, I learn a bit more everyday thanks to Cook!

The administrator has disabled public write access.

Re: How to work with function buildQueryWhere() 16 Mar 2012 22:17 #1634

antzog
Offline
New Member
Posts: 9
Karma: 0

Any help welcome...
This is more a lack of php knowledge than anything else i think... I need to somehow set the 'Where' option but I cannot work out where ETC's code would go in the model file. This is my simple buildQueryWhere function.

What can I do to make the function select only the data where one of the table fields being selected 'uni_id' = $uni_id ?? I have tried so many combinations but nothing seems to work...

Good Karma and Thanks to whoever can assist!

function _buildQueryWhere($where = array())
	{
		$app = JFactory::getApplication();
		$option	= JRequest::getCmd('option');
		$view	= JRequest::getCmd('view');
		$layout	= JRequest::getCmd('layout', 'default');

		$baseUserState = $option . '_' . $view . '.' . $layout . '.';

		return parent::_buildQueryWhere($where);
	}

Last Edit: 16 Mar 2012 22:25 by antzog.

The administrator has disabled public write access.

Re: How to work with function buildQueryWhere() 19 Mar 2012 14:34 #1667

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

I think this one is good :

	function _buildQueryWhere($where = array())
	{
		
		$app = &JFactory::getApplication();
		$option	= JRequest::getCmd('option');
		$view	= JRequest::getCmd('view');
		$layout	= JRequest::getCmd('layout', 'default');

		$baseUserState = $option . '_' . $view . '.' . $layout . '.';


//INIT $uniid HERE
// ....


		$where[] = 'a.uni_id = ' . $uniid;

		return parent::_buildQueryWhere($where);
	}

Should be working.

Coding is now a piece of cake

Last Edit: 19 Mar 2012 14:35 by admin.

The administrator has disabled public write access.

Re: How to work with function buildQueryWhere() 19 Mar 2012 14:37 #1668

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

//get uni of president
		$query = "SELECT uni_id FROM #__swa_user WHERE mambo_id='$user->id'";
		$dbo->setQuery($query);
		$uni = $dbo->loadResult();

		//get the university id
		$query = "SELECT id FROM #__swa_universities WHERE uni_id='$uni'";
		$dbo->setQuery($query);
		$uniid = $dbo->loadResult();

This is not optimized query. Merge these two in one.

Coding is now a piece of cake

The administrator has disabled public write access.

Re: How to work with function buildQueryWhere() 27 Mar 2012 07:46 #1737

andypooz
Offline
New Member
Posts: 18
Thank you received: 2
Karma: 0

I got this working for my project matching the user id to the added_by field (I had to call the JUser otherwise $user->id was returning NULL)

My next challenge is to add a WHERE ... OR .... clause to a query

What I have at the moment is

function _buildQueryWhere($where = array())
	{
		$app = JFactory::getApplication();
		$db= JFactory::getDBO();
		$option	= JRequest::getCmd('option');
		$view	= JRequest::getCmd('view');
		$layout	= JRequest::getCmd('layout', 'default');

		$baseUserState = $option . '_' . $view . '.' . $layout . '.';
		

		if (!isset($this->_active['publish']) || $this->_active['publish'] !== false)	$where[] = "a.publish=1";



		return parent::_buildQueryWhere($where);
	}

This checks whether the category is published and then includes it. I'd like to override this with an OR. There is a field in the categories table called 'added_by' which includes the id of the user who added it. I'm used to normal SQL, but not Joomla database way of doing things. What I'd like is something that will have this effect:

...WHERE ('publish' = 1 OR 'added_by' = $userid)... or ...WHERE ('publish' = 1 || 'added_by' = $userid)...

So that it is included even if it's not published only if it is that user's own category

How can I do this? I'm fine getting the user id into a variable, but how to I add a WHERE..OR.. clause to the model above?

Many thanks

Andy

Last Edit: 27 Mar 2012 07:48 by andypooz.

The administrator has disabled public write access.