Welcome, Guest
Username: Password: Remember me
Forgot your password? Forgot your username? Create an account
Forum
Support forum
Coding inside your component
[SOLVED] How to work with function buildQueryWhere()
  • Page:
  • 1

TOPIC:

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

  • etc
  • etc's Avatar Topic Author
  • Offline
  • Premium Member
  • Premium Member
  • Posts: 132
  • Thank you received: 19
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 `vs4go_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

Please Log in or Create an account to join the conversation.

Last edit: by etc.

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

  • admin
  • admin's Avatar
  • Offline
  • Administrator
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 986
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

Please Log in or Create an account to join the conversation.

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

  • etc
  • etc's Avatar Topic Author
  • Offline
  • Premium Member
  • Premium Member
  • Posts: 132
  • Thank you received: 19
Nice, thanks.

After a few tests works this:
. $this->addWhere('`user_exercise` = ' . (int)$user->id)

Please Log in or Create an account to join the conversation.

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

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 `vs4go_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;

Please Log in or Create an account to join the conversation.

Last edit: by antzog.

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

  • admin
  • admin's Avatar
  • Offline
  • Administrator
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 986
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

Please Log in or Create an account to join the conversation.

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

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!

Please Log in or Create an account to join the conversation.

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

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);
	}

Please Log in or Create an account to join the conversation.

Last edit: by antzog.

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

  • admin
  • admin's Avatar
  • Offline
  • Administrator
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 986
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

Please Log in or Create an account to join the conversation.

Last edit: by admin.

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

  • admin
  • admin's Avatar
  • Offline
  • Administrator
  • Administrator
  • Chef
  • Posts: 3711
  • Thank you received: 986 
//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

Please Log in or Create an account to join the conversation.

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

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

Please Log in or Create an account to join the conversation.

Last edit: by andypooz.
  • Page:
  • 1
Forum
Support forum
Coding inside your component
[SOLVED] How to work with function buildQueryWhere()
Time to create page: 0.096 seconds
“
”

I jumped and started to work on a demo component... but 2 days later this demo component became the real component. I just showed today the end result to my customer and he turned to me and said... "this is more than I expected"... All of this is because Cook did cut about 70% of my work and provided me more ways to improve the usability of the component. The end result was 17 tables all related between than to generate a full dashboard for the travel agents. Thanks for Cook developers for such great tool. This component would not be possible to be done at short time with all the features in it
Griiettner (Forum)  

Get Started

Log In Sign In