Complex Field Queries [SOLVED]

Brat
Topic Author
Offline
Senior Member

12 years 8 months ago - 12 years 7 months ago #18532 by Brat

Complex Field Queries [SOLVED] was created by Brat

Hello again. Please excuse if this question is repetitious; I couldn't find an answer.

I'm slowly but surely working my way through a very wild project using Flexicontent. I'm finding that a large part of my problem is with SQL field queries.

Example: I want to create a field which will populate a select list using a query such as:

Code:

SELECT [whatever item] FROM [some table] WHERE author = $user->id

In the very crude example above, $user->id would be the front end user who is now using the entry form. I can run simple queries and ones involving JOIN (which made me very happy), but I don't know how to add a query to a field like in the example.

For now, I'm using some jQuery in order to manipulate my items, and it's working, but I'd really prefer to construct do this in a query and make things a lot simpler.

Is it possible for me to write a query like this for a Flexicontent field?

If not, is this a query that must be run in my custom form.php file? If so, how would I create a select list that could then be passed by the form upon submit? Thanks! 8-)

Last edit: 12 years 7 months ago by Brat.

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

micker
Offline
Administrator

12 years 8 months ago #18535 by micker

Replied by micker on topic Complex Field Queries [SOLVED]

It a good idéal but i thinks its not possible ... wihtout hard modification
Maybe use universal module to modify and display it content

FLEXIcontent is Free but involves a very big effort on our part.
Like the our support? (for a bug-free FC, despite being huge extension) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing reviews. Thanks![/size]

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

ggppdk
Offline
Administrator

12 years 8 months ago #18537 by ggppdk

Replied by ggppdk on topic Complex Field Queries [SOLVED]

Example for item titles:

Code:

SELECT c.id AS value, c.title AS text FROM #__content AS c WHERE created_by=62

(USERID 62 is the administrator) It is possible to create replaceable tags for the query e.g. current user {userid} but the userid of currently editing user is different from that of the currently viewing user, so the replacement will not have the results you expect.

A more meaningful replacement could be {itemowner} which will be the userid of the item owner

Code:

SELECT c.id AS value, c.title AS text FROM #__content AS c WHERE created_by={itemowner}

in selectmultiple.php in folder plugins/flexicontent_fields replace:

Code:

if ($sql_mode) { // SQL mode
 ...
} else { // Elements mode

with:

Code:

if ($sql_mode) { // SQL mode
	
	$db =& JFactory::getDBO();
	$jAp=& JFactory::getApplication();
	$itemowner = $item->created_by;
	
	$query = preg_match('#^select#i', $field_elements) ? $field_elements : '';
	$query = str_replace("{itemowner}", $itemowner, $query);
	
	$db->setQuery($query);
	$options = $db->loadObjectList();
	if (!$options) {$jAp->enqueueMessage(nl2br($db->getErrorMsg()),'error'); return;}
			
	if (!$query || !is_array($options)) {
		$field->html = JText::_('FLEXI_FIELD_INVALID_QUERY');
	} else {
	
		$field->html	= JHTML::_('select.genericlist', $options, $field->name.'[]', 'multiple="multiple" class="'.$required.'"'.$size, 'value', 'text', $field->value);
	}

} else { // Elements mode

i will add more of these into the select(multiple) Fields for v1.5.6

Regards

-- Flexicontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star...

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

ggppdk
Offline
Administrator

12 years 8 months ago #18538 by ggppdk

Replied by ggppdk on topic Complex Field Queries [SOLVED]

And also forgot to mention that probably the item will be viewed by unlogged users too (unless you limit access to it). Maybe you meant item-owner in the first place ...

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